题解 | #考试分数(三)#
考试分数(三)
http://www.nowcoder.com/practice/b83f8b0e7e934d95a56c24f047260d91
知识点
- 使用窗口函数建立一个新表，题目要求的排名前2是分数排名所以使用dense_rank
- 内连接一下语言岗位表，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
