错了,不要“Qty”+:
select ID ,PartNo,Qty,Total=(Qty+Total) from tb_name where ID=1
union
select ID ,PartNo,Qty,Total=select Total=sum(Qty) from tb_name where ID in(1,2)) from tb_name where ID=2
union
select ID ,PartNo,Qty,Total=select Total=sum(Qty) from tb_name where ID in(1,2,3)) ) from tb_name where ID=3
union
select ID ,PartNo,Qty,Total=select Total=sum(Qty) from tb_name where ID<>4 ) from tb_name where ID=4
昨天刚给人做过一个相减的,比减法简单一点,用一个select语句:类似楼上
select ID ,PartNo,Qty,Total=(Qty+Total) from tb_name where ID=1
union
select ID ,PartNo,Qty,Total=Qty+(select Total=sum(Qty) from tb_name where ID in(1,2)) from tb_name where ID=2
union
select ID ,PartNo,Qty,Total=Qty+(select Total=sum(Qty) from tb_name where ID in(1,2,3)) ) from tb_name where ID=3
union
select ID ,PartNo,Qty,Total=Qty+(select Total=sum(Qty) from tb_name where ID<>4 ) from tb_name where ID=4
declare @tb table
(
ID int,
PartNo varchar(10),
Qty int,
Total int
)
insert @tb
select 1,'A',1,0 union
select 2,'A',10,0 union
select 3,'A',20,0 union
select 4,'A',15,0
--测试
update @tb
set [Total]=(select sum(Qty) from @tb where ID<=t.ID)
from @tb t
--查看
select * from @tb
--结果
/*
ID PartNo Qty Total
----------- ---------- ----------- -----------
1 A 1 1
2 A 10 11
3 A 20 31
4 A 15 46