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

with t1 as (
    select a.id, b.name, a.score
    from grade as a
    left join language as b
    on a.language_id = b.id
),
# 按照name进行分组，然后按照score降序进行排序
t2 as (
    select id, name, score,
    Dense_rank() over (PARTITION BY name ORDER BY score DESC) as score_rank
    from t1
)
select id, name, score
from t2
where score_rank <= 2
order by name, score desc, id;