使用CTE Oracle生成日期范围 [英] Generate range of dates using CTE Oracle

问题描述

我想使用Oracle中的递归WITH子句在两个不同日期之间生成一个天范围。

I want to generate a range of days between two different dates using recursive WITH clause in 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

返回的结果完全不同于预期：

The returned results are completely other than expected:

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 Database 11g Express Edition版本11.2.0.2.0-64位生产。

Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production.

编辑：
据我所知，这是一个知识Oracle中的错误，该错误通过Oracle 11.2.0.2存在，并且已在11.2.0.3。中修复。

As I understood, this is a known Bug in Oracle, the bug exists through Oracle 11.2.0.2 and it was fixed in 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')))

推荐答案

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

This was a known bug in recursive CTE's in Oracle 11 (specifically with regard to date arithmetic). Fixed in Oracle 12. Exactly that behavior: whether you add or subtract in your code, the engine always subtracts, it never adds.

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

EDIT: Actually, as Alex Poole pointed out in a Comment to the original post, the bug exists through Oracle 11.2.0.2 and it was fixed in 11.2.0.3. End edit

A，我不是付费客户，因此我无法引用章节和经文，但会使用谷歌搜索功能会找到与此相关的链接（包括在OTN上，我参与了一些讨论此问题的线程以及递归CTE中的其他错误-有些已修复，有些仍然是Oracle 12.1中的错误）。

Alas I am not a paying customer, so I can't quote chapter and verse, but with a little bit of Googling you will find links to this (including on OTN where I was involved in a few threads discussing this and other bugs in recursive CTEs - some were fixed, some are still bugs in Oracle 12.1).

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

Added - here is one of those discussions: https://community.oracle.com/thread/3974408

这篇关于使用CTE Oracle生成日期范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！