从 Oracle 中的内连接右表返回一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14979037/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Returning one row from inner join right table in Oracle
提问by rojanu
I have three tables (TABLE_A, TABLE_B, TABLE_C) and I am joining them on C_MAIN_REF. TABLE_B has multiple entries and I need only one to be returned, doesn't matter which?
我有三个表（TABLE_A、TABLE_B、TABLE_C），我将在 C_MAIN_REF 上加入它们。TABLE_B 有多个条目，我只需要返回一个条目，哪个无关紧要？
Any Ideas?
有任何想法吗？
EDIT:
编辑：
SELECT C.C_UNIT_CODE as BU,'EPLC' as PRODUCT, A.BENE_NM as CUSTOMER, A.C_MAIN_REF,
A.C_TRX_REF, A.PRES_CCY, TO_CHAR(A.PRES_AMT) AS PRES_AMT, A.PRES_DT AS PRES_DATE,
A.DOC_STAT, '' AS APPL_RESPONSE, A.SETTLE_STATUS as SETTLE_STATUS
FROM EPLC_EM_NEGO A
INNER JOIN CPYT_SCHEDULE B
ON a.C_MAIN_REF = b.C_MAIN_REF AND ROWNUM =1
INNER JOIN EPLC_MASTER C
ON a.C_MAIN_REF = c.C_MAIN_REF
WHERE a.CLS_DRWG_FLG = 'NO' AND b.CPYT_UNPAID_FLAG = 'T'
Above is the current query I am using but the problem is that there are multiple entries in CPYT_SCHEDULE I don't want so the join shoul only include one row from CPYT_SCHEDULE table;
以上是我正在使用的当前查询，但问题是我不想要 CPYT_SCHEDULE 中有多个条目，因此连接应该只包含 CPYT_SCHEDULE 表中的一行；
Currently above returns
目前高于回报
BU PRODUCT CUSTOMER C_MAIN_REF C_TRX_REF PRES_CCY PRES_AMT PRES_DATE DOC_STAT SETTLE_STATUS
------------ ---------------------------------------------------- -------- --------- --------- ------------ -------------
GB01 EPLC Finance Customer No. 5 XL000053GB01 XL000053GB01-01 USD 90000 24-AUG-12 COMPLIANT Settle
GB01 EPLC Finance Customer No. 5 XL000053GB01 XL000053GB01-01 USD 90000 24-AUG-12 COMPLIANT Settle
GB01 EPLC Finance Customer No. 5 XL000053GB01 XL000053GB01-01 USD 90000 24-AUG-12 COMPLIANT Settle
GB01 EPLC Finance Customer No. 5 XL000053GB01 XL000053GB01-01 USD 90000 24-AUG-12 COMPLIANT Settle
but the desired output is
但所需的输出是
BU PRODUCT CUSTOMER C_MAIN_REF C_TRX_REF PRES_CCY PRES_AMT PRES_DATE DOC_STAT SETTLE_STATUS
------------ ---------------------------------------------------- -------- --------- --------- ------------ -------------
GB01 EPLC Finance Customer No. 5 XL000053GB01 XL000053GB01-01 USD 90000 24-AUG-12 COMPLIANT Settle
回答by a_horse_with_no_name
something like this:
像这样：
SELECT C.C_UNIT_CODE as BU,
'EPLC' as PRODUCT,
A.BENE_NM as CUSTOMER,
A.C_MAIN_REF,
A.C_TRX_REF,
A.PRES_CCY,
TO_CHAR(A.PRES_AMT) AS PRES_AMT,
A.PRES_DT AS PRES_DATE,
A.DOC_STAT,
'' AS APPL_RESPONSE,
A.SETTLE_STATUS as SETTLE_STATUS
FROM EPLC_EM_NEGO A
INNER JOIN (select cs.*,
row_number() over (partition by C_MAIN_REF) as rn
from CPYT_SCHEDULE cs) b
ON a.C_MAIN_REF = b.C_MAIN_REF AND b.rn = 1
INNER JOIN EPLC_MASTER C
ON a.C_MAIN_REF = c.C_MAIN_REF
WHERE a.CLS_DRWG_FLG = 'NO'
AND b.CPYT_UNPAID_FLAG = 'T'
回答by rojanu
The following sub-select worked for me, thanks for all the answers and input.
以下子选择对我有用，感谢所有的答案和输入。
SELECT C.C_UNIT_CODE as BU,'EPLC' as PRODUCT, A.BENE_NM as CUSTOMER, A.C_MAIN_REF, A.C_TRX_REF, A.PRES_CCY, TO_CHAR(A.PRES_AMT) AS PRES_AMT, A.PRES_DT AS PRES_DATE, A.DOC_STAT, '' AS APPL_RESPONSE, A.SETTLE_STATUS as SETTLE_STATUS
FROM E_EM_NEGO A, EPLC_MASTER C
WHERE a.CLS_DRWG_FLG = 'NO' AND a.C_MAIN_REF = c.C_MAIN_REF
AND a.C_MAIN_REF = (
SELECT C_MAIN_REF FROM CPYT_SCHEDULE B
WHERE B.CPYT_UNPAID_FLAG = 'T' AND b.C_MAIN_REF = a.C_MAIN_REF AND rownum=1)
