老有关问题,搞了二天了,还没有搞定.郁闷啊

create 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',12,1,0.0351 union allselect 2,180050,'dy111117',14,1,0.075 union allselect 3,180050,'dy111108',18,9,2.5 go--测试数据表2create table bitlstb2(sku int,qty int)insert into bitlstb2select 180050,20 --测试数据表3create 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)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]endgodeclare @sql varchar(max) set @sql=''select @[email protected]+' exec updateqty '+ltrim(sku)+';' from bitlstb2exec(@sql)--bitlstb1 数据!/*id          sku         batchno  qty         ordqty      volume----------- ----------- -------- ----------- ----------- ---------------------------------------1           180050      dy101110 12          1           0.035102           180050      dy111117 14          1           0.075003           180050      dy111108 18          9           2.50000*/--bitlstb2 数据!/*sku    qty180050    20 */--上面的存储过程没有问题,能按batchno字段升序得出正常的结果,现在需要把更新了那些数据插入到bitlstb3--需要得到的结果bitlstb3/*id    sku    batchno    qty    ordqty    volume1    180050    dy101110    0    1    0.03513    180050    dy111108    10    9    2.52    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 allselect 180050,'DY111117',14,1,0.075 union allselect 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,20if 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]          endoutput 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.03510180050      DY111108 10          9           2.50000180050      DY111117 14          1           0.07500*/--Qty1是修改前的值，Qty2是修改后的值select * from [TB3] order by BatchNo/*SKU         BatchNo  Qty1        Qty2        OrdQty      Volume----------- -------- ----------- ----------- ----------- ---------------------------------------180050      DY101110 12          0           1           0.03510180050      DY111108 18          10          9           2.50000180050      DY111117 14          14          1           0.07500*/