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

select month,ranking,song_name,play_pv from(
select month,row_number() over(partition by month order by play_pv desc) as ranking,song_name,play_pv
from(
select month(a.fdate) as month,a.song_id,b.song_name,count(b.song_name) play_pv
from play_log a, song_info b, user_info c
where a.user_id=c.user_id and a.song_id=b.song_id and 
c.age between 18 and 25 and b.singer_name='周杰伦' and year(a.fdate)=2022
group by month,song_id,song_name
order by month asc,play_pv desc, song_id asc
)d
)e
where ranking<4