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

with b as(select a.month, 
row_number() over(partition by a.month order by a.play_pv desc) as ranking, 
a.song_name,a.play_pv,a.song_id
from 
(select month(pl.fdate) as month, si.song_name,pl.song_id,
count(pl.user_id) as play_pv
from play_log pl
left join song_info si on pl.song_id = si.song_id
left join user_info ui on pl.user_id = ui.user_id
where si.singer_name = '周杰伦' and ui.age between 18 and 25 and year(pl.fdate) = 2022
group by month(pl.fdate), pl.song_id,si.song_name
order by pl.song_id
) a
)
select month,ranking,song_name,play_pv from b
having ranking <= 3

生成连续且不重复的排序序号用row_number()

排序的序号要前三，需要having ranking <= 3

最里层的子查询按月分，所以group by month，要记录不同的歌被听的次数，group by pl.song_id,si.song_name，不能只group by pl.song_id，或者group by si.song_name，因为SQL不知道song_id和song_name是一一对应的