oracle递归循环日期,ORACLE（11.2.0.1.0） - 带有日期表达式的递归CTE(ORACLE (11.2.0.1.0) - Recursive CTE with a date expr...

最新推荐文章于 2024-03-30 06:14:54 发布

桃花时尚

最新推荐文章于 2024-03-30 06:14:54 发布

阅读量346

点赞数

文章标签： oracle递归循环日期

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

题

我有一张桌子

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行)

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

)

SELECT d,i

FROM dateCTE

为了测试，我将条件改为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

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中返回了错误的结果？

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

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

)

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中不起作用？您可以建议哪些替代变体？谢谢！

来自真实系统的屏幕截图：

The right answer on the following question:

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

Question

I have a table

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'));

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

)

SELECT d,i

FROM dateCTE

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

WHERE i<10 -- exit condition

)

SELECT d,i

FROM dateCTE

And get the next result

| 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 |

Why do this recursive query returned bad result in Oracle?

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

)

SELECT d,i

FROM dateCTE

The right result

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

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

Screen shots from a real system:

原文：https://stackoverflow.com/questions/47526482

更新时间：2019-12-12 22:20

最满意答案

如果您想拥有从日期到最新的顺序，请使用以下选项：

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;

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;

2017-11-28

相关问答

这里你不需要一个“真正的”递归CTE。您可以使用一个月份参考： ;WITH Months

(

SELECT DATEADD(day, -DATEPART(day, GETDATE())+1, GETDATE()) as 'MonthStart'

UNION ALL

SELECT DATEADD(month, -1, MonthStart) as 'MonthStart'

FROM Months

)

然后你可以在上面的查询中JOIN SELECT TOP 1

...

在子查询vs 简单 (非递归)CTE版本中，它们可能非常相似。您将不得不使用分析器和实际执行计划来发现任何差异，这将与您的设置特定(因此我们无法全面告诉您答案)。一般来说 CTE可以递归使用; 一个子查询不行。这使得它们特别适合于树结构。 In the sub-query vs simple (non-recursive) CTE versions, they are probably very similar. You would have to use the profiler and

...

如果您想拥有从日期到最新的顺序，请使用以下选项： 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;

If you want to have a sequential from-date to to-date, Use such this select: SELECT DATE '2017-1

...

您是否需要输出每个组或者是否足以知道点值？如果是后者，你可以把它看作是“差距和岛屿”问题的变种。如果你想深入了解，这里有一篇很好的文章。我正在调整该页面中的一个代码段。将起点定义为在记录之前3天内没有记录的记录。终点是在3天之内没有记录的记录。一旦识别出每个岛屿，我们就可以获取起点和终点之间的天数，并通过将答案分开并舍入来确定其中有多少3天组合。注意：以下代码是针对组织1的硬编码。 CREATE TABLE #t(

OrganizationMrn int,

Visi

...

我已经做出了一些假设，你的描述没有像书面描述的那样完全有意义，但是下面的结果会达到你想要的结果： if object_id('tempdb.dbo.#leads', 'u') is not null

drop table #leads;

create table #leads (lead_id int, customer_id int, lead_created_date datetime, salesperson_name varchar(20))

insert into #leads

val

...

只需在开始时recursive ，即使递归递归到后来： with recursive cte1 as (

...

), cte2 as (

-- here comes the recursive cte

...

)

select *

from ...

Just put the recursive at the start, even if the recursive one comes later: with recursive cte1 as (

...

), cte2 as (

...

where B.Name in ('BCD')是将结果集过滤到一行的内容。将其更改为以下，您应该得到您想要的结果： ;with cte (Name, GUID, ParentGUID)

(

select distinct B.Name

,B.GUID

,B.ParentGUID

from EMP B

where B.Name in ('BCD')

union All

...

好的，我决定将xa-datasource-class放在standalone.xml中的上

oracle.jdbc.driver.OracleDriver

OK I resolved with putting xa-datasource-class on <

...

创建一个TVF： CREATE FUNCTION my_function (

@ProviderId int

)

RETURNS @ProviderTable TABLE

(

Id int NULL,

ProviderId int NULL,

ConsumerId int NULL

)

BEGIN

WITH cte AS (

SELECT Id,

ProviderId,

...

WITH子句用于SELECT语句。从文档： “您可以在任何顶级SELECT语句和大多数类型的子查询中指定此子句。” (强调我的)。如果您真的需要这样做，可以使用ORAFAQ来解决这个问题。主要的博客文章就在这里。 The WITH clause is for use with a SELECT statement. From the documents: "You can specify this clause in any top-level SELECT statement and in

...

桃花时尚

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
oracle递归循环日期,ORACLE（11.2.0.1.0） - 带有日期表达式的递归CTE(ORACLE (11.2.0.1.0) - Recursive CTE with a date expr...

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_...
复制链接

扫一扫

oracle递归循环日期,ORACLE（11.2.0.1.0） - 带有日期表达式的递归CTE(ORACLE (11.2.0.1.0) - Recursive CTE with a date expr...

“相关推荐”对你有帮助么？