回答

收藏

ORACLE(11.2.0.1.0)-具有日期表达式的递归CTE

技术问答 技术问答 171 人阅读 | 0 人回复 | 2023-09-13

以下问题的正确答案:
: N2 f. @: t9 F  E* |1 X如果我没有记错的话,那是在11.2.0.3或更高版本中修复的错误。(无论如何不再支持11.2.0.1.11.2.0.4是唯一仍受支持的11.2版本)-@a_horse_with_no_name
  ]% }- A( R- d2 a' R4 p7 Q0 t: s: U错误号为11840579,已在11.2.0.3和12.1.0.1中进行了修复-@a_horse_with_no_name问题3 G' M/ V* O5 H: @/ s5 E
I have a table: |+ ]7 o$ S# o* [  O* C9 R6 ^
CREATE TABLE test() N3 T/ N; T+ g# s3 i0 }2 ^/ _& z
  from_date date,8 m% i( n4 K. C+ q; G& W
  to_date date
" `" j  O2 a! r# l8 w$ H);" B4 y+ J. p: f8 e
INSERT INTO test(from_date,to_date)
6 s0 U( T; m# k( W: W--VALUES('20171101','20171115');" H  |0 d4 @: ?* d
VALUES(TO_DATE('20171101','YYYYMMDD'),TO_DATE('20171115','YYYYMMDD'));( Y4 u8 H; ~# ~1 P
The following query in Oracle return only one row (expected 15 rows)
3 l5 A) \7 h9 v  e8 Z0 AWITH dateCTE(from_date,to_date,d,i) AS(; h- ]. E7 U9 q% b! R" l5 P. x
  SELECT from_date,to_date,from_date AS d,1 AS i
# C8 a7 [' a: t  l  FROM test
: O0 [3 P8 f( e5 A$ t6 L3 L, }  UNION ALL
2 X; t6 ]/ z! r5 M! d" y1 [! j  SELECT from_date,to_date,d+INTERVAL '1' DAY,i+12 F1 A- N2 D$ b6 B: x8 _9 x, _/ y
  FROM dateCTE1 |& L4 {% ~9 j/ b+ A
  WHERE dSQL Fiddle - http://sqlfiddle.com/#!4/36907/84 U% ~! O- ?# N9 s/ l
For test I changed the condition to i
! S" m( x( u4 [2 u$ U1 KWITH dateCTE(from_date,to_date,d,i) AS(8 p8 {' b/ T9 c7 {
  SELECT from_date,to_date,from_date AS d,1 AS i
2 |1 P8 Y; x; S9 E7 }  FROM test3 B0 t8 d( E, j1 u4 N  u/ o) _
  UNION ALL
3 d" R3 e2 ]: w) {' v# T  SELECT from_date,to_date,d+INTERVAL '1' DAY,i+1
- G6 x3 b! x- A8 [  FROM dateCTE
' `2 Q2 I" |# Q! b: v  l% K' k  --WHERE dAnd get the next result
- R! q8 ~# [2 T! p9 C, D  D$ x! d| D          | I  |" x% @  s' [  ~3 m9 Z* \: T
|------------|----|
( k, B2 h4 V; A) g/ j3 C| 2017-11-01 |  1 |" C/ d0 \! b0 @9 {9 ^
| 2017-10-31 |  2 |
* R( ?! ?% C& X| 2017-10-30 |  3 |
) ?: |8 [: a: O| 2017-10-29 |  4 |( A5 q# I7 P- v) G/ ]1 ~" D
| 2017-10-28 |  5 |7 H' o7 c( I" N5 n4 W
| 2017-10-27 |  6 |
4 c6 y7 C! O  m+ }| 2017-10-26 |  7 |
: e, [  Y) o; q* `| 2017-10-25 |  8 |
- b+ S0 z2 d- a4 l. o( E| 2017-10-24 |  9 |
" w5 R( j( b( K, q$ }8 W! U6 y| 2017-10-23 | 10 |8 y3 \" z4 U+ D3 o/ f7 y
Why do this recursive query returned bad result in Oracle?9 o+ T; m# |5 k* f! p7 N9 p6 \
SQL Fiddle - http://sqlfiddle.com/#!4/36907/58 p5 |" q8 I4 ?, p8 D' B$ v
为了进行测试,我将条件更改为 i. }$ H" K! p; f: G1 T5 P
WITH dateCTE(from_date,to_date,d,i) AS(
" O' m& j: F4 T& }" ?* l% y  SELECT from_date,to_date,from_date AS d,1 AS i9 {; a) g# u1 X2 P, J) n" P' @7 v
  FROM test
( Y9 e+ v& a6 y( ]+ n  UNION ALL8 H+ P" j3 z) \% ^: ]* M9 X
  SELECT from_date,to_date,DATEADD(DAY,1,d),i+1
' V: l7 I$ V/ h, z8 z7 C. y! C2 B- j0 i  FROM dateCTE
) t  K2 r/ b; z/ O! j  WHERE dThe right result
$ {0 X! a" E; S3 @  a+ Fd           i& T8 o. @% n$ z9 v) O
2017-11-01  1- a) z4 }7 ]/ g0 Y
2017-11-02  2
, g7 t3 Q1 H+ }; n7 i! m2017-11-03  30 v: r' v3 r" R- |# D0 J! }
2017-11-04  4
+ A4 m8 G# i* s7 q& J2017-11-05  5
$ `4 L. G" Z1 l% O/ e: g( a2017-11-06  6* K4 H+ K1 ^% p. K3 z9 w1 l- h
2017-11-07  7
- S# w. {; T8 k9 E& b) F5 J' }2017-11-08  8' u7 g. Q* M; @7 {  h
2017-11-09  9* D  v. h' G; c0 R
2017-11-10  10
# C8 n) Q1 @% k* g  A2017-11-11  11" |5 x" @8 J3 l
2017-11-12  12
4 a( p' e* s4 v8 k  r* X; i, w2017-11-13  13
1 a8 B* Z. C- d1 P9 c: M4 f2017-11-14  14
5 }6 y; k0 `/ k, P) N2017-11-15  153 s/ d! f# }$ v- _9 B
                ' S' U+ ^  n, O, B4 N5 \4 `
解决方案:
) a) x2 O0 s) J3 h9 v               
5 Z% q' V$ R& g% z- g; ~3 b! Y* [; S  B# F
8 k$ `) O9 E' v+ y% |% G/ O
                如果您希望有一个连续的起始日期到最新日期,请使用以下选择:
. B# t! x! S. t: ]& NSELECT  DATE '2017-11-01' + LEVEL - 1 AS D, LEVEL AS I+ s  k& h2 i, ?' v/ M; Q
FROM DUAL
/ G! W$ a3 ]; G6 S- i0 ?CONNECT BY LEVEL <= DATE '2017-11-15' - DATE '2017-11-01' + 1;
分享到:
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则