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

select
c.month
,c.ranking
,c.song_name
,c.play_pv
from
(
    select
    b.month as month 
    ,row_number()over(partition by b.month order by play_pv desc) as ranking
    ,b.song_name as song_name
    ,b.play_pv as play_pv
    ,b.song_id
    from
    (
        select
        a.month as month
        ,a.song_name as song_name
        ,count(a.song_name) as play_pv
        ,a.song_id as song_id
        from
        (
            select
            month(pl.fdate) as month
            ,ui.age as age
            ,si.singer_name as singer_name
            ,si.song_name as song_name
            ,pl.user_id
            ,si.song_id as song_id
            from play_log as pl
            left join user_info as ui on (pl.user_id = ui.user_id)
            left join song_info as si on (si.song_id = pl.song_id)
            where si.singer_name = '周杰伦'
            and ui.age>=18 and ui.age<=25
        )a
        group by 1,2,4
        order by 4
    )b
)c 
where c.ranking<=3