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

看完题目和输出图片中观察到排名的情况刚好和dense_rank一致,即可以使用窗口函数;

select *,dense_rank()over(partition by language_id order by score desc)as rs
from grade)c
left join language as l
on c.language_id = l.id

查询出成绩表格中的成绩排名后将对应的语言名名称左连接;

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

最终结果,在查询成绩排名中抽取排名小于等于2的排名,并根据题目要求排序即可;