左右联接，它在子查询oracle上工作吗

Question

我有两个表，如下所示：

loy_credits_ledger

+-----------------+------------------+-----------------+-----------------+|CRL_OUTLET_CODE|CRL_REDEEM_MONTH|CRL_recredit_year|CRL_RM_REDEEMED|+-----------------+------------------+-----------------+-----------------+|144000581 | 2014年4月| 273.3||144000581 | 2014年7月| 194.1||144000581 | 2014年10月| 216.3||144000581 | 2015年1月| 24.9||144000581 | 2015年1月| 177.1|+-----------------+------------------+-----------------+-----------------+

jti_qtr_credit_data

+-----------------+----------+-------------+------------+------------+---------------------+|QCD_OUTLET_CODE | QCD_YEAR | QCD_QUARTER | QCD_CREDIT | QCD_POINTS | QCD_redits_BALANCE|+-----------------+----------+-------------+------------+------------+---------------------+|144000581|2014 |Q1|273.3|54660|0||144000581 | 2014 |第2季度| 194.1 | 38820 | 0||144000581 | 2014 |第3季度| 378.8 | 75760 | 0||144000581|2014 |第4季度|202 |40400|0||144000581 | 2015 |第1季度| 321.55 | 64310 |0|+-----------------+----------+-------------+------------+------------+---------------------+

我需要在某些条件下连接这两个表。除了混乱的表格结构(我无法控制)，我设法使我的结果几乎正确。基本上，这就是我的目标：

+-----------------+----------+-------------+------------+------------+--------------+---------------------+|QCD_OUTLET_CODE | QCD_YEAR | QCD_QUARTER | QCD_POINTS | QCD_CREDIT | QCD_resident | QCD_redits_BALANCE|+-----------------+----------+-------------+------------+------------+--------------+---------------------+|144000581 | 2014 |第2季度| 38820 | 194.1 | 194.1 |0||144000581|2014 |第4季度|40400|202|202|0||144000581|2014 |Q1|54660|273.3|273.3|0||144000581 | 2014 |第3季度| 75760 | 378.8 | 216.3 | 162.5|+-----------------+----------+-------------+------------+------------+--------------+---------------------+

它只是缺少第五条记录，该记录仅存在于jti_qtr_credit_data中。

我使用的脚本是：

SELECT Q.QCD_OUTLET_CODE,  Q.QCD_YEAR, Q.QCD_QUARTER, Q.QCD_POINTS, Q.QCD_CREDIT,   nvl(SUM(L.REDEEMED), 0) AS QCD_REDEEMED, Q.QCD_CREDIT - NVL(SUM(L.REDEEMED),0) AS QCD_CREDITS_BALANCE
FROM 
(SELECT 
(case when CRL_REDEEM_MONTH = 'JAN' Then 'Q4'
when CRL_REDEEM_MONTH = 'FEB' Then 'Q4'
when CRL_REDEEM_MONTH = 'MAR' Then 'Q4'
when CRL_REDEEM_MONTH = 'APR' Then 'Q1'
when CRL_REDEEM_MONTH = 'MAY' Then 'Q1'
when CRL_REDEEM_MONTH = 'JUN' Then 'Q1'
when CRL_REDEEM_MONTH = 'JUL' Then 'Q2'
when CRL_REDEEM_MONTH = 'AUG' Then 'Q2'
when CRL_REDEEM_MONTH = 'SEP' Then 'Q2'
when CRL_REDEEM_MONTH = 'OCT' Then 'Q3'
when CRL_REDEEM_MONTH = 'NOV' Then 'Q3'
when CRL_REDEEM_MONTH = 'DEC' Then 'Q3'
ELSE NULL END) QUARTER, 
(case 
when CRL_REDEEM_MONTH = 'JAN' Then crl_redeem_year-1
when CRL_REDEEM_MONTH = 'FEB' Then crl_redeem_year-1
when CRL_REDEEM_MONTH = 'MAR' Then crl_redeem_year-1
when CRL_REDEEM_MONTH = 'APR' Then crl_redeem_year
when CRL_REDEEM_MONTH = 'MAY' Then crl_redeem_year
when CRL_REDEEM_MONTH = 'JUN' Then crl_redeem_year
when CRL_REDEEM_MONTH = 'JUL' Then crl_redeem_year
when CRL_REDEEM_MONTH = 'AUG' Then crl_redeem_year
when CRL_REDEEM_MONTH = 'SEP' Then crl_redeem_year
when CRL_REDEEM_MONTH = 'OCT' Then crl_redeem_year
when CRL_REDEEM_MONTH = 'NOV' Then crl_redeem_year
when CRL_REDEEM_MONTH = 'DEC' Then crl_redeem_year
ELSE NULL END) redeem_year,
CRL_OUTLET_CODE,  CRL_CREDITS_EARNED, NVL(CRL_RM_REDEEMED, 0) REDEEMED
FROM loy_credits_ledger
where TO_CHAR(crl_redeem_year) is not null
AND CRL_OUTLET_CODE = 144000581
--GROUP BY CRL_REDEEM_MONTH, crl_redeem_year, CRL_OUTLET_CODE, CRL_CREDITS_EARNED
) L
full outer JOIN jti_qtr_credit_data Q 
ON L.CRL_OUTLET_CODE = Q.QCD_OUTLET_CODE
AND L.redeem_year = Q.QCD_YEAR
AND L.QUARTER =  Q.QCD_QUARTER
AND L.CRL_OUTLET_CODE = 144000581
GROUP BY Q.QCD_OUTLET_CODE,  Q.QCD_YEAR, Q.QCD_QUARTER, Q.QCD_YEAR, Q.QCD_CREDIT, Q.QCD_POINTS;

现在，问题是，我尝试了左和右加入。由于我有一个QCD_ Quarter＝Q1&QCD_Year=2015在jti_qtr_credit_data中，但不在loy_credits_ledge；不应该仍然显示带有左联接或右联接的null值的记录吗？

这个问题与带有联接的子查询有关吗？

Answer 1

通过在正常WHERE条件中使用(+)号来告诉DB包含所有具有NULL值的不匹配行；我设法得到了想要的结果。

以下是修改后的脚本：

SELECT Q.QCD_OUTLET_CODE,  Q.QCD_YEAR, Q.QCD_QUARTER, Q.QCD_POINTS, Q.QCD_CREDIT,   nvl(SUM(L.REDEEMED), 0) AS QCD_REDEEMED, Q.QCD_CREDIT - NVL(SUM(L.REDEEMED),0) AS QCD_CREDITS_BALANCE
FROM 
(SELECT 
(case when CRL_REDEEM_MONTH = 'JAN' Then 'Q4'
when CRL_REDEEM_MONTH = 'FEB' Then 'Q4'
when CRL_REDEEM_MONTH = 'MAR' Then 'Q4'
when CRL_REDEEM_MONTH = 'APR' Then 'Q1'
when CRL_REDEEM_MONTH = 'MAY' Then 'Q1'
when CRL_REDEEM_MONTH = 'JUN' Then 'Q1'
when CRL_REDEEM_MONTH = 'JUL' Then 'Q2'
when CRL_REDEEM_MONTH = 'AUG' Then 'Q2'
when CRL_REDEEM_MONTH = 'SEP' Then 'Q2'
when CRL_REDEEM_MONTH = 'OCT' Then 'Q3'
when CRL_REDEEM_MONTH = 'NOV' Then 'Q3'
when CRL_REDEEM_MONTH = 'DEC' Then 'Q3'
ELSE NULL END) QUARTER, 
(case 
when CRL_REDEEM_MONTH = 'JAN' Then crl_redeem_year-1
when CRL_REDEEM_MONTH = 'FEB' Then crl_redeem_year-1
when CRL_REDEEM_MONTH = 'MAR' Then crl_redeem_year-1
when CRL_REDEEM_MONTH = 'APR' Then crl_redeem_year
when CRL_REDEEM_MONTH = 'MAY' Then crl_redeem_year
when CRL_REDEEM_MONTH = 'JUN' Then crl_redeem_year
when CRL_REDEEM_MONTH = 'JUL' Then crl_redeem_year
when CRL_REDEEM_MONTH = 'AUG' Then crl_redeem_year
when CRL_REDEEM_MONTH = 'SEP' Then crl_redeem_year
when CRL_REDEEM_MONTH = 'OCT' Then crl_redeem_year
when CRL_REDEEM_MONTH = 'NOV' Then crl_redeem_year
when CRL_REDEEM_MONTH = 'DEC' Then crl_redeem_year
ELSE NULL END) redeem_year,
CRL_OUTLET_CODE,  CRL_CREDITS_EARNED, NVL(CRL_RM_REDEEMED, 0) REDEEMED
FROM loy_credits_ledger
where TO_CHAR(crl_redeem_year) is not null
AND CRL_OUTLET_CODE = 144000581
--GROUP BY CRL_REDEEM_MONTH, crl_redeem_year, CRL_OUTLET_CODE, CRL_CREDITS_EARNED
) L
,
jti_qtr_credit_data q
where L.CRL_OUTLET_CODE(+) = Q.QCD_OUTLET_CODE
AND L.redeem_year(+) = Q.QCD_YEAR
AND L.QUARTER(+) =  Q.QCD_QUARTER
and q.QCD_OUTLET_CODE = 144000581
GROUP BY Q.QCD_OUTLET_CODE,  Q.QCD_YEAR, Q.QCD_QUARTER, Q.QCD_YEAR, Q.QCD_CREDIT, Q.QCD_POINTS;

结果

+-----------------+----------+-------------+------------+------------+--------------+---------------------+|QCD_OUTLET_CODE | QCD_YEAR | QCD_QUARTER | QCD_POINTS | QCD_CREDIT | QCD_resident | QCD_redits_BALANCE|+-----------------+----------+-------------+------------+------------+--------------+---------------------+|144000581 | 2014 |第2季度| 38820 | 194.1 | 194.1 |0||144000581|2014 |第4季度|40400|202|202|0||144000581|2014 |Q1|54660|273.3|273.3|0||144000581 | 2014 |第3季度| 75760 | 378.8 | 216.3 | 162.5||144000581|2015|Q1|64310|321.55|0|321.55|+-----------------+----------+-------------+------------+------------+--------------+---------------------+

此方法受到本文和本文的启发。