题解 | #考试分数(三)#

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