首页 >

每个月Top3的周杰伦歌曲

牛客529794785号头像 牛客529794785号 
with play_log_name as(
    select
        month(p.fdate) as month,
        p.user_id,
        p.song_id,
        s.song_name
    from play_log p 
    join song_info s on p.song_id = s.song_id
    where s.singer_name = '周杰伦' and year(p.fdate) = 2022
),

usr_age_play as(
    select
        pln.month,
        u.age,
        pln.song_id,
        pln.song_name
    from play_log_name pln
    join user_info u on pln.user_id = u.user_id
),

constrained_age_play_count as (
    select
        month,
        song_id,
        song_name,
        count(*) as play_pv
    from usr_age_play
    where age between 18 and 25
    group by month, song_id, song_name
),

ranked_songs as (
    select
        month,
        song_name,
        play_pv,
        row_number() over(partition by month order by play_pv desc, song_id asc ) as ranking
    from constrained_age_play_count
)

select 
    month,
    ranking,
    song_name,
    play_pv
from ranked_songs
where ranking <=3
order by month, ranking


    

查看原题
添加回答
开心的牛油果许愿ssp头像 开心的牛油果许愿ssp

这也报错我也是没话可说，要不就不要设置这种会有多种答案的情况，设置就应该考虑到多种情况的出现，还大厂真题，这水平也是没谁了
发表于 2025-02-12 14:53:44 回复(15)
牛客620340814号头像 牛客620340814号
小白求问：在哪里看有什么表啊
发表于 2024-09-10 10:47:02 回复(8)
牛客185356452号头像 牛客185356452号 
with t as (
    select month(fdate) as `month`
        ,row_number() over (partition by month(fdate) order by count(*) desc,song_id) as ranking
        ,song_name
        ,count(*) as play_pv
    from play_log
    join user_info using(user_id)
    join song_info using(song_id)
    where year(fdate) = '2022'
    and age between 18 and 25
    and singer_name = '周杰伦'
    group by month(fdate),song_name,song_id    #因为窗口函数中用到song_id排序。所以分组时要加上song_id
)
select *
from t
where ranking <= 3

发表于 2024-08-09 11:19:08 回复(7)
Sen🍓🍀头像 Sen🍓🍀
题目也不说明相同play_pv, 按song_id升序
发表于 2025-01-18 17:52:03 回复(6)
许小北_头像 许小北_
从听歌流水中找到18-25岁用户在2022年每个月播放次数top 3的周杰伦的歌曲.
虽然这里只给出了一个用户的听歌记录,但是需要注意实际中可能会有多个不同年龄段用户.
STEP1: 左外连接play_log和user_info,再左外连接song_info表;
STEP2: 在此基础上筛选出2022年,18-25岁和歌手为周杰伦的所有听歌信息;
STEP3: 需要注意的是,需要新建一列RANKNG对听歌数进行排序,因此联想到使用窗口函数,并且由于存在相同排名但是只取3首歌的情况,所以使用ROW_NUMBER(); 
SELECT * 
FROM
    (SELECT MONTH(fdate) month,
    ROW_NUMBER() OVER(PARTITION BY MONTH(fdate) ORDER BY COUNT(s.song_id) DESC,p.song_id ASC) ranking,
    song_name,COUNT(s.song_id) play_pv
    FROM user_info u 
    LEFT JOIN play_log p ON u.user_id=p.user_id
    LEFT JOIN song_info s ON p.song_id=s.song_id
    WHERE YEAR(fdate)=2022 AND (age BETWEEN 18 AND 25) AND (singer_name='周杰伦')
    GROUP BY month,song_name,p.song_id) t1
WHERE ranking IN(1,2,3);
如果提交报错,可能是因为存在排名听歌数量相同但是排名不一样的歌曲,因此需要在窗口函数的orderby部分按照song_id再降序排列即可.
发表于 2024-08-31 11:18:27 回复(4)
别惹cc头像 别惹cc 
select 
    *
from
(
# 按月份分区、按播放总量降序、歌曲id升序进行rank排序
select
    a.month,
    row_number() over(partition by a.month order by play_pv desc, song_id asc) as ranking,
    song_name,
    play_pv
from
(
# 先搜索周杰伦22年每个月的不同歌曲及播放总量
select
    month(pl.fdate) as month,
    si.song_name,
    count(si.song_name) as play_pv,
    si.song_id
from
    play_log as pl inner join
    song_info as si on si.song_id = pl.song_id inner join
    user_info as ui on ui.user_id = pl.user_id    
where
    ui.age >= 18 and 
    ui.age <= 25 and
    year(pl.fdate) = 2022 and 
    si.singer_name = "周杰伦"
group by 
    month(pl.fdate),
    si.song_name,
    si.song_id
) a
) b
having 
    ranking <= 3

发表于 2024-09-28 22:59:04 回复(2)
szywdwd头像 szywdwd 
select *
from (
    select
        month(fdate) as month,
        row_number() over (partition by month(fdate) order by count(*) desc, play_log.song_id) as ranking,
        song_name,
        count(*) as play_pv
    from play_log left join song_info on play_log.song_id = song_info.song_id left join user_info on play_log.user_id = user_info.user_id
    where 18 <= age and age <= 25 and year(fdate) = '2022' and singer_name = '周杰伦'
    group by month(fdate), song_name, play_log.song_id
) as t
where ranking <= 3

发表于 2024-09-13 08:55:36 回复(2)
活泼的小太阳在炒股头像 活泼的小太阳在炒股
不使用窗口函数，应该怎么做？
我使用的MYSQL版本5.8  不支持ROW_NUMBER()
发表于 2025-01-11 15:13:19 回复(2)
Icarusyzm头像 Icarusyzm
第一步：cte求出第一个表：月份，歌名，歌id，播放次数 （left join user_info区分出年纪，left join song_info得到歌名和歌id
                where里限制年龄，年份，和周杰伦。
第二步：cte1求出第二个表：月份，排名（播放次数作为评级的标准,如果播放次数相同则用歌id区分），歌名，播放次数。
第三步：选出前三的歌（我总觉得有办法把第二，三步合为一步，知道的请告知
                
with cte as(
select month(play_log.fdate) as month,
song_info.song_name,
song_info.song_id,
count(play_log.song_id)as play_pv
from play_log
left join song_info on play_log.song_id=song_info.song_id
left join user_info on play_log.user_id=user_info.user_id
where user_info.age between 18 and 25 and year(play_log.fdate)=2022
and song_info.singer_name='周杰伦'
group by month,song_info.song_id,song_info.song_name
)

,cte1 as (select month,rank() over (partition by month order by play_pv desc,song_id) as ranking,
song_name,play_pv
from cte)

select * from cte1 where ranking <4
发表于 2024-08-18 20:18:04 回复(1)
Sqlite
御坂202412头像 御坂202412
如果某个月里，有两首或多首歌的播放量是一样的，那么还要按照歌曲编号再排一次顺序。
确实，题目没有明确说明这个要求，但实际项目中要考虑的东西甚至不止于此！求职者能否考虑到这些隐藏条件，也是面试官要考察的一部分！ 
            ROW_NUMBER() OVER (
                PARTITION BY
                    MONTH (fdate)
                ORDER BY
                    COUNT(1) DESC,
                    song_info.song_id ASC
            ) AS ranking
发表于 2025-02-14 11:39:06 回复(0)
Mysql
牛客249182747号头像 牛客249182747号 
with t as(
select month(pl.fdate) as month,
rank() over (partition by month(pl.fdate) order by count(pl.song_id) desc,si.song_id) as ranking,
si.song_name as song_name,
count(*) as play_pv

from play_log pl,song_info si,user_info ui
where pl.user_id=ui.user_id
and pl.song_id = si.song_id
and ui.age between 18 and 25 
and si.singer_name = '周杰伦'
and year(pl.fdate) ='2022'
group by month(pl.fdate),song_name,pl.song_id
)
select *
from t
where ranking <= 3

千幸万苦终于搞懂了

首先链接三张表，按照18-25，2022年，周杰伦的歌筛选条件，用rank函数对同一月份的总播放数排序，rank函数在排序时会有两个排序相等的情况，给他多设置一个参数可以解决这个问题，或者使用ROW_NUMBER()函数





发表于 2024-12-05 20:38:55 回复(4)
叁两_头像 叁两_ 
select
    month,
    ranking,
    song_name,
    play_pv
from
    (
        select
            month(p.fdate) month,
            (
                row_number() over (
                    partition by
                        month(p.fdate)
                    order by
                        count(s.song_name) desc,
                        p.song_id
                )
            ) ranking,
            s.song_name,
            count(s.song_name) play_pv
        from
            play_log p
            left join song_info s on p.song_id = s.song_id
            left join user_info u on p.user_id = u.user_id
        where
            (u.age between 18 and 25)
            and (year(p.fdate) = 2022)
            and (s.singer_name = '周杰伦')
        group by
            month(p.fdate),
            s.song_name,
            p.song_id
    ) n1
where
    ranking < 4


发表于 2025-08-07 16:27:29 回复(0)
Mysql
BeiChrn头像 BeiChrn 
SELECT month,ranking,song_name,play_pv
FROM(
SELECT MONTH(t.fdate) AS month,
t1.song_name AS song_name,
COUNT(*) AS play_pv,
ROW_NUMBER() OVER (PARTITION BY MONTH(t.fdate) ORDER BY COUNT(*) DESC,t1.song_id ASC ) AS ranking
FROM play_log AS t INNER JOIN song_info t1 ON t.song_id=t1.song_id
INNER JOIN user_info t2 ON t.user_id=t2.user_id
WHERE t2.age BETWEEN 18 AND 25 AND year(fdate)=2022 AND t1.singer_name='周杰伦'
GROUP BY month,t1.song_name,t1.song_id
) AS temp
WHERE temp.ranking<=3
ORDER BY month, ranking

发表于 2026-03-25 13:21:38 回复(0)
Mysql
牛客983493477号头像 牛客983493477号 
select xx.month,	xx.ranking,	xx.song_name,	xx.play_pv
from (

select
    x.month,
    
    row_number() over (
        partition by
            x.month
        order by
            x.play_pv desc,
            x.song_id asc
    ) as ranking,
    x.song_name,
    x.play_pv
from
    (
        select
            month(a.fdate) as month,
            b.song_name,
            count(a.song_id) as play_pv,
            a.song_id
        from
            play_log a
            left join song_info b on a.song_id = b.song_id
            left join user_info c on a.user_id = c.user_id
        where
            b.singer_name = "周杰伦" and
            c.age >= 18 and c.age <= 25  and year(a.fdate) = 2022
        group by
            month,
            song_name,
            song_id
    ) x ) xx
where ranking <= 3

发表于 2025-11-27 02:15:20 回复(0)
有礼貌的土拨鼠希望被offer砸中头像 有礼貌的土拨鼠希望被offer砸中
SELECT
    month,
    ranking,
    song_name,
    play_pv
FROM (
    SELECT
        month,
        song_name,
        play_pv,
        ROW_NUMBER() OVER (PARTITION BY month ORDER BY play_pv DESC) as ranking
    FROM (
        SELECT
            MONTH(p.fdate) AS month,
            s.song_name,
            COUNT(*) AS play_pv
        FROM play_log p
        INNER JOIN song_info s ON s.song_id = p.song_id
        INNER JOIN user_info u ON u.user_id = p.user_id  
        WHERE p.fdate LIKE '2022-%'
            AND s.singer_name = '周杰伦'
            AND u.age BETWEEN 18 AND 25
        GROUP BY MONTH(p.fdate), s.song_name
    ) t
) ranked_table
WHERE ranking <= 3
ORDER BY month, ranking;

发表于 2025-11-07 00:14:06 回复(0)
在泡澡的候选人很喜欢耍游戏头像 在泡澡的候选人很喜欢耍游戏
结果排序不一样，只能做到这里了。
select *
from
(
        select  month,
                row_number()over(partition by month order by play_pv desc) as ranking,
                song_name,
                play_pv
        from
        (
            select
            month,
            c.song_id ,
            c.song_name,
            count(c.song_name) as play_pv
            from user_info a
            left join (select month(fdate) as month ,user_id,song_id from play_log) b
            on a.user_id =b.user_id
            left join song_info c
            on b.song_id=c.song_id
            group by month,c.song_id,c.song_name
            order by month asc,play_pv desc
        ) f
) g
where ranking<=3;

发表于 2025-10-07 17:29:10 回复(1)
Mysql
狼漫主导的狼头像 狼漫主导的狼
with t as( select
            month(fdate) as month,
            song_id,
            count(song_id) as play_pv
        from
            play_log
            where user_id in
            (select
            user_id
            from user_info
            where age between 18 and 25)
        group by
            month(fdate),
            song_id)
select
    *
    from (select
        t.month, row_number() over (partition by t.month order by t.play_pv desc) as ranking,a.song_name,t.play_pv
from t join
(select
    *
    from song_info
    where singer_name="周杰伦") a
    on t.song_id=a.song_id) b
    where b.ranking<=3  
反思：有筛选字段在需要join的表里面，需要先join之后在进行row_number的排序
发表于 2025-10-06 20:19:07 回复(0)
牛客558348316号头像 牛客558348316号
注意题目示例1中说明了 
播放次数相同，排名先后由两者的song_id先后顺序决定
发表于 2025-09-01 10:20:18 回复(0)
Mysql
属实大白菜头像 属实大白菜
1.先关联筛选数据，再聚合（聚合后一定要按song_id进行升序），再row_number按月份排序，最终筛选排序结果前3。
2.之前没按song_id排序，提交结果总有几个测试用例过不去（坑点）。
发表于 2025-07-18 18:02:42 回复(0)
东方宸星头像 东方宸星
我写的好长。。。 
WITH
    song_pv_month AS (
        SELECT
            MONTH (fdate) AS `month`,
            song_name,
            song_id,
            COUNT(*) AS play_pv
        FROM
            play_log
            LEFT JOIN user_info USING (user_id)
            LEFT JOIN song_info USING (song_id)
        WHERE
            age >= 18
            AND AGE <= 25
            AND YEAR (fdate) = 2022
            AND singer_name = '周杰伦'
        GROUP BY
            MONTH (fdate),
            song_name,
            song_id
    ),
    song_pv_month_rank AS (
        SELECT
            `month`,
            ROW_NUMBER() OVER (
                PARTITION BY
                    `month`
                ORDER BY
                    play_pv DESC,
                    song_id
            ) AS `ranking`,
            song_name,
            play_pv
        FROM
            song_pv_month
    )
SELECT
    `month`,
    ranking,
    song_name,
    play_pv
FROM
    song_pv_month_rank
WHERE
    ranking <= 3


发表于 2025-06-02 21:47:00 回复(0)
提交观点

问题信息

SQL
来自：2023年腾讯音乐秋招...
难度：
294条回答 3388浏览

热门推荐

相关试题

每个月Top3的周杰伦歌曲

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

扫描二维码，关注牛客公众号