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

SELECT month,
ranking,
song_name,
play_pv
FROM (
    SELECT song_name,
    MONTH(fdate) AS month,
    COUNT(song_id) AS play_pv,
    ROW_NUMBER() OVER (PARTITION BY MONTH(fdate) ORDER BY COUNT(song_id) DESC,song_id) AS ranking
    FROM play_log LEFT JOIN song_info USING(song_id) LEFT JOIN user_info USING(user_id) 
    WHERE singer_name='周杰伦' AND (age BETWEEN 18 AND 25) AND (YEAR(fdate)=2022) 
    GROUP BY MONTH(fdate),song_name,song_id 
) AS t
HAVING ranking<=3
ORDER BY month,ranking;

本题要点：

1.MONTH（日期）：提取日期的月份

2.主查询只写列名，子查询具体实现