sql 让库存根据字段数量进行扣除

建立测试表、添加数据

create table #check_wl(
ids int,
name varchar(200),
sex int,
stocks int,
nums int
)

insert into #check_wl values(1,'001',1,1000,100)
insert into #check_wl values(2,'001',2,1000,200)
insert into #check_wl values(3,'002',1,1000,300)
insert into #check_wl values(4,'002',2,1000,400)
insert into #check_wl values(5,'002',3,1000,500)

select *From #check_wl

原表数据：
ids name sex stocks nums
1 001 1 1000 100
2 001 2 1000 200
3 002 1 1000 300
4 002 2 1000 400
5 002 3 1000 500

进行库存扣除

select ID=identity(int,1,1),* into # from #check_wl

update #check_wl set stocks=case when #check_wl.stocks>=(select isnull(SUM(nums),0) from # where id<a.ID and name=a.name)
then #check_wl.stocks-(select isnull(SUM(nums),0) from # where ID<a.ID and name=a.name) else 0 end
from  #check_wl,# a
where #check_wl.sex=a.sex and #check_wl.name=a.name


select *From # 

select *From #check_wl

扣除后表数据：
ids name sex stocks nums
1 001 1 1000 100
2 001 2 900 200
3 002 1 1000 300
4 002 2 700 400
5 002 3 300 500

完整代码

create table #check_wl(
ids int,
name varchar(200),
sex int,
stocks int,
nums int
)

insert into #check_wl values(1,'001',1,1000,100)
insert into #check_wl values(2,'001',2,1000,200)
insert into #check_wl values(3,'002',1,1000,300)
insert into #check_wl values(4,'002',2,1000,400)
insert into #check_wl values(5,'002',3,1000,500)

select *From #check_wl


select ID=identity(int,1,1),* into # from #check_wl

update #check_wl set stocks=case when #check_wl.stocks>=(select isnull(SUM(nums),0) from # where id<a.ID and name=a.name)
then #check_wl.stocks-(select isnull(SUM(nums),0) from # where ID<a.ID and name=a.name) else 0 end
from  #check_wl,# a
where #check_wl.sex=a.sex and #check_wl.name=a.name


select *From # 

select *From #check_wl

drop table #check_wl
drop table #