题解 | #每个月Top3的周杰伦歌曲#

select
    tt.month,
    tt.ranking,
    tt.song_name,
    tt.play_pv
from
(select
    t.month,
    t.song_name,
    t.song_id,
    t.play_pv,
    rank() over(partition by t.month order by t.play_pv desc,t.song_id asc) as ranking
from
    (select 
        month(a.fdate) as month,
        b.song_name,
        b.song_id,
        count(*) as play_pv
    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 
        year(a.fdate) = 2022 and c.age between 18 and 25 and b.singer_name = '周杰伦'
    group by 
        1,2,3) t
) tt
where tt.ranking <= 3
order by tt.month,tt.ranking