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

SELECT *
FROM(
    SELECT
DISTINCT
month,
ROW_NUMBER() OVER (PARTITION BY month ORDER BY play_pv DESC,song_id)AS ranking,
song_name,play_pv
FROM (
    SELECT 
    DISTINCT month,
    DENSE_RANK() OVER (PARTITION BY month ORDER BY play_pv DESC) AS ranking,
    song_id,
    song_name,
    play_pv
    FROM(
        SELECT *, 
        MONTH(pl.fdate) AS month,
        COUNT(si.song_name) OVER (PARTITION BY MONTH(pl.fdate), si.song_name) AS play_pv
        FROM play_log pl
        LEFT JOIN user_info ui USING(user_id)
        LEFT JOIN song_info si USING (song_id)
        WHERE ui.age BETWEEN 18 AND 25 
            AND YEAR(pl.fdate)=2022 
            AND si.singer_name = "周杰伦"
        ) AS t
    ) AS t2
    ) AS t3
WHERE ranking <=3

写的不好 但注意防坑

相同play_pv的歌曲 记得先按照song_id升序排序后 再rank