题解 | 每个月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) AS ranking, song_name, play_pv FROM (
SELECT month(fdate) AS month, play_log.song_id, song_name, COUNT(*) AS play_pv FROM( 
play_log 
JOIN user_info ON play_log.user_id = user_info.user_id
JOIN song_info ON play_log.song_id = song_info.song_id)
WHERE (age BETWEEN 18 AND 25)
AND (singer_name = '周杰伦')
GROUP BY month(fdate), play_log.song_id, song_name
ORDER BY month
) sub_sub_query
) sub_query
WHERE ranking <= 3