Oracle DB SQL SELECT FROM a, b INNER JOIN c:是可能的吗?

Question

有一个SQL

SELECT dtl.ACCT_YM              AS ACCT_YM,
src.SRC_NAME             AS SRC_NAME,
ptnr.PTNR_NAME           AS PTNR_NAME,
info.PTNR_COMPANY_ID     AS PTNR_COMPANY_ID
FROM MPTNR_DTL dtl,
(SELECT SRC_ID ,SRC_NAME FROM SOURCE_VIEW WHERE DELETED = '0') src,
(SELECT PTNR_ID ,PTNR_NAME FROM PARTNER_MV WHERE DELETED = '0') ptnr
INNER JOIN PTNR_INFO info ON (info.DELETED = '0' AND info.ACCT_YM = dtl.ACCT_YM)
WHERE dtl.ACCT_YM = '202204'
AND dtl.DELETED = '0'
AND ROWNUM <= 10
ORDER BY dtl.SRC_ID;

我在SQLplus上尝试了它，但它是一个错误的SQL。错误是:dtl。不能识别ACCT_YM

基本句是

赛力特a, b, cFROM table_1， (sub select) table_2， (sub select) table_3INNER JOIN表3在XXXX

我该如何修复它?谢谢你！

Answer 1

在我看来，它应该是3个不同的表的连接:

SELECT dtl.acct_ym, src.src_name, ptnr.ptrn_name
FROM mptnr_dtl dtl
JOIN source_view src ON src.src_id = dtl.src_id
JOIN partner_mv ptnr ON ptnr.some_id = dtl.some_id      --> which ID?
JOIN ptnr_info info ON info.acct_ym = dtl.acct_ym
WHERE     src.deleted = '0'
AND ptnr.deleted = '0'
AND info.deleted = '0'
AND dtl.deleted = '0'
AND dtl.acct_ym = '202204'
AND ROWNUM <= 10
ORDER BY dtl.src_id

我不知道连接条件，所以我猜测列名(因为你没有发布表描述或它们之间的关系)。

Answer 2

如果你真的想混合使用传统的逗号和ANSI连接(请不要)，那么你需要在逗号连接之前使用ANSI连接:

SELECT dtl.ACCT_YM              AS ACCT_YM,
src.SRC_NAME             AS SRC_NAME,
ptnr.PTNR_NAME           AS PTNR_NAME,
info.PTNR_COMPANY_ID     AS PTNR_COMPANY_ID
FROM   MPTNR_DTL dtl
INNER JOIN PTNR_INFO info ON (info.DELETED = '0' AND info.ACCT_YM = dtl.ACCT_YM),
(SELECT SRC_ID ,SRC_NAME FROM SOURCE_VIEW WHERE DELETED = '0') src,
(SELECT PTNR_ID ,PTNR_NAME FROM PARTNER_MV WHERE DELETED = '0') ptnr
WHERE  dtl.ACCT_YM = '202204'
AND  dtl.DELETED = '0'
AND  ROWNUM <= 10
ORDER BY dtl.SRC_ID;

但是，这是不好的做法，最好始终坚持使用单一连接语法。

您还有一个额外的问题，即ROWNUM是在读取行时生成的，WHERE过滤器在ORDER BY子句之前应用，因此您将获得前10个随机行，然后对它们进行排序。

---

从Oracle 12开始，遗留的逗号连接和行限制过滤器可以转换为:

SELECT dtl.ACCT_YM              AS ACCT_YM,
src.SRC_NAME             AS SRC_NAME,
ptnr.PTNR_NAME           AS PTNR_NAME,
info.PTNR_COMPANY_ID     AS PTNR_COMPANY_ID
FROM   MPTNR_DTL dtl
CROSS JOIN (SELECT SRC_ID ,SRC_NAME FROM SOURCE_VIEW WHERE DELETED = '0') src
CROSS JOIN (SELECT PTNR_ID ,PTNR_NAME FROM PARTNER_MV WHERE DELETED = '0') ptnr
INNER JOIN PTNR_INFO info ON (info.DELETED = '0' AND info.ACCT_YM = dtl.ACCT_YM)
WHERE  dtl.ACCT_YM = '202204'
AND    dtl.DELETED = '0'
ORDER BY dtl.SRC_ID
FETCH FIRST 10 ROWS ONLY;

或者更简单:

SELECT dtl.ACCT_YM              AS ACCT_YM,
src.SRC_NAME             AS SRC_NAME,
ptnr.PTNR_NAME           AS PTNR_NAME,
info.PTNR_COMPANY_ID     AS PTNR_COMPANY_ID
FROM   MPTNR_DTL dtl
CROSS JOIN SOURCE_VIEW src
CROSS JOIN PARTNER_MV ptnr
INNER JOIN PTNR_INFO info ON (info.DELETED = '0' AND info.ACCT_YM = dtl.ACCT_YM)
WHERE  dtl.ACCT_YM = '202204'
AND    dtl.DELETED = '0'
AND    src.DELETED = '0'
AND    ptnr.DELETED = '0'
ORDER BY dtl.SRC_ID
FETCH FIRST 10 ROWS ONLY;

在Oracle 11和更早的版本中，您希望首先ORDER，然后通过ROWNUM过滤(而不是过滤前10个随机行，然后对这些随机行排序):

SELECT *
FROM   (
SELECT dtl.ACCT_YM              AS ACCT_YM,
src.SRC_NAME             AS SRC_NAME,
ptnr.PTNR_NAME           AS PTNR_NAME,
info.PTNR_COMPANY_ID     AS PTNR_COMPANY_ID
FROM   MPTNR_DTL dtl
CROSS JOIN (SELECT SRC_ID ,SRC_NAME FROM SOURCE_VIEW WHERE DELETED = '0') src
CROSS JOIN (SELECT PTNR_ID ,PTNR_NAME FROM PARTNER_MV WHERE DELETED = '0') ptnr
INNER JOIN PTNR_INFO info ON (info.DELETED = '0' AND info.ACCT_YM = dtl.ACCT_YM)
WHERE  dtl.ACCT_YM = '202204'
AND    dtl.DELETED = '0'
ORDER BY dtl.SRC_ID
)
WHERE  ROWNUM <= 10;

或:

SELECT *
FROM   (
SELECT dtl.ACCT_YM              AS ACCT_YM,
src.SRC_NAME             AS SRC_NAME,
ptnr.PTNR_NAME           AS PTNR_NAME,
info.PTNR_COMPANY_ID     AS PTNR_COMPANY_ID
FROM   MPTNR_DTL dtl
CROSS JOIN SOURCE_VIEW src
CROSS JOIN PARTNER_MV ptnr
INNER JOIN PTNR_INFO info ON (info.DELETED = '0' AND info.ACCT_YM = dtl.ACCT_YM)
WHERE  dtl.ACCT_YM = '202204'
AND    dtl.DELETED = '0'
AND    src.DELETED = '0'
AND    ptnr.DELETED = '0'
ORDER BY dtl.SRC_ID
)
WHERE  ROWNUM <= 10;

---

如果您愿意，您可以将CROSS JOIN更改为INNER JOIN，但随后您需要指定连接条件(这是您现有查询没有的东西)。