sql - 我正在尝试使用左外连接进行子查询，但它给了我错误。有人可以看看吗？请

Question

在这个查询中，除了 5 个连接表之外，我试图使用第 6 个表“Days”将值与连接中的三个表进行比较。但它给了我错误，我不能在连接中使用子查询。

select
a.ID, a.Name, a.AMT, b.Address, c.Date, c.Pay, d.Check
from
Table1 a 
left outer join Table2 b        on a.ID = b.ID
left outer join Table3 c    on a.ID = c.ID and c.Date= (select Derived_date from  Days where TODAY_DATE = TO_DATE(SYSDATE, 'YYYY/MM/DD'))
left outer join Table4 d    on a.ID = d.ID and d.Date= (select Derived_date from Days where TODAY_DATE = TO_DATE(SYSDATE, 'YYYY/MM/DD'))
left outer join Table5 e    on a.ID = e.ID and e.Date= (select Derived_date from Days where TODAY_DATE = TO_DATE(SYSDATE, 'YYYY/MM/DD'))

Answer 1

试图在 ON 中使用子选择条款不会奏效。你需要JOIN像对待任何其他 table 一样回到它。由于您的子选择对于每个 JOIN 都是相同的，我会先把它放在(临时？)表中，这样你就可以 JOIN正常而不必SELECT 3 次相同的数据。

CREATE TABLE Derived_Dates AS SELECT Derived_date FROM Days WHERE TODAY_DATE = TO_DATE(SYSDATE, 'YYYY/MM/DD')

SELECT a.ID, a.Name, a.AMT, b.Address, c.Date, c.Pay, d.Check
FROM Table1 a LEFT OUTER JOIN Table2 b on a.ID = b.ID
    LEFT OUTER JOIN Table3 c ON a.ID = c.ID
    LEFT OUTER JOIN Table4 d ON a.ID = d.ID
    LEFT OUTER JOIN Table5 e ON a.ID = e.ID
    INNER JOIN Dervied_date dt ON c.Date = dt.Derived_date
        AND d.Date = dt.Derived_date
        AND e.Date = dt.Derived_date

以下是使用子选择的方法:

SELECT a.ID, a.Name, a.AMT, b.Address, c.Date, c.Pay, d.Check
FROM Table1 a LEFT OUTER JOIN Table2 b on a.ID = b.ID
    LEFT OUTER JOIN Table3 c ON a.ID = c.ID
    LEFT OUTER JOIN Table4 d ON a.ID = d.ID
    LEFT OUTER JOIN Table5 e ON a.ID = e.ID
    INNER JOIN (SELECT Derived_date FROM Days WHERE TODAY_DATE = TO_DATE(SYSDATE, 'YYYY/MM/DD')) dt ON c.Date = dt.Derived_date
        AND d.Date = dt.Derived_date
        AND e.Date = dt.Derived_date

而不是 JOIN回到您的派生日期，您也可以使用 WHERE条款。您有一些选择，并且您可能希望对您的特定实现进行一些更改，但这或多或少是我处理此问题的方式。