从两个不同的表中添加金额Oracle [英] Add amounts from two different tables Oracle
问题描述
我有两个表,一个利息表和一个费用表.利息表具有借方利息和贷方利息,我可以使用以下查询获得差额:
I have two tables, an interest table and a charges table. The Interest Table Has debit Interest and credit Interest Which I have been able to get the difference using this query:
select e.sol_id, (sum(CASE WHEN e.INTEREST_IND = 'D' THEN e.AMOUNT_IN_LCY ELSE 0 END)-
sum(CASE WHEN e.INTEREST_IND = 'C' THEN e.AMOUNT_IN_LCY ELSE 0 END)) as Difference
from tbaadm.INTEREST_DETAILS e
group by e.sol_id;
我的输出是正确的:
我现在需要像这样尝试在费用"表中添加金额:
I now need to add the amount in the Charges table which I have attempted like this:
select e.sol_id, (sum(CASE WHEN e.INTEREST_IND = 'D' THEN e.AMOUNT_IN_LCY ELSE 0 END)-
sum(CASE WHEN e.INTEREST_IND = 'C' THEN e.AMOUNT_IN_LCY ELSE 0 END) +
sum(f.AMOUNT_IN_LCY)) as Difference
from tbaadm.INTEREST_DETAILS e,TBAADM.CHARGE_DETAILS f
where F.SOL_ID = E.SOL_ID
group by e.sol_id, f.sol_id
挂起片刻后的这个查询给我这个输出:
This Query after hanging for a moment gives me this Output:
这是错误的,考虑到虽然从第一张表中的贷方减去借方有分行000,001和003的条目,但是即使第二张表中没有条目,我如何才能从第二张表中添加金额并维护我的结果集000和003的分支?
Which is wrong Considering that while Subtracting Debits from the credit in the first tables has entries for Branch 000,001 and 003, How can I add the Amount from the second table and maintain my result set even if the second table does not have entries for branch 000 and 003?
推荐答案
我认为您需要外部联接.
I think you need an OUTER JOIN.
您基本上有两个这样的数据集:
You essentially have two Datasets like this:
Set1: ID VAL Set2: ID Val => Expected Result:
1 10 1 5 1 15
2 20 2 20
3 30 3 30
因此您需要一个正确的外部联接
So you need a RIGHT OUTER JOIN
对于您的SQL,它将是:
For your SQL it would be:
select e.sol_id
,(sum(CASE WHEN e.INTEREST_IND = 'D' THEN e.AMOUNT_IN_LCY ELSE 0 END)
- sum(CASE WHEN e.INTEREST_IND = 'C' THEN e.AMOUNT_IN_LCY ELSE 0 END)
+ sum(f.AMOUNT_IN_LCY)
) as Difference
from tbaadm.INTEREST_DETAILS e
,tbaadm.CHARGE_DETAILS f
where E.SOL_ID = F.SOL_ID (+)
group by e.sol_id, f.sol_id;
(+)表示表,也可以为NULL.另一种写法是:
The (+) denotes the Table, which can also be NULL. Another way to write it would be:
select e.sol_id
,(sum(CASE WHEN e.INTEREST_IND = 'D' THEN e.AMOUNT_IN_LCY ELSE 0 END)
- sum(CASE WHEN e.INTEREST_IND = 'C' THEN e.AMOUNT_IN_LCY ELSE 0 END)
+ sum(f.AMOUNT_IN_LCY)
) as Difference
from tbaadm.INTEREST_DETAILS e
LEFT OUTER JOIN
tbaadm.CHARGE_DETAILS f
ON E.SOL_ID = F.SOL_ID
group by e.sol_id, f.sol_id;
第二个版本符合SQL标准,而第一个(+)仅受Oracle支持.
The Second Version is conforming to the SQL Standard, whereas the first one (+) is only supported by Oracle.
这篇关于从两个不同的表中添加金额Oracle的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!