因为我需要使用的数据范围BETWEEN，因为据我所知，下面的这两个查询应该是相同的：
select * from table1 where my_date1 - my_date2 between (-1) and (-30);
和
select * from table1 where my_date1 - my_date2 <= (-1) and my_date1 - my_date2 >= (-30);
但是，当我在脚本中尝试时：
SELECT
a.account_no AS ACCOUNT_NO,
a.installment_no AS INSTALLMENT_NO,
a.INSTALLMENT_DATE AS INSTALLMENT_DATE
FROM myTable a
INNER JOIN (SELECT
ACCOUNT_NO,
MIN(INSTALLMENT_NO) AS INSTALLMENT_NO
FROM myTable
WHERE
ACCOUNT_NO IS NOT NULL
AND INSTALLMENT_NO IS NOT NULL
AND STATUS = 'A'
GROUP BY ACCOUNT_NO) b
ON A.ACCOUNT_NO = B.ACCOUNT_NO AND A.INSTALLMENT_NO = B.INSTALLMENT_NO
WHERE (TRUNC(INSTALLMENT_DATE) - TRUNC(TO_DATE('12/01/2011','DD/MM/YYYY'))) BETWEEN (-1) AND (-30) -- If I change this
我有 0 行，但是当我改变时
WHERE (TRUNC(INSTALLMENT_DATE) - TRUNC(TO_DATE('12/01/2011','DD/MM/YYYY'))) BETWEEN (-1) AND (-30)
至
WHERE (TRUNC(INSTALLMENT_DATE) - TRUNC(TO_DATE('12/01/2011','DD/MM/YYYY'))) <= (-1) and (TRUNC(INSTALLMENT_DATE) - TRUNC(TO_DATE('12/01/2011','DD/MM/YYYY'))) >= (-30)
我得到超过 0 行。我想使用BETWEEN它，因为它更具可读性。我错过了什么吗？
我相信所用范围的语法BETWEEN是：
WHERE col BETWEEN <smaller_value> AND <larger_value>
这相当于
WHERE col >= <smaller_value> AND col <= <larger_value>
您当前的WHERE条款正在寻找日期的日期差异较大的比-1和少比-30。这将消除您试图定位的所有记录，实际上永远不会是真的。要解决此问题，请更正范围：
WHERE (TRUNC(INSTALLMENT_DATE) - TRUNC(TO_DATE('12/01/2011','DD/MM/YYYY')))
BETWEEN (-30) AND (-1)
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句