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

SELECT *
FROM(
   SELECT
        n.month AS month,
        ROW_NUMBER() OVER(
            PARTITION BY n.month
            ORDER BY n.play_pv DESC , n.song_id ASC
            ) AS ranking,
        n.song_name,
        n.play_pv
    FROM
        (
            SELECT  s.song_id,MONTH(p.fdate)AS month,s.song_name,s.singer_name,COUNT(p.song_id) AS  play_pv
            FROM play_log p 
            LEFT JOIN song_info s USING(song_id)
            LEFT JOIN user_info u USING(user_id)
            WHERE age BETWEEN 18 AND 25 AND 
                YEAR(fdate)=2022 AND
                s.singer_name IN('周杰伦')
            GROUP BY s.song_name ,s.singer_name, MONTH(p.fdate),s.song_id
            
        ) n) t 
    WHERE t.ranking IN(1,2,3)