mysql - 获取累计和

Question

假设我在 mysql 数据库中有一个表，如下所示，

date        inQty   outQty
2011-10-24  700.00  0.0
2011-10-01  500.00  0.0
2011-10-02  500.00  0.0
2011-10-03  550.00  0.0
2011-10-04  100.00  0.0 
2011-10-05  200.00  0.0
2011-10-05  0.00    100.0
2011-10-02  0.00    500.0
2011-10-03  0.00    150.0
2011-10-24  200.00  0.0

从上表中我需要查询以下结果，

date        inQty   outQty   Balance
2011-10-24  700.00  0.0      700.00
2011-10-01  500.00  0.0  500.00
2011-10-02  500.00  0.0  500.00
2011-10-03  550.00  0.0      550.00
2011-10-04  100.00  0.0  100.00
2011-10-05  200.00  0.0      200.00
2011-10-05  0.00    100.0    100.00
2011-10-02  0.00    500.0    0.0
2011-10-03  0.00    150.0    400.00
2011-10-24  200.00  0.0      500.00

如何从 SQL 查询中获取此信息？

Answer 1

我认为您正在寻找分区上的累积总和。但这只是一个猜测，因为您的帖子相当不清楚，因为数据没有明确的模式。我想我明白你的目的...

也许您可以查看并编辑您的帖子以使问题更清楚？

无论如何，请尝试一下。我不知道你的表叫什么，所以我会发布我自己的示例。

create table dateCumulative
(vDate date not null,
inQty decimal (12,2) not null default 0.0,
outQty decimal (12,2) not null default 0.0
);

insert into dateCumulative values ('2011-10-24',700.00,0.0);
insert into dateCumulative values ('2011-10-01',500.00,0.0);
insert into dateCumulative values ('2011-10-02',500.00,0.0);
insert into dateCumulative values ('2011-10-03',550.00,0.0);
insert into dateCumulative values ('2011-10-04',100.00,0.0);
insert into dateCumulative values ('2011-10-05',200.00,0.0);
insert into dateCumulative values ('2011-10-05',0.00,100.0);
insert into dateCumulative values ('2011-10-02',0.00,500.0);
insert into dateCumulative values ('2011-10-03',  0.00    ,150.0);
insert into dateCumulative values ('2011-10-24',  200.00  ,0.0);

select t.vDate,t.inQty,t.outQty,
round(t.inQtySum-t.outQtySum,2) as balance
from
( 
select 
(case when vDate = @curDate then (@inCsum := @inCsum + inQty) else @inCsum := inQty   end) as inQtySum,
(case when vDate = @curDate then (@outCsum := @outCsum + outQty) else @outCsum := outQty end) as outQtySum,
(case when vDate != @curDate then @curDate := vDate end) as dateChanged,
dc.*
from dateCumulative dc
inner join (SELECT @curDate := '1970-01-01',@inCsum:=0,@outCsum:=0) as t
order by vDate asc
) t;