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

# 1.进行表联结
# 2.分组聚合，并使用窗口函数排序
# 3.将以上查询作为子查询传入主查询，筛选出每组前三名
SELECT *
FROM(
    SELECT MONTH(p.fdate) AS month, 
    ROW_NUMBER() OVER(PARTITION BY MONTH(p.fdate) ORDER BY COUNT(s.song_name)    DESC, p.song_id) AS ranking,
    s.song_name, 
    COUNT(*) AS play_pv
    FROM  play_log AS p
    LEFT OUTER JOIN song_info AS s
    ON p.song_id = s.song_id
    LEFT OUTER JOIN user_info AS u
    ON p.user_id = u.user_id	
    WHERE u.age BETWEEN 18 AND 25 
    AND YEAR(p.fdate) = 2022
    AND s.singer_name = '周杰伦'
    GROUP BY MONTH(p.fdate), s.song_name, p.song_id # 聚合函数前的字段都要出现在GROUP BY中
) AS T1
WHERE T1.ranking <=3
ORDER BY month, ranking;