create table #Atest00 (fid int,invoiceid varchar(5),amount float)
create table #Atest01 (fid int,billid varchar(5),amount float,remainamount float)
create table #Atest03 (fid int,invoiceid varchar(5),invamount float,billid varchar(5),billamount float)insert into #Atest00 values(1,'I01',1000)
insert into #Atest00 values(1,'I02',2000)
insert into #Atest00 values(1,'I03',3000)
insert into #Atest01 values(1,'B01',2500,2500)
insert into #Atest01 values(1,'B02',3500,3500)select * into #a from #Atest00 where fid=1 order by invoiceid
select * into #b from #Atest01 where fid=1 order by billid
declare @a varchar(100),@b varchar(100),@c varchar(100),@d varchar(100)
while exists(select 1 from #a where amount>0) and exists(select 1 from #b where amount>0)
begin
select @a=a.fid,@b=a.invoiceid,
@c=case when a.amount<b.amount then a.amount else b.amount end,@d=b.billid
from (select top 1 * from #a where amount>0) a ,(select top 1 * from #b where amount>0) b insert #Atest03 values(@a,@b,@c,@d,@c)
update #a set amount=amount-@c where invoiceid=(select min(invoiceid) from #a where amount>0)
update #b set amount=amount-@c where billid=(select min(billid) from #b where amount>0)end
select * from #Atest03go
drop table #Atest00,#Atest01,#Atest03,#a,#b
create table #Atest01 (fid int,billid varchar(5),amount float,remainamount float)
create table #Atest03 (fid int,invoiceid varchar(5),invamount float,billid varchar(5),billamount float)insert into #Atest00 values(1,'I01',1000)
insert into #Atest00 values(1,'I02',2000)
insert into #Atest00 values(1,'I03',3000)
insert into #Atest01 values(1,'B01',2500,2500)
insert into #Atest01 values(1,'B02',3500,3500)select * into #a from #Atest00 where fid=1 order by invoiceid
select * into #b from #Atest01 where fid=1 order by billid
declare @a varchar(100),@b varchar(100),@c varchar(100),@d varchar(100)
while exists(select 1 from #a where amount>0) and exists(select 1 from #b where amount>0)
begin
select @a=a.fid,@b=a.invoiceid,
@c=case when a.amount<b.amount then a.amount else b.amount end,@d=b.billid
from (select top 1 * from #a where amount>0) a ,(select top 1 * from #b where amount>0) b insert #Atest03 values(@a,@b,@c,@d,@c)
update #a set amount=amount-@c where invoiceid=(select min(invoiceid) from #a where amount>0)
update #b set amount=amount-@c where billid=(select min(billid) from #b where amount>0)end
select * from #Atest03go
drop table #Atest00,#Atest01,#Atest03,#a,#b
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货