题解 | 考试分数(三)

# 先连接表，把语言和名字对应起来；
# 根据不同语言的学生进行排序
# 取出前两名并排序
select
    a.id as id,
    a.name as name,
    a.score as score
from
    (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 ranks
    from
        grade as g
    inner join language as l on g.language_id = l.id) a
where 
    a.ranks in (1,2)
order by 
    name asc,score desc,id asc;