--表2好销
update tb_PrePay set ChargePrePay=case when a.ChargePrePay-b.Charge<=0 then 0 else a.ChargePrePay-b.Charge end
from tb_PrePay a join(
select AcctID,[Charge]=sum(Charge) from tb_Charge group by acctid
) b on a.acctid=b.acctid
update tb_PrePay set ChargePrePay=case when a.ChargePrePay-b.Charge<=0 then 0 else a.ChargePrePay-b.Charge end
from tb_PrePay a join(
select AcctID,[Charge]=sum(Charge) from tb_Charge group by acctid
) b on a.acctid=b.acctid
set @y2=0update tb_Charge
set @y1=case @accid when accid then @y2
else (select ChargePrePay from tb_PrePay where AcctID=a.AcctID)
end-Charge
,PayCharge=@y1,@y2=@y1
from tb_Charge a
declare @acctid int,@y1 int,@y2 int
update tb_Charge
set @y2=case @acctid when acctid then @y2
else (select ChargePrePay from tb_PrePay where AcctID=a.AcctID)
end
,@y1=case when @y2-Charge>0 then Charge else @y2 end
,@y2=@y2-@y1
,PayCharge=@y1,@acctid=acctid
from tb_Charge a--更新表tb_PrePay的处理
update tb_PrePay set ChargePrePay=case when a.ChargePrePay-b.Charge<=0 then 0 else a.ChargePrePay-b.Charge end
from tb_PrePay a join(
select AcctID,[Charge]=sum(Charge) from tb_Charge group by acctid
) b on a.acctid=b.acctid
declare @tb_Charge table(AcctID int,ChargeCode varchar(10),Charge int,PayCharge int)
insert into @tb_charge
select 1,'IDC',5000,0
union all select 1,'MAR',1200,0
union all select 2,'MCR',500,0
union all select 3,'IDC',2500,0
union all select 3,'MCR',800,0declare @tb_PrePay table(AcctID int,ChargePrePay int)
insert into @tb_PrePay
select 1,4500
union all select 2,1000
union all select 3,3000--更新表tb_Charge的处理
declare @acctid int,@y1 int,@y2 intupdate @tb_Charge
set @y2=case @acctid when acctid then @y2
else (select ChargePrePay from @tb_PrePay where AcctID=a.AcctID)
end
,@y1=case when @y2-Charge>0 then Charge else @y2 end
,@y2=@y2-@y1
,PayCharge=@y1,@acctid=acctid
from @tb_Charge a--更新表tb_PrePay的处理
update @tb_PrePay set ChargePrePay=case when a.ChargePrePay-b.Charge<=0 then 0 else a.ChargePrePay-b.Charge end
from @tb_PrePay a join(
select AcctID,[Charge]=sum(Charge) from @tb_Charge group by acctid
) b on a.acctid=b.acctid
--显示处理结果
select * from @tb_Charge
select * from @tb_PrePay/*--测试结果AcctID ChargeCode Charge PayCharge
----------- ---------- ----------- -----------
1 IDC 5000 4500
1 MAR 1200 0
2 MCR 500 500
3 IDC 2500 2500
3 MCR 800 500(所影响的行数为 5 行)
AcctID ChargePrePay
----------- ------------
1 0
2 500
3 0(所影响的行数为 3 行)
--*/
declare @acctid int,@y1 int,@y2 int
set @y2=0update tb_Charge
set @y1=case @accid when accid then @y2
else (select ChargePrePay from tb_PrePay where AcctID=a.AcctID)
end-Charge
,PayCharge=@y1,@y2=@y1
from tb_Charge a后,表1的数据变为如下:
AcctID ChargeCode Charge PayCharge
1 IDC 5000 -500
1 MAR 1200 3300
2 MCR 500 500
3 IDC 2500 500
3 MCR 800 2200其结果只是把表2中的ChargePrePay字段值-表1中的Charge字段值后再添加到表1中的PayCharge,所以还不是我要的结果,请zjcxc(邹建)或其它高手继续指点。
up
结贴。