SQL282. 最差是第几名(二)
描述
| grade | number |
| A | 2 |
| C | 4 |
| B | 4 |
| D | 2 |
| grade |
| B |
| C |
示例1
输入:
drop table if exists class_grade;
CREATE TABLE class_grade (
grade varchar(32) NOT NULL,
number int(4) NOT NULL
);
INSERT INTO class_grade VALUES
('A',2),
('C',4),
('B',4),
('D',2);
输出:
B C
Sqlite 解法, 执行用时: 10ms, 内存消耗: 3684KB, 提交时间: 2021-08-07
select g.grade from
(
select c.grade,c.number,(
select sum(number) from class_grade a where c.grade>=a.grade order by a.grade desc) as num1,
( select sum(number) from class_grade a where c.grade<=a.grade order by a.grade ) as num2,
(select sum(number) from class_grade ) allNum
from class_grade c order by c.grade
) g
where g.num1*2>=g.allNum and g.num2*2>=g.allNum
Sqlite 解法, 执行用时: 11ms, 内存消耗: 3512KB, 提交时间: 2021-09-24
select grade
from
(select grade,
(select sum(number) from class_grade) as total,
sum(number)over(order by grade) a, -- 求正序
sum(number)over(order by grade desc) b -- 求逆序
from class_grade
order by grade)t
where a >= cast(total as float)/2 and b >= cast(total as float)/2 -- 正序逆序均大于整个数列数字个数的一半
order by grade
Sqlite 解法, 执行用时: 11ms, 内存消耗: 3516KB, 提交时间: 2022-01-27
select grade from (select grade,(select sum(number) from class_grade) as total,
sum(number) over(order by grade) a,
sum(number) over(order by grade desc) b
from class_grade) t1
where a >= total*1.0/2 and b >=total*1.0/2
order by grade;
Sqlite 解法, 执行用时: 11ms, 内存消耗: 3516KB, 提交时间: 2021-09-23
select grade
from (select grade ,sum(number)over(order by grade) cnt1,sum(number)over(order by grade desc) cnt2,
(select sum(number) from class_grade) totalsnt
from class_grade)a
where cnt1>=totalsnt*1.0/2
and cnt2>=totalsnt*1.0/2
order by grade
Sqlite 解法, 执行用时: 11ms, 内存消耗: 3584KB, 提交时间: 2022-02-13
select t.grade from
(select *,(select sum(number) from class_grade) total,
sum(number) over (order by grade) a,
sum(number) over (order by grade desc) b
from class_grade)t
where t.a>=total*1.0/2 and t.b>= total*1.0/2
order by grade