2026-08-08

1 sql40

select
 
    sub.month,
 
    sub.ranking,
 
    sub.song_name,
 
    sub.play_pv
 
from(
 
    select
 
        month(pl.fdate) as month,
 
        ROW_NUMBER() OVER (PARTITION BY MONTH(pl.fdate) ORDER BY COUNT(*) DESC, si.song_id ASC) AS ranking,
 
        si.song_name,
 
        count(*) as play_pv
 
    from
 
        play_log as pl
 
    inner join song_info as si
 
        on pl.song_id = si.song_id
 
    inner join user_info as ui
 
        on pl.user_id = ui.user_id
 
    where
 
        si.singer_name = "周杰伦"
 
        and ui.age between 18 and 25
 
        and year(pl.fdate) = 2022
 
    group by
 
        month(pl.fdate), si.song_name, si.song_id
 
) as sub
 
where
 
    sub.ranking <= 3
 
order by
 
    sub.month asc, sub.play_pv desc