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

select*
from(
select month(fdate) as month,
rank()over(partition by month(fdate) order by count(pl.song_id) desc, si.song_id asc )as ranking,
song_name,
count(pl.song_id) as play_pv
from 
song_info as si 
inner join play_log as pl on si.song_id=pl.song_id 
inner join user_info as ui on pl.user_id=ui.user_id

where year(fdate)=2022
and age between 18 and 25
and singer_name='周杰伦'
group by month(pl.fdate),si.song_name,si.song_id)
as ra
where ranking<=3