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

select month,ranking,song_name,play_pv from (select
    mm as 'month',
    row_number() over (
        partition by
            mm 
        order by
            cc desc,song_id
    ) as ranking,
    song_name,
    song_id,
    cc as play_pv
from
    (
        select distinct
            month(fdate) as mm,
            count(*) over (
                partition by
                    month(fdate),
                    song_name
            ) as cc,
            song_id,
            song_name
        from
            (
                select
                    p1.*,
                    p2.song_name,
                    p2.singer_name,
                    p3.age
                from
                    play_log as p1
                    left outer join song_info as p2 on p1.song_id = p2.song_id
                    left outer join user_info as p3 on p1.user_id = p3.user_id
                where
                    singer_name = '周杰伦'
                    and (age between 18 and 25)
            ) as pp
    ) as ppp) as ppp where ranking<=3 order by month,ranking;
	
#实际上包含了隐形条件 次数相同，应该按song_id 进行升序排列