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

with t as(
select month(fdate) as 'month',
row_number() over(partition by month(fdate),singer_name order by count(song_name) desc,a.song_id) as ranking,
singer_name,
song_name,
count(song_name) as play_pv
from play_log a
left join song_info b on a.song_id=b.song_id
left join user_info c on a.user_id=c.user_id
where year(fdate)='2022' 
and 18<=age and age<=25 
group by month(fdate),singer_name,song_name,a.song_id
order by month(fdate),singer_name,count(song_name) desc,a.song_id
)

select month,ranking,song_name,play_pv
from t where ranking<=3 and singer_name='周杰伦'

临时表储存所有歌手的歌曲排名，在其中筛选周杰伦前三的曲目。