窗口函数dense()的使用

先找出排名

select *, dense_rank() over(partition by b.name order by a.score desc) as rank from grade a left join language b on a.language_id = b.id

再进一步查询即可

select c.id, c.name, c.score
from (select *, dense_rank() over(partition by b.name order by a.score desc) as rank from grade a left join language b on a.language_id = b.id) c
where c.rank <= 2
order by c.name asc, c.score desc, c.id asc;