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

select month,ranking,song_name,play_pv from 
(select 
CAST(DATE_FORMAT(a.fdate, '%c') AS UNSIGNED)  month,b.song_name,b.song_id,count(*)   play_pv,row_number() over(partition by CAST(DATE_FORMAT(a.fdate, '%c') AS UNSIGNED)  order by count(*) desc,song_id) ranking
from play_log a
join song_info b on a.song_id=b.song_id
join user_info c on a.user_id=c.user_id
where c.age between 18 and 25 and year(a.fdate)='2022' and b.singer_name='周杰伦'
group by month,b.song_name,b.song_id
) t where t.ranking<4
order by month,ranking