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

需要输出每个岗位分数前两名的用户，意味着需要使用开窗，但题目中的前两名并没有说明是哪种类型的排名，根据报错结果推测是dense_rank。

首先，采用开窗进行分组排序

select
    *,
    dense_rank() over(partition by g.language_id order by score desc) as ranking
from grade

其次，连接language表，获得完整的排序表

select
    g.id,
    l.name,
    g.score,
    dense_rank() over(partition by g.language_id order by g.score desc) as ranking
from grade as g
join language as l
on g.language_id = l.id

最后，在查找出的排序表中找出前两名的用户信息

select 
    id, 
    name,
    score
from (
    select 
        g.id,
        l.name,
        g.score,
        dense_rank() over(partition by g.language_id order by g.score desc) as ranking
    from grade as g
    join language as l
    on g.language_id = l.id
      ) as t
where ranking = 1 or ranking = 2
order by name, score desc, id