--三个数据插入#t测试
select 141 ZCNum,0 STNum, 1 ID into #t union all
select 124 , 0 , 2 union all
select 140 , 0 , 3 select * from #t
--更新逻辑:主要是获取sum的那段
Update t set t.STNum=SumZC
from #t as t
join (
select t.ID,t.ZCNum,sum(p.ZCNum) as SumZC
from #t as t
join #t as p on t.ID>=p.ID
group by t.ZCNum,t.ID
) as r
on t.ID=r.ID
--最终数据
select * from #t
--删除表
drop table #t/*****************************************
(3 行受影响)
ZCNum STNum ID
----------- ----------- -----------
141 0 1
124 0 2
140 0 3(3 行受影响)(3 行受影响)ZCNum STNum ID
----------- ----------- -----------
141 141 1
124 265 2
140 405 3(3 行受影响)
*****************************************/
go
if OBJECT_ID('TolSum') is not null drop table tolsum
go
create table TolSum(
zcnum int,
stnum int,
id int
)
insert into TolSum
select 141,null,1 union all
select 124,null,2 union all
select 140,null,3
go
update TolSum set tolsum.stnum=c.snum from
(select a.id,sum(b.zcnum) as snum from TolSum as a,TolSum as b where a.id>=b.id
group by a.id) as c where TolSum.id=c.id
go
select * from TolSum
create table TolSum
(ZCNum int,STNum int,ID int)insert into TolSum
select 141,null,1 union all
select 124,null,2 union all
select 140,null,3
-- 更新
update a
set a.STNum=isnull((select sum(b.ZCNum)
from TolSum b
where b.ID<=a.ID),0)
from TolSum a
-- 结果
select * from TolSum
/*
ZCNum STNum ID
----------- ----------- -----------
141 141 1
124 265 2
140 405 3(3 行受影响)
*/