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

select month,ranking,song_name,play_pv
from (
    select month,row_number()over(partition by month order by play_pv desc,song_id asc) ranking,song_name,play_pv
    from(
        select month(p.fdate) as month,s.song_name,p.song_id,count(p.song_id) as 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 s.singer_name = '周杰伦' and year(p.fdate) = '2022'and u.age between 18 and 25
        group by month,s.song_name,p.song_id
    ) as pv
) as rank_pv
where rank_pv.ranking between 1 and 3
order by month asc,ranking asc,song_name desc