请教一个更新余数的问题，不用游标的情况下

[liuri璇玑]

请教一个更新余数的问题，不用游标的情况下

A表

产品(material) 总余数(remain_amount)
DM1 5000
DM2 3000
0

B表

产品 出库数 结余
DM1 2000 0
DM1 1000 0
DM2 500 0

希望更新B表的结余


产品 出库数 结余
DM1 2000 3000
DM1 1000 2000
DM2 500 2500

没分了,请多多包涵：Ｄ

[liuri璇玑]

先占个位，悉听各路高人分解～～～～～～～

[子陌红尘]

借助辅助自增列：
-------------------------------------------------------------------------------------
create table A(material varchar(10),remain_amount int)
insert into A select 'DM1',5000
insert into A select 'DM2',3000

create table B(material varchar(10),out int,remain_amount int)
insert into B select 'DM1',2000,0
insert into B select 'DM1',1000,0
insert into B select 'DM2',500 ,0
go

alter table B add id int identity(1,1)
go

update t2
set
remain_amount=A.remain_amount-(select sum(out) from B where material=t2.material and id<=t2.id)
from
A,B t2
where
A.material=t2.material
go

alter table B drop column id
go

select * from B
/*
material out remain_amount
---------- ----------- -------------
DM1 2000 3000
DM1 1000 2000
DM2 500 2500
*/

drop table A,B

[pengdali]

create table #A(material varchar(10),remain_amount int)
insert into #A select 'DM1',5000
insert into #A select 'DM2',3000

create table #B(material varchar(10),out int,remain_amount int)
insert into #B select 'DM1',2000,0
insert into #B select 'DM1',1000,0
insert into #B select 'DM2',500 ,0


select material,0 flag,0 [out],remain_amount into # from #a
union all
select material,1,[out],remain_amount from #b
order by material,flag

declare @i int,@material varchar(10)
update # set @i=case when material=@material then @i-out else remain_amount end,@material=material,remain_amount=@i
select * from # where flag=1

go
drop table #a,#b,#

[子陌红尘]

嘿嘿，看看璇玑家的钻石。

[liuri璇玑]

搞定了,谢谢子陌:P