本文介绍了ORACLE(11.2.0.1.0)-具有日期表达式的递归CTE的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
以下问题的正确答案:
- 如果我没有记错的话,那是在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 dateCTESQL提琴- sqlfiddle/#!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小提琴- sqlfiddle/#!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)
发布评论