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

jobstartendB22C++22Java12

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

三个排序窗口函数的使用

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

idjobscores_rank6B1200017B1100028B999931C++1100112C++1000023C++900035Java1300014Java120002

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

idjobscores_rank2C++1000024Java1200025Java1300017B110002