没办法只能多层嵌套 | #每个月Top3的周杰伦歌曲#

with ranked as (
  select 
  	month, 
  	row_number() over (partition by month order by play_pv DESC, song_id ASC) as ranking,
  	song_name, 
  	play_pv
from(
  	select 
  		month(p.fdate) as month, 
  		s.song_name, 
  		count(*) as play_pv, 
  		max(p.song_id) as s.song_id
	from 
  		play_log p 
  	join 
  		song_info s 
  	on 
  		p.song_id = s.song_id
	join 
  		user_info i 
 	on 
  		p.user_id = i.user_id 
	where 
  		s.singer_name = '周杰伦' and i.age between 18 and 25
	group by 
  		s.song_name, s.song_id, month
) temp)

select
	* 
from 
	ranked
where 
	ranking <= 3
order by 
	month, ranking