Oracle递归查询-日期 [英] Oracle Recursive Query - Dates

问题描述

我有两组日期传递到查询中，我想查找两组日期之间的所有月份/年份.

I've got two sets of dates being passed into a query and I would like to find all the months/years between both sets of dates.

当我尝试此操作时:

WITH CTE_Dates (cte_date)  AS (
    SELECT cast(date '2014-01-27' as date)  from dual
    UNION ALL
    SELECT cast(ADD_MONTHS(TRUNC(cte_date, 'MONTH'),1) as date)
    FROM CTE_Dates
    WHERE ( TO_DATE(ADD_MONTHS(TRUNC(cte_date, 'MONTH'), 1)) BETWEEN TO_DATE ('27-01-2014','DD-MM-YYYY') AND TO_DATE ('27-04-2014','DD-MM-YYYY'))
        OR
          ( TO_DATE(ADD_MONTHS(TRUNC(cte_date, 'MONTH'), 1)) BETWEEN TRUNC(TO_DATE('27-11-2014','DD-MM-YYYY'), 'MONTH')  AND TO_DATE ('27-01-2015','DD-MM-YYYY'))
      )
SELECT * from CTE_Dates

我得到:

27-JAN-14
01-FEB-14
01-MAR-14
01-APR-14

我也想得到:

01-NOV-14
01-DEC-14
01-JAN-15

似乎WHERE子句的OR部分被忽略了.

It looks like the OR portion of the WHERE clause gets ignored.

有关如何创建此查询的建议?

Suggestions on how to create this query?

谢谢 科里

推荐答案

您现在所拥有的问题(除了额外的cast()和to_date()调用之外)是，在第四次迭代中，两个条件均为假，因此递归停止；没有什么可以让它略微跳过然后再次拾取，否则它将永远持续下去.我认为您无法在递归中实现这两个范围.

The problem with what you have now (aside from extra cast() and to_date() calls) is that on the fourth iteration both the conditions are false so the recursion stops; there's nothing to make it skip a bit and pick up again, otherwise it would continue forever. I don't think you can achieve both ranges within the recursion.

您可以将所需的最新日期放入递归部分中，然后过滤之后需要的两个范围:

You can put the latest date you want inside the recursive part, and then filter the two ranges you want afterwards:

WITH CTE_Dates (cte_date)  AS (
    SELECT date '2014-01-27'  from dual
    UNION ALL
    SELECT ADD_MONTHS(TRUNC(cte_date, 'MONTH'), 1)
    FROM CTE_Dates
    WHERE ADD_MONTHS(TRUNC(cte_date, 'MONTH'), 1) <= date '2015-01-27'
)
SELECT * from CTE_Dates
WHERE cte_date BETWEEN date '2014-01-27' AND date '2014-04-27'
OR cte_date BETWEEN date '2014-11-27' AND date '2015-01-27';

CTE_DATE 
---------
27-JAN-14 
01-FEB-14 
01-MAR-14 
01-APR-14 
01-DEC-14 
01-JAN-15 

 6 rows selected 

您可以用成对的开始日期和结束日期替换硬编码的值.如果范围可能重叠或第二个范围可能在第一个范围之前(或结束)，则可以选择较高的日期:

You can replace the hard-coded values with your pairs of start and end dates. If the ranges might overlap or the second range could be (or end) before the first one, you could pick the higher date:

    WHERE ADD_MONTHS(TRUNC(cte_date, 'MONTH'), 1)
      <= greatest(date '2015-01-27', date '2014-04-27')

...尽管仅对变量有意义，而不对固定值有意义.

... though that only makes sense with variables, not fixed values.

这篇关于Oracle递归查询-日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！