--库存表
/* 
TB1
SKU    BatchNo     Qty    OrdQty    Volume
180050    DY101110    1    1    0.0351
180050    DY111117    1    1    0.075
180050    DY111108    18    9    2.5
180050    DY111109    7    4    1.40832
*/
TB2
--发货表
/*
SKU    Qty
180050    20
*/
--更新后库存表
/*
SKU    BatchNo     Qty    OrdQty    Volume
180050    DY101110    0    1    0.0351
180050    DY111108    0    9    2.5
180050    DY111109    6    4    1.40832
180050    DY111117    1    1    0.075
*/
--求一更新语句,BatchNo字段升序排序后,UPDATE->QTY字段
--半把操作的库存表的字段插入后TB3
/*
TB3
SKU    BatchNo     Qty    OrdQty    Volume
180050    DY101110    1    1    0.0351
180050    DY111108    18    9    2.5
180050    DY111109    1    4    1.40832
*/

--多个编号的更新
--测试数据表1(我把主键id加上了)
go
create table bitlstb1 
(id int primary key,sku int,batchno varchar(8),qty int,ordqty int,volume numeric(6,5))
insert into bitlstb1

select 1,180050,'dy101110',1,1,0.0351 union all
select 2,180050,'dy111117',1,1,0.075 union all
select 3,180050,'dy111108',18,9,2.5 union all
select 4,180050,'dy111109',7,4,1.40832 union all
select 5,180051,'dy101110',3,1,0.0351 union all
select 6,180051,'dy111117',2,1,0.075 union all
select 7,180051,'dy111108',18,9,2.5 union all
select 8,180051,'dy111109',7,4,1.40832

go
--测试数据表2
create table bitlstb2(sku int,qty int)
insert into bitlstb2
select 180050,20 union all
select 180051,10 


go
--创建一个存储过程
create proc updateqty(@p int)
as
begin
    --得到发货数量
    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=@p
end


declare @sql varchar(max) set @sql=''
select @sql=@sql+' exec updateqty '+ltrim(sku)+';' from bitlstb2
exec(@sql)

select * from bitlstb1
/*
id          sku         batchno  qty         ordqty      volume
----------- ----------- -------- ----------- ----------- ---------------------------------------
1           180050      dy101110 0           1           0.03510
2           180050      dy111117 1           1           0.07500
3           180050      dy111108 0           9           2.50000
4           180050      dy111109 6           4           1.40832
5           180051      dy101110 0           1           0.03510
6           180051      dy111117 2           1           0.07500
7           180051      dy111108 11          9           2.50000
8           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 tb
select 180050,'DY101110',1,1,0.0351 union all
select 180050,'DY111117',1,1,0.075 union all
select 180050,'DY111108',18,9,2.5 union all
select 180050,'DY111109',7,4,1.40832
go

create table tb2(sku int,qty int)
insert into tb2
select 180050,20
go

declare @batch varchar(max)
declare @qty int
declare @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
)

up