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

select 
*
from 
(
    select 
    months as `month`
    ,rank() over(partition by months order by song_cunt desc ,song_id  ) as ranking
    ,song_name
    ,song_cunt as play_pv
    from 
    (
        select 
        month(t1.fdate) as months
        ,t2.song_name
        ,t1.song_id
        ,count(*) as song_cunt
        from
        play_log t1 
        left join song_info  t2  
        on t1.song_id = t2.song_id
        left join user_info t3
        on t1.user_id = t3.user_id
        where year(fdate) = 2022
        and t3.age between  18 and 25
        and t2.singer_name  = '周杰伦'
        group by month(t1.fdate),t1.song_id,t2.song_name
    ) t1
) t1 
where ranking <= 3