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

SELECT *
FROM (SELECT month,
             ROW_NUMBER() OVER (PARTITION BY month ORDER BY play_pv DESC ,song_id) AS ranking,
             song_name,
             play_pv
      FROM (SELECT MONTH(fdate) AS month, MIN(p.song_id) AS song_id, COUNT(1) AS play_pv, MIN(song_name) AS song_name
            FROM play_log p
                     JOIN user_info u ON p.user_id = u.user_id
                     JOIN song_info s ON p.song_id = s.song_id
            WHERE YEAR(fdate) = 2022
              AND age >= 18
              AND age <= 25
              AND singer_name = '周杰伦'
            GROUP BY month, p.song_id) t) t2
WHERE ranking <= 3;