declare @need table(spid varchar(10),needqty int)--订单,spid是商品内码,NEEDQTY,需求数量
insert into @need select 'aaa',100 union all select 'bbb',100
DECLARE @store table(spid varchar(10),lot varchar(20),sxrq varchar(10),qty int)
insert into @store select 'aaa','050601','2008-08',60
union all select 'aaa','050718','2008-12',80
union all select 'bbb','t0506','2007-12',60
union all select 'bbb','t0721','2008-05',10declare @result table (spid varchar(10),lot varchar(20),sxrq varchar(10),ckqty int,syqty int,status varchar(20)) insert @result
select spid,lot,sxrq,0 as ckqty,qty as syqty,'Wait' as status from @store order by spid,sxrqdeclare @spid varchar(10),@qty int,@t int
declare cur CURSOR LOCAL for select * from @need
open cur
fetch next from cur into @spid,@qty
WHILE @@FETCH_STATUS = 0
BEGIN
update @Result
set @t=case when @qty>syqty then syqty else @qty end,
@qty=case when @qty>syqty then @qty-syqty else 0 end,
ckqty =@t,
syqty=syqty-@t,status='ok'
where spid=@spid
if @qty>0
insert @Result values(@spid,'','',@qty,-@qty,'need')
fetch next from cur into @spid,@qty
END
CLOSE cur
DEALLOCATE curselect * from @Result--结果
spid lot sxrq ckqty syqty status
---------- -------------------- ---------- ----------- ----------- --------------------
aaa 050601 2008-08 60 0 ok
aaa 050718 2008-12 40 40 ok
bbb t0506 2007-12 60 0 ok
bbb t0721 2008-05 10 0 ok
bbb 30 -30 need(所影响的行数为 5 行)
ps:你的插入数据是已经写好了,不过有错,你检查下
insert into @need select 'aaa',100 union all select 'bbb',100
DECLARE @store table(spid varchar(10),lot varchar(20),sxrq varchar(10),qty int)
insert into @store select 'aaa','050601','2008-08',60
union all select 'aaa','050718','2008-12',80
union all select 'bbb','t0506','2007-12',60
union all select 'bbb','t0721','2008-05',10declare @result table (spid varchar(10),lot varchar(20),sxrq varchar(10),ckqty int,syqty int,status varchar(20)) insert @result
select spid,lot,sxrq,0 as ckqty,qty as syqty,'Wait' as status from @store order by spid,sxrqdeclare @spid varchar(10),@qty int,@t int
declare cur CURSOR LOCAL for select * from @need
open cur
fetch next from cur into @spid,@qty
WHILE @@FETCH_STATUS = 0
BEGIN
update @Result
set @t=case when @qty>syqty then syqty else @qty end,
@qty=case when @qty>syqty then @qty-syqty else 0 end,
ckqty =@t,
syqty=syqty-@t,status='ok'
where spid=@spid
if @qty>0
insert @Result values(@spid,'','',@qty,-@qty,'need')
fetch next from cur into @spid,@qty
END
CLOSE cur
DEALLOCATE curselect * from @Result--结果
spid lot sxrq ckqty syqty status
---------- -------------------- ---------- ----------- ----------- --------------------
aaa 050601 2008-08 60 0 ok
aaa 050718 2008-12 40 40 ok
bbb t0506 2007-12 60 0 ok
bbb t0721 2008-05 10 0 ok
bbb 30 -30 need(所影响的行数为 5 行)
ps:你的插入数据是已经写好了,不过有错,你检查下
我想着先逐个取订单表的SPID,
然后再根据SPID逐个取STORE表的批次数量,再根据条件WHILE循环