ORACLE(11.2.0.1.0)-具有日期表达式的递归CTE [英] ORACLE (11.2.0.1.0) - Recursive CTE with a date expression

问题描述

以下问题的正确答案:

• 如果我没有记错的话，那是在11.2.0.3或更高版本中修复的错误. (仍然不再支持11.2.0.1.11.2.0.4是唯一仍受支持的11.2版本)– @a_horse_with_no_name
• 错误号为11840579，已在11.2.0.3和12.1.0.1中修复. – @a_horse_with_no_name

• That is a bug that was fixed in 11.2.0.3 or later if I recall correctly. (11.2.0.1 is no longer supported anyway. 11.2.0.4 is the only 11.2 release that is still supported) – @a_horse_with_no_name
• The bug number is 11840579 and it was fixed in 11.2.0.3 and 12.1.0.1 – @a_horse_with_no_name

问题

我有一张桌子

CREATE TABLE test(
  from_date date,
  to_date date
);

INSERT INTO test(from_date,to_date)
--VALUES('20171101','20171115');
VALUES(TO_DATE('20171101','YYYYMMDD'),TO_DATE('20171115','YYYYMMDD'));

Oracle中的以下查询仅返回一行(预期为15行)

The following query in Oracle return only one row (expected 15 rows)

WITH dateCTE(from_date,to_date,d,i) AS(
  SELECT from_date,to_date,from_date AS d,1 AS i
  FROM test

  UNION ALL

  SELECT from_date,to_date,d+INTERVAL '1' DAY,i+1
  FROM dateCTE
  WHERE d<to_date
)
SELECT d,i
FROM dateCTE

SQL提琴- http://sqlfiddle.com/#!4/36907/8

为了进行测试，我将条件更改为i<10

For test I changed the condition to i<10

WITH dateCTE(from_date,to_date,d,i) AS(
  SELECT from_date,to_date,from_date AS d,1 AS i
  FROM test

  UNION ALL

  SELECT from_date,to_date,d+INTERVAL '1' DAY,i+1
  FROM dateCTE
  --WHERE d<to_date
  WHERE i<10 -- exit condition
)
SELECT d,i
FROM dateCTE

并获得下一个结果

| D          | I  |
|------------|----|
| 2017-11-01 |  1 |
| 2017-10-31 |  2 |
| 2017-10-30 |  3 |
| 2017-10-29 |  4 |
| 2017-10-28 |  5 |
| 2017-10-27 |  6 |
| 2017-10-26 |  7 |
| 2017-10-25 |  8 |
| 2017-10-24 |  9 |
| 2017-10-23 | 10 |

为什么此递归查询在Oracle中返回不良结果?

Why do this recursive query returned bad result in Oracle?

SQL小提琴- http://sqlfiddle.com/#!4/36907/5

我在SQLServer中运行了类似的查询，并且得到了正确的结果

I ran a similar query in SQLServer and I get the right result

WITH dateCTE(from_date,to_date,d,i) AS(
  SELECT from_date,to_date,from_date AS d,1 AS i
  FROM test

  UNION ALL

  SELECT from_date,to_date,DATEADD(DAY,1,d),i+1
  FROM dateCTE
  WHERE d<to_date
)
SELECT d,i
FROM dateCTE

正确的结果

d           i
2017-11-01  1
2017-11-02  2
2017-11-03  3
2017-11-04  4
2017-11-05  5
2017-11-06  6
2017-11-07  7
2017-11-08  8
2017-11-09  9
2017-11-10  10
2017-11-11  11
2017-11-12  12
2017-11-13  13
2017-11-14  14
2017-11-15  15

为什么它在Oracle中不起作用?您可以建议哪些替代方案?谢谢！

Why it doesn't work in Oracle? What alternative variants can you suggest? Thank you!

真实系统中的屏幕截图:

推荐答案

如果您要按顺序从起始日期到当前日期，请使用以下选择:

If you want to have a sequential from-date to to-date, Use such this select:

SELECT  DATE '2017-11-01' + LEVEL - 1 AS D, LEVEL AS I
FROM DUAL
CONNECT BY LEVEL <= DATE '2017-11-15' - DATE '2017-11-01' + 1;

这篇关于ORACLE(11.2.0.1.0)-具有日期表达式的递归CTE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！