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

SELECT
    month,
    ranking,
    song_name,
    play_pv
FROM
    (
        SELECT
            MONTH(p.fdate) AS month,
            s.song_name,
            COUNT(*) AS play_pv,
            ROW_NUMBER() OVER (
                PARTITION BY
                    MONTH(p.fdate)
                ORDER BY
                    COUNT(*) DESC,
                    s.song_id ASC
            ) AS ranking
            
        FROM
            play_log p
            JOIN song_info s ON s.song_id = p.song_id
            JOIN user_info u ON u.user_id = p.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
    ) t
WHERE
    ranking <= 3
ORDER BY
    month,
    ranking

初学，轻喷