SQL练习76：考试分数（五）

题目链接：牛客网

题目描述
牛客每次考试完，都会有一个成绩表(grade)，如下:

第1行表示用户id为1的用户选择了C++岗位并且考了11001分
…
第8行表示用户id为8的用户选择了B语言岗位并且考了9999分

请你写一个sql语句查询各个岗位分数的中位数位置上的所有grade信息，并且按id升序排序，结果如下:

解释：

第1行表示C++岗位的中位数位置上的为用户id为2，分数为10000，在C++岗位里面排名是第2

第2，3行表示Java岗位的中位数位置上的为用户id为4,5，分数为12000,13000，在Java岗位里面排名是第2,1

第4行表示B语言岗位的中位数位置上的为用户id为7，分数为11000，在前端岗位里面排名是第2

解法
1.根据 SQL练习75：考试分数（四）中的思想，我们可以获得每个job的中位数区间。

SELECT job, round(COUNT(id)/2) `start`, round((COUNT(id)+1)/2) `end`
FROM grade
GROUP BY job
ORDER BY job

job	start	end
B	2	2
C++	2	2
Java	1	2

2.使用窗口函数row_number()按job进行分区score进行降序排序，获取各个job的score排名。

三个排序窗口函数的使用

SELECT *, row_number() over(PARTITION BY job ORDER BY score DESC) s_rank
FROM grade

id	job	score	s_rank
6	B	12000	1
7	B	11000	2
8	B	9999	3
1	C++	11001	1
2	C++	10000	2
3	C++	9000	3
5	Java	13000	1
4	Java	12000	2

3.之后将上面查询到的结果进行连接，查询s_rank在区间start, end之间的数据。之后按照id进行排序。

SELECT r2.*
FROM (SELECT job, round(COUNT(id)/2) `start`, round((COUNT(id)+1)/2) `end`
      FROM grade
      GROUP BY job
      ORDER BY job) r1
      JOIN
      (SELECT *, row_number() over(PARTITION BY job ORDER BY score DESC) s_rank
       FROM grade) r2
ON r1.job = r2.job AND r2.s_rank BETWEEN `start` AND `end`
ORDER BY r2.id

id	job	score	s_rank
2	C++	10000	2
4	Java	12000	2
5	Java	13000	1
7	B	11000	2