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',12,1,0.0351 union all
select 2,180050,'dy111117',14,1,0.075 union all
select 3,180050,'dy111108',18,9,2.5 
go
--测试数据表2
create table bitlstb2(sku int,qty int)
insert into bitlstb2
select 180050,20 

--测试数据表3
create table bitlstb3 
(id int primary key,sku int,batchno varchar(8),qty int,ordqty int,volume numeric(6,5))
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
go

declare @sql varchar(max) set @sql=''
select @sql=@sql+' exec updateqty '+ltrim(sku)+';' from bitlstb2
exec(@sql)
--bitlstb1 数据!
/*
id          sku         batchno  qty         ordqty      volume
----------- ----------- -------- ----------- ----------- ---------------------------------------
1           180050      dy101110 12          1           0.03510
2           180050      dy111117 14          1           0.07500
3           180050      dy111108 18          9           2.50000
*/
--bitlstb2 数据!
/*sku    qty
180050    20 */
--上面的存储过程没有问题,能按batchno字段升序得出正常的结果,现在需要把更新了那些数据插入到bitlstb3
--需要得到的结果bitlstb3
/*
id    sku    batchno    qty    ordqty    volume
1    180050    dy101110    0    1    0.0351
3    180050    dy111108    10    9    2.5
2    180050    dy111117    14    1    0.075
*/

go

--小爱之前写过一个比我这个效率高很多的，改一下就插入插入表3了。

--> 测试数据：[TB1]
if object_id('[TB1]') is not null drop table [TB1]
go 
create table [TB1]([SKU] int,[BatchNo] varchar(8),[Qty] int,[OrdQty] int,[Volume] numeric(6,5))
insert [TB1]
select 180050,'DY101110',12,1,0.0351 union all
select 180050,'DY111117',14,1,0.075 union all
select 180050,'DY111108',18,9,2.5 
--------------开始查询--------------------------
--> 测试数据：[TB2]
if object_id('[TB2]') is not null drop table [TB2]
go 
create table [TB2]([SKU] int,[Qty] int)
insert [TB2]
select 180050,20

if object_id('[TB3]') is not null drop table [TB3]
go 
create table [TB3]([SKU] int,[BatchNo] varchar(8),[Qty1] int,[Qty2] int,[OrdQty] int,[Volume] numeric(6,5))

--------------开始查询--------------------------

; with t as
(
    select *,id=row_number() over(order by BatchNo) from[TB1]
)
update a set 
    a.Qty= case when b.[Qty]>(select sum([Qty]) from t where [id]<=a.[id])
           then 0
           when b.[Qty]>(select sum([Qty]) from t where [id]<a.[id]) 
                and b.[Qty]<(select sum([Qty]) from t where [id]<=a.[id]+1)
           then a.[Qty]-(b.[Qty]-(select sum([Qty]) from t where [id]<a.[id]))
           else a.[Qty]
          end
output deleted.[SKU],
deleted.[BatchNo],
deleted.[Qty],
inserted.[Qty],
deleted.[OrdQty],
deleted.[Volume]
into [TB3] from t a,[TB2] b 

select * from tb1  order by BatchNo
/*
SKU         BatchNo  Qty         OrdQty      Volume
----------- -------- ----------- ----------- ---------------------------------------
180050      DY101110 0           1           0.03510
180050      DY111108 10          9           2.50000
180050      DY111117 14          1           0.07500
*/

--Qty1是修改前的值，Qty2是修改后的值
select * fro