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

SELECT 
a.month,
a.ranking,
b.song_name,
a.play_pv
FROM(
    SELECT 
    MONTH(fdate) month,
    song_id,
    ROW_NUMBER() OVER(partition by MONTH(fdate) order by COUNT(song_id) desc,song_id) ranking,
    COUNT(song_id) play_pv
    FROM play_log
    WHERE user_id IN(
        SELECT user_id
        FROM user_info
        WHERE age BETWEEN 18 AND 25
    )
    AND song_id IN(
        SELECT song_id
        FROM song_info
        WHERE singer_name = '周杰伦'
    )
    AND fdate BETWEEN '2022-01-01' AND '2022-12-31'
    GROUP BY MONTH(fdate),song_id
    ) a
INNER JOIN song_info b
ON a.song_id = b.song_id
WHERE a.ranking < 4
ORDER BY a.month,a.ranking

也可以用三表连接，我是新人写不出来只能这样了