在连接条件中使用子查询进行SQL选择

问题描述：

虽然我不是高级SQL程序员，但我正在尝试编写SQL select，我需要将子查询放入连接条件以便从Actor表中获取数据。所以，我有选择：在连接条件中使用子查询进行SQL选择

SELECT JEI.account_number        AS JEI_accountNumber, 
    JE.branch_code           AS JE_branchCode, 
    JE.journal_entry_status        AS JEI_transStatus, 
    JEI.trans_amount          AS JEI_transAmount, 
    JEI.account_currency         AS JEI_accountCurrency, 
    JEI.other_account_number        AS JEI_otherAccountNumber, 
    JEI.other_bank_number         AS JEI_otherBankNumber, 
    nts_rep_cheque.getISOStringFromDate(JE.effective_date) AS JE_effectiveDate, 
    JE.multiple_trans_id         AS JE_multipleTransID, 
    A.user_logon_name          AS USER_ID, 
    A.di_teller_id           AS A_DITellerID, 
    nts_rep_cheque.getISOStringFromDate(JE.created_date) AS JE_createdDate, 
    JE.operation_id          AS JE_operationID, 
    JEI.nts_di_trans_number        AS JEI_transType 
FROM nts_journal_entry JE 
JOIN nts_journal_entry_item JEI 
ON JEI.company_code  = JE.company_code 
AND JEI.user_id   = JE.user_id 
AND JEI.journal_date  = JE.journal_date 
AND JEI.journal_entry_id = JE.journal_entry_id 
JOIN actor A 
ON A.ACCE_PROC_ID   = JE.COMPANY_CODE 
AND A.actor_id    = JE.actor_id 
WHERE JE.company_code  = '01'

但是我想用从演员表中选择不同的行此选择：

(SELECT USER_ID 
    FROM 
     (SELECT USER_ID 
     FROM NTS_JOURNAL_ENTRY 
     WHERE MULTIPLE_TRANS_ID = JE.MULTIPLE_TRANS_ID 
     ORDER BY journal_entry_id 
    ) 
    WHERE rownum =1 
    )

我把它放在前面的选择替换：

AND A.actor_id = JE.actor_id

到

AND A.actor_id = (SELECT USER_ID 
    FROM 
     (SELECT USER_ID 
     FROM NTS_JOURNAL_ENTRY 
     WHERE MULTIPLE_TRANS_ID = JE.MULTIPLE_TRANS_ID 
     ORDER BY journal_entry_id 
    ) 
    WHERE rownum =1 
    )

但是，它s eems，甲骨文有嵌套查询限制，所以我得到一个错误：ORA-00904: "JE"."MULTIPLE_TRANS_ID" invalid identifier.

有没有解决方案，如何解决该错误或任何方式如何重写查询以获得相同的结果？由于

答

您可以使用FIRST_VALUE如果你想为这个MULTIPLE_TRANS_ID

AND A.actor_id = 
     (SELECT DISTINCT FIRST_VALUE(USER_ID) OVER (PARTITION BY MULTIPLE_TRANS_ID ORDER BY journal_entry_id) 
     FROM NTS_JOURNAL_ENTRY 
     WHERE MULTIPLE_TRANS_ID = JE.MULTIPLE_TRANS_ID 
    )

第一用户如果想在最后一个用户使用DESC或LAST_VALUE

AND A.actor_id = 
     (SELECT DISTINCT FIRST_VALUE(USER_ID) OVER (PARTITION BY MULTIPLE_TRANS_ID ORDER BY journal_entry_id DESC) 
     FROM NTS_JOURNAL_ENTRY 
     WHERE MULTIPLE_TRANS_ID = JE.MULTIPLE_TRANS_ID 
    )

在连接条件中使用子查询进行SQL选择

相关推荐