老问题,搞了二天了,还没有搞定.郁闷啊!!--在线等!
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 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=@pendgodeclare @sql varchar(max) set @sql=''select @sql=@sql+' 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*/
[解决办法]
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 sku=@p declare @j varchar(20);declare @k int declare @str varchar(max) set @str=' create trigger my_trig on InventoryList for update as begin insert into InventoryList_LS select * from deleted d where not exists( select 1 from inserted i where i.id = d.id and i.sku = d.sku and i.batchno = d.batchno and i.qty = d.batchno and i.ordqty = d.batchno and i.volume = d.volume and i.kuwei=d.kuwei and i.SkuName=d.SkuName) end' exec(@str) declare @sss varchar(20) set @sss='drop trigger my_trig' --逐行更新 ;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 exec(@sss)endgodeclare @sql varchar(max) set @sql=''select @sql=@sql+' exec updateqty '+ltrim(sku)+';' from bitlstb2exec(@sql)