原题链接:牛客SQL269 考试分数(四)
题目
每次考试完,都会有一个成绩表(grade),如下:
表grade
id | job | score |
---|---|---|
1 | C++ | 11001 |
2 | C++ | 11000 |
3 | C++ | 9000 |
4 | JAVA | 12000 |
5 | JAVA | 13000 |
6 | B | 12000 |
7 | B | 11000 |
8 | B | 9999 |
第1行表示用户id为1的用户选择了C++岗位并且考了11001分
。。。
第8行表示用户id为8的用户选择了B语言岗位并且考了9999分
请你写一个sql语句查询各个岗位分数升序排列之后的中位数位置的范围,并且按job升序排序,结果如下:
结果
job | start | end |
---|---|---|
B | 2 | 2 |
C++ | 2 | 2 |
Java | 1 | 2 |
具体思路就是以job为分组以score为排序依据,通过row_number得到排名,如果组内用户数为偶数,则中位数为 max( r ) / 2 和 max( r ) / 2 + 1, 若为奇数则中位数只有一个值排名为 ceil( max( r ) / 2 )
select job, ceil(max(r) / 2) start,
case when max(r) % 2 = 0 then round(max(r) / 2) + 1 else ceil(max(r) / 2) end as end
from (
--先求出各组排名
select job, score,
row_number()over(partition by job order by score) r
from grade order by job, score
) t
group by job;