SQL270 考试分数(五)
- 题目
- 题解(105)
- 讨论(312)
- 排行
- 面经
new
困难 通过率:28.22% 时间限制:1秒 空间限制:32M
描述
牛客每次考试完,都会有一个成绩表(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语句查询各个岗位分数的中位数位置上的所有grade信息,并且按id升序排序,结果如下:
id | job | score | t_rank |
2 | C++ | 10000 | 2 |
4 | Java | 12000 | 2 |
5 | Java | 13000 | 1 |
7 | B | 11000 | 2 |
解释:
第1行表示C++岗位的中位数位置上的为用户id为2,分数为10000,在C++岗位里面排名是第2
第2,3行表示Java岗位的中位数位置上的为用户id为4,5,分数为12000,13000,在Java岗位里面排名是第2,1
第4行表示B语言岗位的中位数位置上的为用户id为7,分数为11000,在前端岗位里面排名是第2
(注意: sqlite 1/2得到的不是0.5,得到的是0,只有1*1.0/2才会得到0.5,sqlite四舍五入的函数为round,sqlite不支持floor函数,支持cast(x as integer) 函数,不支持if函数,支持case when ...then ...else ..end函数,sqlite不支持自定义变量)
示例1
输入:
drop table if exists grade; CREATE TABLE grade( `id` int(4) NOT NULL, `job` varchar(32) NOT NULL, `score` int(10) NOT NULL, PRIMARY KEY (`id`)); INSERT INTO grade VALUES (1,'C++',11001), (2,'C++',10000), (3,'C++',9000), (4,'Java',12000), (5,'Java',13000), (6,'B',12000), (7,'B',11000), (8,'B',9999);
复制输出:
2|C++|10000|2 4|Java|12000|2 5|Java|13000|1 7|B|11000|2
select
id,job,score,rnk2 t_rank
from
(
select
id,job,score,count(1) over(partition by job) cnt
,row_number() over(partition by job order by score) rnk1
,row_number() over(partition by job order by score desc) rnk2
from
grade
) s1
where round((1+cnt)/2) >= rnk1 and round((1+cnt)/2) >= rnk2
order by id