考试分数(二)

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

第1行表示用户id为1的用户选择了C++岗位并且考了11001分

。。。

第8行表示用户id为8的用户选择了前端岗位并且考了9999分

请你写一个sql语句查询用户分数大于其所在工作(job)分数的平均分的所有grade的属性，并且以id的升序排序，如下:

id	job	score
1	C++	11001
5	Java	13000
6	JS	12000
7	JS	11000

(注意: sqlite 1/2得到的不是0.5，得到的是0，只有1*1.0/2才会得到0.5，sqlite四舍五入的函数为round)

示例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,'JS',12000),
(7,'JS',11000),
(8,'JS',9999),
(9,'Java',12500);

输出:

1|C++|11001
5|Java|13000
6|JS|12000
7|JS|11000

原站题解

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

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

select * from grade g
where score > (select avg(score) avg from grade g1
              where g.job = g1.job)
order by id;

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

select
    id,
    job,
    score
from grade g
where g.score > (select avg(g1.score) from grade g1
                 where g1.job = g.job)
order by id

Sqlite 解法, 执行用时: 10ms, 内存消耗: 3460KB, 提交时间: 2021-12-27

/*
select g.*
from grade as g
join (
    select job,avg(score) as avg
    from grade
    group by job
) as a on g.job = a.job and g.score > a.avg
order by g.id;
*/

select * from grade as g1
where g1.score > (
    select avg(score)
    from grade as g2
    where g2.job = g1.job)
order by g1.id;

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

select
t1.id
,t1.job
,t1.score
from grade t1 
left join(
select
job
,avg(score) avg_score
from grade
group by job)t2
on t1.job = t2.job
where t1.score > t2.avg_score
order by t1.id
;

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

select g1.* from grade g1
inner join (select job,avg(score) a from grade
group by job) g2
on g1.job=g2.job
where g1.score >g2.a
order by g1.id asc;

上一题

下一题

详情