- SQL code
--库存表/* TB1SKU BatchNo Qty OrdQty Volume180050 DY101110 1 1 0.0351180050 DY111117 1 1 0.075180050 DY111108 18 9 2.5180050 DY111109 7 4 1.40832*/TB2--发货表/*SKU Qty180050 20*/--更新后库存表/*SKU BatchNo Qty OrdQty Volume180050 DY101110 0 1 0.0351180050 DY111108 0 9 2.5180050 DY111109 6 4 1.40832180050 DY111117 1 1 0.075*/--求一更新语句,BatchNo字段升序排序后,UPDATE->QTY字段--半把操作的库存表的字段插入后TB3/*TB3SKU BatchNo Qty OrdQty Volume180050 DY101110 1 1 0.0351180050 DY111108 18 9 2.5180050 DY111109 1 4 1.40832*/
在线等,解决后,马上结贴!
------解决方案--------------------
------解决方案--------------------
- SQL code
--多个编号的更新--测试数据表1(我把主键id加上了)gocreate table bitlstb1 (id int primary key,sku int,batchno varchar(8),qty int,ordqty int,volume numeric(6,5))insert into bitlstb1select 1,180050,'dy101110',1,1,0.0351 union allselect 2,180050,'dy111117',1,1,0.075 union allselect 3,180050,'dy111108',18,9,2.5 union allselect 4,180050,'dy111109',7,4,1.40832 union allselect 5,180051,'dy101110',3,1,0.0351 union allselect 6,180051,'dy111117',2,1,0.075 union allselect 7,180051,'dy111108',18,9,2.5 union allselect 8,180051,'dy111109',7,4,1.40832go--测试数据表2create table bitlstb2(sku int,qty int)insert into bitlstb2select 180050,20 union allselect 180051,10 go--创建一个存储过程create proc updateqty(@p int)asbegin --得到发货数量 declare @qty int select @qty=qty from bitlstb2 where [email protected] declare @j varchar(20);declare @k int --逐行更新 ;with maco as ( select * from bitlstb1 where [email protected]), maco1 as(select *,zqty=(select sum(qty) from maco where batchno<=a.batchno) from maco a) select top 1 @j=batchno,@[email protected] from maco1 where zqty>[email protected] order by batchno update bitlstb1 set [email protected] where [email protected] and [email protected] update bitlstb1 set qty=0 where [email protected] and batchno<@j and [email protected]enddeclare @sql varchar(max) set @sql=''select @[email protected]+' exec updateqty '+ltrim(sku)+';' from bitlstb2exec(@sql)select * from bitlstb1/*id sku batchno qty ordqty volume----------- ----------- -------- ----------- ----------- ---------------------------------------1 180050 dy101110 0 1 0.035102 180050 dy111117 1 1 0.075003 180050 dy111108 0 9 2.500004 180050 dy111109 6 4 1.408325 180051 dy101110 0 1 0.035106 180051 dy111117 2 1 0.075007 180051 dy111108 11 9 2.500008 180051 dy111109 7 4 1.40832*/drop table bitlstb1,bitlstb2
------解决方案--------------------
- SQL code
create table tb(SKU int,BatchNo varchar(8),Qty int,OrdQty int,Volume numeric(6,5))insert into tbselect 180050,'DY101110',1,1,0.0351 union allselect 180050,'DY111117',1,1,0.075 union allselect 180050,'DY111108',18,9,2.5 union allselect 180050,'DY111109',7,4,1.40832gocreate table tb2(sku int,qty int)insert into tb2select 180050,20godeclare @batch varchar(max)declare @qty intdeclare @newqty int;with ach as( select a.*,b.qty newqty from tb a join tb2 b on a.sku = b.sku where (select sum(qty) from tb where sku=a.sku and BatchNo<=a.BatchNo) >= b.qty and (select sum(qty) from tb where sku=a.sku and BatchNo<a.BatchNo) <= b.qty)update aset @qty=(select sum(qty) from tb where sku=a.sku and BatchNo<=a.BatchNo), @newqty = b.newqty - @qty, a.qty = (case when @newqty<=0 then @qty-b.newqty when @newqty>0 then 0 end)from tb a join ach b on a.sku = b.sku and a.BatchNo<=b.BatchNoselect * from tbdrop table tb,tb2/**************************SKU BatchNo Qty OrdQty Volume----------- -------- ----------- ----------- ---------------------------------------180050 DY101110 0 1 0.03510180050 DY111117 1 1 0.07500180050 DY111108 0 9 2.50000180050 DY111109 6 4 1.40832(4 行受影响)