我正在尝试从给定的表中获取所有这些"num"，其中"OUT" > "IN"

Question

我们可以为一个"num"价值。我们需要按num的升序输出。

表:

create table bill
( 
type varchar(5),
num varchar(12),
dur int
);

insert into bill values
('OUT',1818,13),
('IN', 1818,10),
('OUT',1818,7),
('OUT',1817,15),
('IN',1817,18),
('IN',1819,18),
('OUT',1819,40),
('IN',1819,18)

这就是我要查询的:我正在按"类型"对记录进行分组。在不同的子查询中获取"OUT"比;"IN".

select a.num
from 
(select num,sum(dur) as D
from bill  
where type ='OUT'
group by num) a ,
(select num,sum(dur) as D
from bill  
where type ='IN'
group by num) b 

where a.D > b.D
group by a.num
order by 1
My output:     Expected output:
num             num
1817           1818
1818           1819
1819

谢谢

Answer 1

使用条件聚合:

SELECT num
FROM bill
GROUP BY num
HAVING SUM(CASE WHEN type = 'OUT' THEN dur ELSE 0 END) >
SUM(CASE WHEN type = 'IN' THEN dur ELSE 0 END);

Answer 2

我会在子查询中使用条件聚合函数并添加type in ('IN','OUT')，如果您在type的第一列上创建索引，可能会获得更好的性能

SELECT num
FROM (
SELECT num,
SUM(CASE WHEN type = 'OUT' THEN dur ELSE 0 END) outval, 
SUM(CASE WHEN type = 'IN' THEN dur ELSE 0 END) inval
FROM bill  
WHERE type in ('IN','OUT')
GROUP BY num
) t1
WHERE outval > inval

sqlfiddle