题解 | 考试分数(三)
考试分数(三)
https://www.nowcoder.com/practice/b83f8b0e7e934d95a56c24f047260d91
with a as(
select g.id as id,l.name as name,g.score as score,
dense_rank()over(partition by l.name order by g.score desc) as rk
from grade g join language l on g.language_id=l.id
)
select id,name,score from a
where rk in (1,2)
order by name asc,score desc,id asc
注意：刚开始用的row_number排序，如99、99、98排序是1、2、3，这样就不是按实际分数排名，选出1和2
dense_rank，如99、99、98排序是1、1、2，这样就符合题意了
