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

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