表A F_date F_name F_num1 F_num2
2007-05-01 AAA 3 2
2007-05-02 AAA 4 3
2007-05-03 AAA 6 3
2007-05-04 AAA 10 8统计客户AAA每天的累计欠款(累计欠款=F_num1-F_num2) 结果如下 F_date F_name F_qian
2007-05-01 AAA 1 (1号欠1)
2007-05-02 AAA 2 (2号欠1加上1号的等于2)
2007-05-03 AAA 5 (3号欠3加上前两天)
2007-05-04 AAA 7 该怎么做?
2007-05-01 AAA 3 2
2007-05-02 AAA 4 3
2007-05-03 AAA 6 3
2007-05-04 AAA 10 8统计客户AAA每天的累计欠款(累计欠款=F_num1-F_num2) 结果如下 F_date F_name F_qian
2007-05-01 AAA 1 (1号欠1)
2007-05-02 AAA 2 (2号欠1加上1号的等于2)
2007-05-03 AAA 5 (3号欠3加上前两天)
2007-05-04 AAA 7 该怎么做?
insert a
select '2007-05-01' , 'AAA' , 3 , 2
union all select '2007-05-02' , 'AAA' , 4 , 3
union all select '2007-05-03' , 'AAA' , 6 , 3
union all select '2007-05-04' , 'AAA' , 10 , 8select F_date,F_name,F_qian=(select c=sum(F_num1)-sum(F_num2) from a where t.F_name=F_name and t.F_date>=F_date group by F_name)
from a t
drop table a
insert a
select '2007-05-01' , 'AAA' , 3 , 2
union all select '2007-05-02' , 'AAA' , 4 , 3
union all select '2007-05-03' , 'AAA' , 6 , 3
union all select '2007-05-04' , 'AAA' , 10 , 8
go -- 更新处理
-- 增加累计欠款及索引
alter table a add 累计欠款 int
CREATE INDEX IX_update ON a(
F_name)
GO-- 生成剩余金额值
declare @a int, @b varchar(10)
update AA set
@a = CASE WHEN @b = F_name THEN @a + F_num1 - F_num2 ELSE F_num1 - F_num2 END,
累计欠款 = @a,
@b = F_name
from a AA WITH(INDEX(IX_update))
GO
-- 显示结果
select * from a
order by F_name
godrop table a
select '2007-05-01' , 'AAA' , 3 , 2
union all select '2007-05-02' , 'AAA' , 4 , 3
union all select '2007-05-03' , 'AAA' , 6 , 3
union all select '2007-05-04' , 'AAA' , 10 , 8 union all select
'2007-05-01' , 'BBB' , 3 , 2
union all select '2007-05-02' , 'BBB' , 4 , 3
union all select '2007-05-03' , 'BBB' , 6 , 3
union all select '2007-05-04' , 'CCC' , 10 , 8select * from Hegemon
select A.F_date,A.F_name ,F_qian = (select sum(F_num1-F_num2) as F_qian from Hegemon B where A.F_date >= B.F_date and A.F_name =B.F_name
group by F_name)
from Hegemon A F_date F_name F_qian
2007-05-01 00:00:00.000 AAA 1
2007-05-02 00:00:00.000 AAA 2
2007-05-03 00:00:00.000 AAA 5
2007-05-04 00:00:00.000 AAA 7
2007-05-01 00:00:00.000 BBB 1
2007-05-02 00:00:00.000 BBB 2
2007-05-03 00:00:00.000 BBB 5
2007-05-04 00:00:00.000 CCC 2