1 使用窗口函数按照每个language_id计算分组的score排名
<窗口函数> over (partition by <分组的列名> order by <排序的列名> desc/asc)
注意:出现序列并列情况 RANK()&DENSE_RANK()&ROW_NUMBER()
RANK()返回的是不持续的编号,
例如10, 11, 11, 12返回的编号将是1,2,2,4;
DENSE_RANK()返回的是持续的编号,
例如10, 11, 11, 12返回的编号是1,2,2,3;
ROW_NUMBER()返回的是持续不重复的编号
例如10, 11, 11, 12返回的编号将是1,2,3,4
select id ,language_id,score ,dense_rank()over(partition by language_id order by score desc) as rk from grade group by id
2 运用表连接补全信息,按题目要求排序
select a.id, l.name, a.score from (select id ,language_id ,score, dense_rank()over(partition by language_id order by score desc) as rk from grade group by id) a inner join language as l on a.language_id = l.id where a.rk <=2 order by l.name, a.score desc,a.id asc