4个表中有3个是关于费用的,并且有货币类型代码(rateID)和汇率(rate)表:Code_Rate,如果3个关于费用的表的的费用乘以汇率(rate)后得出的费用的总和,我用了联合查询,想问一下有没有别的更简便的方法(比如:不用联合查询)!谢谢!
select sum(金额) from (select Sum(a.Factfee*c.Rate ) as 金额
from FY_Chargy a,FY_Translate b,Code_Rate c
where (a.TranslateID=b.TranslateID)
and (c.RateID=a.RateID)
Union
select Sum( -d.amount*c.Rate ) as 金额
from FY_Translate b,Code_Rate c ,FY_Payment d
where (d.TranslateID=b.TranslateID)
and (c.RateID=d.RateID)
Union
select Sum( -e.amount*c.Rate) as 金额
from FY_Translate b,Code_Rate c ,FY_withdraw e
where (e.TranslateID=b.TranslateID)
and (c.RateID=e.RateID )
select sum(金额) from (select Sum(a.Factfee*c.Rate ) as 金额
from FY_Chargy a,FY_Translate b,Code_Rate c
where (a.TranslateID=b.TranslateID)
and (c.RateID=a.RateID)
Union
select Sum( -d.amount*c.Rate ) as 金额
from FY_Translate b,Code_Rate c ,FY_Payment d
where (d.TranslateID=b.TranslateID)
and (c.RateID=d.RateID)
Union
select Sum( -e.amount*c.Rate) as 金额
from FY_Translate b,Code_Rate c ,FY_withdraw e
where (e.TranslateID=b.TranslateID)
and (c.RateID=e.RateID )
from FY_Chargy a,FY_Translate b,Code_Rate c
where (a.TranslateID=b.TranslateID)
and (c.RateID=a.RateID) )
+
(select Sum( -d.amount*c.Rate ) as 金额
from FY_Translate b,Code_Rate c ,FY_Payment d
where (d.TranslateID=b.TranslateID)
and (c.RateID=d.RateID))
+
(select Sum( -e.amount*c.Rate) as 金额
from FY_Translate b,Code_Rate c ,FY_withdraw e
where (e.TranslateID=b.TranslateID)
and (c.RateID=e.RateID ) )
(
Select Sum(IsNull(A.Factfee,0))+Sum(-IsNull(B.Amount,0))+Sum(-IsNull(C.Amount,0)) As 金额,T.RateID
From TransLate T Left Outer Join FY_Translate A On T.TransLateID=A.TransLateID
Left Outer Join FY_Payment B On T.TransLateID=B.TransLateID
Left Outer Join FY_Withdraw C On T.TransLateID=C.TransLateID
Group By T.RateID
) D Inner Join Code_Rate E On D.RateID
表的字段:
1.FY_Translate 业务表
FY_translateID 业务编号.
2.Code_Rate 汇率表
RateID 汇率编号 Rate 汇率值
3.FY_Chargy 收费表
FY_TranslateID 业务编号,FactFee 收费金额,RateID 汇率编号
4.FY_PayMent 付费表
FY_TranslateID 业务编号,Amount 收费金额,RateID 汇率编号
5.FY_WithDraw 退费表
FY_TranslateID 业务编号,Amount 收费金额,RateID 汇率编号我要汇总3个费用表的费用总和,条件是:
1:汇率编号和Code_Rate 相对应
2:3个费用表中的业务编号只要存在于 FY_Translate 中就行(不是FY_Translate中的业务编号在3个费用表中都存在)