使用CTE Oracle生成一系列日期

Question

我想使用Oracle中的递归条款在两个不同的日期之间生成多个天的范围。

WITH CTE_Dates (cte_date) AS
  ( SELECT CAST(TO_DATE('10-02-2017', 'DD-MM-YYYY') AS DATE) cte_date FROM dual
  UNION ALL
  SELECT CAST( (cte_date + 1) AS DATE) cte_date
  FROM CTE_Dates
  WHERE TRUNC(cte_date) + 1 <= TO_DATE('20-02-2017', 'DD-MM-YYYY')
  )
SELECT * FROM CTE_Dates

返回的结果完全是预期的：

10-02-2017
09-02-2017
08-02-2017
07-02-2017
06-02-2017
... (unlimited)

预期结果：

10-02-2017
11-02-2017
...
19-02-2017
20-02-2017

Oracle数据库11G Express Edition版本11.2.0.2.0-64位生产。

编辑：如我所知，这是甲骨文中的一个已知错误，该错误通过Oracle 11.2.0.2存在，并将其固定在11.2.0.3。

中。

祭坛解决方案：

SELECT TRUNC (TO_DATE('10-02-2017', 'DD-MM-YYYY') + ROWNUM -1) dt
  FROM DUAL
 CONNECT BY ROWNUM  <= (TO_DATE('20-02-2017', 'DD-MM-YYYY') - (TO_DATE('10-02-2017', 'DD-MM-YYYY')))

Answer 1

这是递归CTE的oracle 11中的一个已知错误（特别是关于日期算术的）。固定在Oracle 12中。确切的行为：无论您是在代码中添加还是减去，引擎总是减去，它永远不会添加。

编辑：实际上，正如Alex Poole在对原始帖子的评论中指出的那样，该错误通过Oracle 11.2.0.2存在，并将其固定在11.2.0.3中。结束编辑

a，我不是一个付费客户，所以我不能引用章节和诗歌，但是有了一点谷歌搜索，您会找到与此链接的链接（包括在OTN上，我参与了一些讨论此和其他讨论的线程递归CTE中的错误 - 有些是修复的，有些仍然是Oracle 12.1中的错误。

添加 - 这是其中之一：https：//community.oracle.com/thread/3974408

Answer 2

，除非您确实需要使用WAND子句，否则还有另一个解决方案可以通过使用子句使用连接。

。

SELECT TO_DATE('10-02-2017', 'DD-MM-YYYY') AS date_range
  FROM dual
UNION ALL
SELECT TO_DATE('10-02-2017', 'DD-MM-YYYY') + LEVEL  
  FROM dual 
  CONNECT BY LEVEL <= (TO_DATE('20-02-2017', 'DD-MM-YYYY') - TO_DATE('10-02-2017', 'DD-MM-YYYY'));

Answer 3

也许递归的反向顺序解决了问题：

with cte_dates (cte_date) as ( 
    select cast(to_date('20-02-2017', 'DD-MM-YYYY') as date) cte_date from dual
    union all
    select  cast((cte_date - 1) as date)  cte_date
    from cte_dates
    where cast(cte_date as date) > to_date('10-02-2017', 'DD-MM-YYYY')
    )
select * from cte_dates 
order by cte_date 
;
2017-02-10
2017-02-11
2017-02-12
...
2017-02-18
2017-02-19
2017-02-20

注意：由于RCTE在11gr2中的另一个错误而导致的日期所需的日期

这里的旧讨论

Answer 4

您不需要递归条款，您只需要一个表行数>要生成的日期数：

WITH
 dates
 AS
 (SELECT 
   TO_DATE('10-02-2017', 'DD-MM-YYYY') + (rownum - 1)
  FROM
   all_tables
  WHERE 1=1 
  AND rownum < (TO_DATE('20-02-2017', 'DD-MM-YYYY') - TO_DATE('10-02-2017', 'DD-MM-YYYY')) + 2
 )
SELECT 
 *
FROM
 dates