考试分数(五)

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

原站题解

上次编辑到这里，代码来自缓存点击恢复默认模板

Sqlite 解法, 执行用时: 10ms, 内存消耗: 3516KB, 提交时间: 2021-11-22

select id,job,score,rn
from 
(select *
        ,(row_number()over(partition by job order by score desc))as rn
	    ,(count(score)over(partition by job))as count
        from grade)t1
WHERE ABS(rn -(count+1)*1.0/2)in (0.5,0)
order by id

Sqlite 解法, 执行用时: 10ms, 内存消耗: 3864KB, 提交时间: 2021-09-08

select g1.* 
from (
select id,job,score,row_number() over(partition by job order by score desc) t_rank
from grade ) g1,
(select job,
case when count(*)%2=1 then count(*)/2+1 
    else count(*)/2 end as start,
case when count(*)%2=1 then count(*)/2+1 
    else count(*)/2+1 end as end
from grade
group by job) g2
where g1.job=g2.job and ( g1.t_rank =g2.start or g1.t_rank =g2.end)
order by g1.id

Sqlite 解法, 执行用时: 11ms, 内存消耗: 3428KB, 提交时间: 2020-10-12

select t.* from 
(
    select id, job, score, 
    row_number() over(partition by job order by score desc) as rank
    from grade
) as t 
where 
t.rank - (
    (
        select count(*) from grade g where g.job = t.job group by job)+1)*1.0/2 >= -0.5
        and
        t.rank - (
            (select count(*) from grade g where g.job = t.job group by job)+1)*1.0/2 <= 0.5
order by id

Sqlite 解法, 执行用时: 11ms, 内存消耗: 3428KB, 提交时间: 2020-09-28

select t.* from 
(select id, job, score, 
row_number() over(partition by job order by score desc) as rank
from grade) as t 
where 
t.rank - ((select count(*) from grade g where g.job = t.job group by job)+1)*1.0/2 >= -0.5
and
t.rank - ((select count(*) from grade g where g.job = t.job group by job)+1)*1.0/2 <= 0.5
order by id

Sqlite 解法, 执行用时: 11ms, 内存消耗: 3428KB, 提交时间: 2020-09-28

select n.*
from (select *, rank() over(partition by job order by score desc) as rank from grade) n
where n.rank - ((select count(*) from grade group by job having job = n.job)+1)*1.0/2 <= 0.5
and n.rank - ((select count(*) from grade group by job having job = n.job)+1)*1.0/2 >= -0.5
order by n.id;

上一题

下一题

详情