SQLPLUS(Oracle)-用于确定日期表达式是否大于n天的WHERE子查询

Question

我是SQL的新手，在理解子查询的使用以及将它们相对于外部查询放置在哪里时遇到了一些困难。下面的查询是我一直在处理的问题的一部分，似乎无法获得所需的结果。

我需要提取开始日期和结束日期之间的天数。然后检查它是否大于2，并将其应用于外部查询。此特定尝试返回"Missing Expression"，而其他迭代（2nd Query Bellow）则返回一个错误，说明内部查询返回多行。（修改为使用"ALL"关键字也没有产生正确的结果）

QUERY1

SELECT P.PETNAME,P.PETTYPE
FROM PETTREATMENT PT, EXAMINATION E, PET P, TREATMENT T
WHERE PT.EXAMNO = E.EXAMNO
AND E.PETNO = P.PETNO
AND PT.TREATNO = T.TREATNO 
AND (SELECT TO_DATE(PETTREATMENT.ENDDATE,'DD-MON-YYYY') - TO_DATE(PETTREATMENT.STARTDATE,'DD-MON-YYYY') AS TOTALDAYS FROM PETTREATMENT WHERE TOTALDAYS > 2)
AND T.COST >100
ORDER BY P.PETNAME;

查询2

SELECT P.PETNAME,P.PETTYPE
FROM PETTREATMENT PT, EXAMINATION E, PET P, TREATMENT T
WHERE PT.EXAMNO = E.EXAMNO
AND E.PETNO = P.PETNO
AND PT.TREATNO = T.TREATNO 
AND 2 < (SELECT TO_DATE(PETTREATMENT.ENDDATE,'DD-MON-YYYY') - TO_DATE(PETTREATMENT.STARTDATE,'DD-MON-YYYY') FROM PETTREATMENT)
AND T.COST >100
ORDER BY P.PETNAME;

Answer 1

避免使用旧式联接。因为enddate和startdate来自pettreatment表，并且您已经从中进行了选择，所以您可以在where子句中指定条件。无需选择。

SELECT P.PETNAME,P.PETTYPE
FROM PETTREATMENT PT 
JOIN EXAMINATION E ON PT.EXAMNO = E.EXAMNO
JOIN PET P ON E.PETNO = P.PETNO
JOIN TREATMENT T ON PT.TREATNO = T.TREATNO 
WHERE TO_DATE(PT.ENDDATE,'DD-MON-YYYY') - TO_DATE(PT.STARTDATE,'DD-MON-YYYY') > 2
AND T.COST > 100
ORDER BY P.PETNAME;