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

【方法一：链接+查询】
【代码】
select t.id, t.name, t.score from
(select g.id, g.score, l.name from grade as g left join
language as l on g.language_id = l.id) as t
where t.id in
(select t1.id
from
(select g.id, g.score, l.name from grade as g left join
language as l on g.language_id = l.id) as t1
left join
(select g.id, g.score, l.name from grade as g left join
language as l on g.language_id = l.id) as t2
on t1.name = t2.name
and t2.score > t1.score
group by t1.id
having count(distinct t2.score)<=1)
order by t.name asc, t.score desc, t.id asc
【方法二：分组排序+查询（使用窗口函数）】
思路：要求最后获得的表格里面显示的是name而不是language_id，所以首先把language和score两个表连接，其次运用dense_rank()进行分组排序，最后用子查询找到排名为1,2的用户信息。
select t2.id, t2.name, t2.score from
(select t1., dense_rank() over(partition by t1.name order by t1.score) as r
from
(select g.id, g.score, l.name from grade as g left join
language as l on g.language_id = l.id) as t1) as t2
where t2.r <= 2
order by t2.name asc, t2.score desc, t2.id asc
结果：C语言组排名前两名的得分有误，其余课程结果正确--原因是在进行dense_rank（）时忘记排序加desc
修改后：
select t2.id, t2.name, t2.score from
(select t1., dense_rank() over(partition by t1.name order by t1.score desc) as r
from
(select g.id, g.score, l.name from grade as g left join
language as l on g.language_id = l.id) as t1) as t2
where t2.r <= 2
order by t2.name asc, t2.score desc, t2.id asc
运行正确