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

select
    *
from
    (
        select
            temp.month,
            row_number() over (
                partition by
                    temp.month
                order by
                    temp.play_pv desc,
                    temp.song_id
            ) ranking,
            temp.song_name,
            temp.play_pv
        from
            (
                select
                    year (a.fdate) year,
                    month (a.fdate) month,
                    b.song_id,
                    b.song_name,
                    count(a.song_id) play_pv
                from
                    play_log as a
                    inner join (select * from song_info where singer_name = '周杰伦') as b on a.song_id = b.song_id
                    inner join (select * from user_info where age >= 18 and age <= 25) as c on a.user_id = c.user_id
                group by
                    year,
                    month,
                    b.singer_name,
                    b.song_id,
                    b.song_name
                having
                    year = 2022
            ) temp
    ) t
where
    ranking <= 3;