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

/*
请你找出每个岗位分数排名前2名的用户，得到的结果先按照language的name升序排序，再按照积分降序排序，最后按照grade的id升序排序

1.每个岗位，分数前2名的用户--窗口函数按照岗位分区，分数排序，给予rank或者row_number
*/

select
t1.id1 as id,
t1.name1 as name,
t1.score1 as score
from
(
select
g.id as id1,
l.name as name1,
g.score as score1,
dense_rank()over(partition by l.name order by g.score desc) as t_rank
#根据示例，同一排名人数可大于1，故选dense_rank 不跳序号且可重复序号
from
grade g left join language l
on g.language_id = l.id) as t1
where t1.t_rank<=2
order by t1.name1 asc, t1.score1 desc, t1.id1 asc