题解 | 考试分数(三)

select
    t2.id,
    t2.name,
    t2.score
from
    (select
        t1.id,
        t1.name,
        t1.score,
        dense_rank() over(partition by t1.name order by t1.score desc) as score_rank
    from
        (select
        g.id,
        l.name,
        g.score
        from grade g left join language l 
        on g.language_id=l.id) t1) t2
where
    t2.score_rank<=2
order by 
    t2.name,t2.score desc,t2.id;