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

select d.moth AS month,d.ranking, d.song_name,d.play_pv from 
    (select moth , (row_number() over(PARTITION BY moth order by count(*) desc, b.song_id )) ranking ,song_name ,count(*) AS play_pv
        from user_info AS a join
    (select month(fdate) AS moth,song_id,user_id from play_log) AS b
        on a.age between 18 and 25 and a.user_id = b.user_id
    join song_info AS c 
        on c.singer_name='周杰伦' and b.song_id=c.song_id
    group by moth, b.song_id
    order by moth asc,play_pv desc,b.song_id ) AS d
where d.ranking<4

此题难点在于摸清分组规则，要把月份和歌曲id名称共同分组