if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TStockDaily]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TStockDaily]
GO
Insert TStockDaily(R, C) Values(3, 2)
Insert TStockDaily(R, C) Values(5, 1)
Insert TStockDaily(R, C) Values(2, 0)
Insert TStockDaily(R, C) Values(7, 4)
Insert TStockDaily(R, C) Values(9, 4)
select 0 s,r,c,r-c zl
from tstockdaily a
where not exists(select 1 from tstockdaily b where a.oid>b.oid)
union
select sum(a.s)+sum(b.r)-sum(b.c) s,sum(a.r) r,sum(a.c),sum(a.s)+sum(b.r)-sum(b.c)+sum(a.r)-sum(a.c) zl
from tstockdaily a,tstockdaily b
where a.oid>b.oid
group by a.oid
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TStockDaily]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TStockDaily]
GO
Insert TStockDaily(R, C) Values(3, 2)
Insert TStockDaily(R, C) Values(5, 1)
Insert TStockDaily(R, C) Values(2, 0)
Insert TStockDaily(R, C) Values(7, 4)
Insert TStockDaily(R, C) Values(9, 4)
Update TStockDaily Set S = (Case When (Select Sum(ZL) From TStockDaily Where OID = A.OID -1) Is Null Then 0 Else (Select Sum(ZL) From TStockDaily Where OID = A.OID -1) End),
ZL = (Case When (Select Sum(ZL) From TStockDaily Where OID = A.OID -1) Is Null Then 0 Else (Select Sum(ZL) From TStockDaily Where OID = A.OID -1) End) + R - C
From TStockDaily A
Select * From TStockDaily
---------------------------结果
OID S R C ZL
----------- ----------- ----------- ----------- -----------
1 0 3 2 1
2 1 5 1 5
3 5 2 0 7
4 7 7 4 10
5 10 9 4 15