求UPDATE语句.多谢

求UPDATE语句.在线等,谢谢

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 sku=@p    declare @j varchar(20);declare @k int     --逐行更新    ;with maco as ( select * from bitlstb1 where sku=@p),    maco1 as(select *,zqty=(select sum(qty) from maco where batchno<=a.batchno) from maco a)     select top 1 @j=batchno,@k=zqty-@qty from maco1 where zqty>=@qty order by batchno    update bitlstb1 set qty=@k where batchno=@j and sku=@p     update bitlstb1 set qty=0 where sku=@p and batchno<@j and sku=@penddeclare @sql varchar(max) set @sql=''select @sql=@sql+' 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 行受影响)