SQL练习74：考试分数（三）

题目链接：牛客网

题目描述
牛客每次举办企业笔试的时候，企业一般都会有不同的语言岗位，比如C++工程师，JAVA工程师，Python工程师，每个用户笔试完有不同的分数，现在有一个分数(grade)表简化如下:

第1行表示用户id为1的选择了language_id为1岗位的最后考试完的分数为12000，
…
第7行表示用户id为7的选择了language_id为2岗位的最后考试完的分数为11000，

不同的语言岗位(language)表简化如下:

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

解法
根据题目的要求可以使用dense_rank（）窗口函数，按照language_id进行分区，score降序排列，再将grade表与language表连接，获取name值。

SELECT g.id, name, score, dense_rank() over(partition by language_id ORDER BY score DESC) s_rank
FROM grade g JOIN language l
ON g.language_id = l.id

id	name	score	s_rank
2	C++	13000	1
1	C++	12000	2
6	C++	11000	3
3	JAVA	11000	1
7	JAVA	11000	1
4	JAVA	10000	2
5	Python	11000	1

2.有了上面的结果集，直接按照题目的要求查询排名前二的数据，再按照name升序排列即可。

SELECT id, name, score
FROM (SELECT g.id, name, score, 
	  dense_rank() over(partition by language_id ORDER BY score DESC) s_rank
      FROM grade g JOIN language l
      ON g.language_id = l.id) r1
WHERE r1.s_rank <= 2
ORDER BY name

id	name	score
2	C++	13000
1	C++	12000
3	JAVA	11000
7	JAVA	11000
4	JAVA	10000
5	Python	11000