表A
F_name F_nums F_date F_Flag
aaa 1 2007-05-01 1
bbb 2 2007-05-01 1
aaa 3 2007-05-02 1
bbb 1 2007-05-02 1
aaa 1 2007-05-03 0
bbb 1 2007-05-03 0统计出客户每天的累计合计数 (F_Flag=1为加,F_Flag=0为减)
结果如下:分别统计出各客户的累计数:如统计A客户,01号是1,02号就是4,03号是3(F_Flag=0为减)
F_date F_name F_leiji
2007-05-01 aaa 1
2007-05-02 aaa 4
2007-05-02 aaa 3
F_name F_nums F_date F_Flag
aaa 1 2007-05-01 1
bbb 2 2007-05-01 1
aaa 3 2007-05-02 1
bbb 1 2007-05-02 1
aaa 1 2007-05-03 0
bbb 1 2007-05-03 0统计出客户每天的累计合计数 (F_Flag=1为加,F_Flag=0为减)
结果如下:分别统计出各客户的累计数:如统计A客户,01号是1,02号就是4,03号是3(F_Flag=0为减)
F_date F_name F_leiji
2007-05-01 aaa 1
2007-05-02 aaa 4
2007-05-02 aaa 3
(select F_nums*(case when F_Flag 0 then -1 else 1 end) from 表A where a.F_name=F_name and a.F_date>=F_date) F_leiji
from 表A a
insert T select 'aaa',1,'2007-05-01',1
insert T select 'bbb',2,'2007-05-01',1
insert T select 'aaa',3,'2007-05-02',1
insert T select 'bbb',1,'2007-05-02',1
insert T select 'aaa',1,'2007-05-03',0
insert T select 'bbb',1,'2007-05-03',0select
F_date,F_name,
(select sum(case when F_Flag=0 then -F_nums else F_nums end) from T where F_name=a.F_name and F_date<=a.F_date) F_leiji
from T a group by F_name,F_date order by F_name,F_date
drop table T--结果
F_date F_name F_leiji
------------------------------------------------------ ---------- -----------
2007-05-01 00:00:00.000 aaa 1
2007-05-02 00:00:00.000 aaa 4
2007-05-03 00:00:00.000 aaa 3
2007-05-01 00:00:00.000 bbb 2
2007-05-02 00:00:00.000 bbb 3
2007-05-03 00:00:00.000 bbb 2(所影响的行数为 6 行)
(select F_nums*(case when F_Flag 0 then -1 else 1 end) from 表A where a.F_name=F_name and a.F_date>=F_date) F_leiji
from 表A a
INSERT INTO AG
SELECT 'aaa', 1 , '2007-05-01' , 1
UNION SELECT 'bbb', 2 , '2007-05-01' , 1
UNION SELECT 'aaa', 3 , '2007-05-02' , 1
UNION SELECT 'bbb', 1 , '2007-05-02' , 1
UNION SELECT 'aaa', 1 , '2007-05-03' , 0
UNION SELECT 'bbb', 1 , '2007-05-03' , 0
SELECT F_name,F_date, (select SUM( CASE WHEN F_Flag=1 then F_nums else -(F_nums) end ) from AG b
where a.F_name=b.F_name and a.F_date>=b.F_date ) as F_leiji
FROM AG a
order by F_name,F_date--result
aaa 2007-05-01 00:00:00.000 1
aaa 2007-05-02 00:00:00.000 4
aaa 2007-05-03 00:00:00.000 3
bbb 2007-05-01 00:00:00.000 2
bbb 2007-05-02 00:00:00.000 3
bbb 2007-05-03 00:00:00.000 2