题解 | 考试分数(三)

with a as(
    select g.id as id,l.name as name,g.score as score,
    dense_rank()over(partition by l.name order by g.score desc) as rk
    from grade g join language l on g.language_id=l.id
)
select id,name,score from a
where rk in (1,2)
order by name asc,score desc,id asc

注意：刚开始用的row_number排序，如99、99、98排序是1、2、3，这样就不是按实际分数排名，选出1和2

dense_rank，如99、99、98排序是1、1、2，这样就符合题意了