想要上面得结果这样就可以了: declare @StkNum int set @StkNum=0 Update tb set StkNum=@StkNum, @StkNum=@StkNum+Num from tb go
Update tb set StkNum=(select sum(num) from tb where ID<=A.ID) FROM tb a with(index=PK_tb)
如果用变量,就用rivery 的写法
Update tb set StkNum=@StkNum,@StkNum=@StkNum+Num from tb with(index=PK_tb)
Update tb set StkNum=@StkNum+Num,@StkNum=@StkNum+Num from tb with(index=PK_tb) 这样写相当于加了两次Num ,用楼上的方法就对了 Update tb set StkNum=@StkNum,@StkNum=@StkNum+Num from tb with(index=PK_tb)
create table tb([ID] [int] IDENTITY (1, 1) NOT NULL constraint pk_tb primary key,ProductNo nvarchar(10),ProDate nvarchar(10),Num int,StkNum int)insert tb(ProductNo,Prodate,Num,StkNum) select '001', '2005/01/01', 100, 0 union all select '001', '2005/02/01' , 50, 0 union all select '001' , '2005/03/01' ,200, 0 union all select '001' , '2005/04/01' , 300, 0 union all select '001' , '2005/05/01' , 150 , 0select ProductNo,Prodate,Num,IDENTITY ( int) AS Id into #Temp1 from tb1
select ProductNo,Num,(select sum(Num) from #Temp1 where ProductNo=a.ProductNo and Id<=a.Id) as TTL from #Temp1 a 已测试过
這個就是我問得啊
并且同一个会话环境下,@stKNum 值不会重回初值0
SQL的优化器导致这样的效果的
第二次 :@StkNum = 100 + 50 ,Num = 100 ,StkNum =200
第三次 :@StkNum = 150 + 200 ,Num = 200 ,StkNum = 550
第四次 :@StkNum = 350 + 300 ,Num = 300 ,StkNum = 950
第五次 :@StkNum = 650 + 150 ,Num = 150 ,StkNum = 950
就是这个结果
declare @StkNum int
set @StkNum=0
Update tb
set StkNum=@StkNum,
@StkNum=@StkNum+Num
from tb
go
Update tb set StkNum=@StkNum,@StkNum=@StkNum+Num from tb with(index=PK_tb)
select '001', '2005/01/01', 100, 0 union all
select '001', '2005/02/01' , 50, 0 union all
select '001' , '2005/03/01' ,200, 0 union all
select '001' , '2005/04/01' , 300, 0 union all
select '001' , '2005/05/01' , 150 , 0select ProductNo,Prodate,Num,IDENTITY ( int) AS Id into #Temp1 from tb1
select ProductNo,Num,(select sum(Num) from #Temp1 where ProductNo=a.ProductNo and Id<=a.Id) as TTL
from #Temp1 a
已测试过