题解 | 每个月Top3的周杰伦歌曲
SELECT * FROM(SELECT month, ROW_NUMBER() OVER(PARTITION BY month ORDER BY play_pv DESC) AS ranking, song_name, play_pv FROM(SELECT DISTINCT MONTH(fdate) AS month, pl.song_id, COUNT(fdate) OVER(PARTITION BY MONTH(fdate), pl.song_id) AS play_pv FROM play_log pl LEFT JOIN user_info ui ON pl.user_id = ui.user_id WHERE age BETWEEN 18 AND 25 AND YEAR(fdate) = 2022 ) a LEFT JOIN song_info si ON a.song_id = si.song_id WHERE singer_name = '周杰伦' ORDER BY month, ranking ) b WHERE ranking <= 3;