知识点

1. 使用窗口函数建立一个新表，题目要求的排名前2是分数排名所以使用dense_rank
2. 内连接一下语言岗位表，name正序score降序排序

代码

select g1.id, name, score
from (
      select *, dense_rank() over(partition by language_id order by score desc) as r
      from grade
      ) as g1
join language as l
on g1.language_id = l.id
where g1.r <= 2
order by name, score desc