Oracle 11GR2的遞歸WITH子查詢方法
下面給大家詳細介紹Oracle 11GR2的遞歸WITH子查詢方法，具體內容如下所示：
SQL> with emp_data(ename,empno,mgr,l)
as
(select ename, empno, mgr, 1 lvl from emp where mgr is null
union all
select emp.ename, emp.empno, emp.mgr, ed.l+1
from emp, emp_data ed
where emp.mgr = ed.empno
)
SEARCH DEPTH FIRST BY ename SET order_by
select l,
lpad('*' ,2*l, '*')||ename nm
from emp_data
order by order_by
/
L NM
---- ---------------
1 **KING
2 ****BLAKE
3 ******ALLEN
3 ******JAMES
3 ******MARTIN
3 ******TURNER
3 ******WARD
2 ****CLARK
3 ******MILLER
2 ****JONES
3 ******FORD
4 ********SMITH
3 ******SCOTT
4 ********ADAMS
14 rows selected.
不知道真用起來怎么樣，按我的想象可以比原來的SYS_CONNECT_BY_PATH多玩出很多新花樣，比如按路徑累加，更靈活的剪枝條件，
WITH子查詢也稱為CTE (Common Table Expression)，是ANSI SQL-99標準的一部分。ORACLE從9i開始引入WITH子查詢，把它被稱作SUBQUERY FACTORING(分解子查詢)。
WITH子查詢的作用類似于內聯視圖(INLINE VIEW)。內聯視圖的定義寫作SQL的FROM 后面，只能夠引用一次；而WITH子查詢需要在引用之前先定義，一旦定義了在整個查詢的后續部分就可以按名稱來反復引用，從這點來看又很像臨時表。
從版本11GR2開始，ORACLE支持遞歸的WITH, 即允許在WITH子查詢的定義中對自身引用。這不是什么新鮮事，其他數據庫如DB2, Firebird, Microsoft SQL Server, PostgreSQL 都先于ORACLE支持這一特性。但對于ORACLE用戶來說，這一遞歸特性還是很令人期待的，利用它可以輕易實現以往做不到的、或者很難做到的許多新功能。這一章我們就來探索這一令人興奮的新特性，并把它和以往的實現手段（主要是CONNECT BY層次查詢）作比較。
我們先來看看這個遞歸WITH子查詢的語法：
WITH
① query_name ([c_alias [, c_alias]...])
② AS (subquery)
③ [search_clause]
④ [cycle_clause]
⑤ [,query_name ([c_alias [, c_alias]...]) AS (subquery) [search_clause] [cycle_clause]]...
①這是子查詢的名稱，和以往不同的是，必須在括號中把這個子查詢的所有列名寫出來。
②AS后面的subquery就是查詢語句，遞歸部分就寫在這里。
③遍歷順序子句，可以指定深度優先或廣度優先遍歷順序。
④循環子句，用于中止遍歷中出現的死循環。
⑤如果還有其他遞歸子查詢，定義同上。
subquery部分由兩個成員組成：anchor member(錨點成員) 和 recursive member(遞歸成員)。它們之間必須用union all聯合起來，anchor member 必須寫在recursive member前面。
anchor member用來定位遞歸的入口，錨點成員是一個SELECT語句，它不可以包含自身名稱(query_name)。這相當于CONNECT BY查詢中的START WITH，典型寫法就是:
SELECT ... FROM 要遍歷的表 WHERE ... (起始條件)
遞歸成員也是一個SELECT語句，用于定義上下級的關系，它必須包含自身名稱(即query_name)，而且僅僅只能引用一次。遞歸正是體現在對于自身的引用。典型的做法就是把query_name和其他表(一般來說就是你要遍歷的表)做一個連接，連接條件表明了上下級的關系。必須注意，在這個query_name中，并不是截止目前為止的所有數據都是可見的，可見的只是上次遞歸新加入的最近的一層數據。對query_name列的引用相當于CONNECT BY中的PRIOR操作符。當找不到滿足條件的下級，遍歷就會停止；如果你還有其他的遞歸出口條件，也可以一起寫在WHERE中，當WHERE不滿足時，遍歷就會停止，這就是在遍歷樹、圖時候的剪枝操作。越早停止則效率越高。
這個遞歸成員就是程序員發揮創造力的地方，以往在CONNECT BY中做不到的事情，比如沿路徑求和、求積等運算，現在都輕而易舉。而SYS_CONNECT_BY_PATH也很容易用字符串拼接'||'來實現。
搜索子句(search_clause)和循環子句(cycle_clause)我們后面的例子中會見到。
下面我們就來看看遞歸WITH子查詢的用法實例。
例1:
先來一個簡單例子，從scott/tiger的emp表來查找上下級關系：
傳統的CONNECT BY寫法：
SELECT empno ,ename ,job ,mgr ,deptno ,level ,SYS_CONNECT_BY_PATH(ename,'\') AS path ,CONNECT_BY_ROOT(ename) AS top_manager FROM EMP START WITH mgr IS NULL -- mgr列為空，表示沒有上級，該員工已經是最高級別。這是層次查詢的起點 CONNECT BY PRIOR empno= mgr;
新的遞歸WITH寫法:
WITH T(empno, ename, job, mgr, deptno, the_level, path,top_manager) AS ( ---- 必須把結構寫出來 SELECT empno, ename, job, mgr, deptno ---- 先寫錨點查詢，用START WITH的條件 ,1 AS the_level ---- 遞歸起點，第一層 ,'\'||ename ---- 路徑的第一截 ,ename AS top_manager ---- 原來的CONNECT_BY_ROOT FROM EMP WHERE mgr IS NULL ---- 原來的START WITH條件 UNION ALL ---- 下面是遞歸部分 SELECT e.empno, e.ename, e.job, e.mgr, e.deptno ---- 要加入的新一層數據，來自要遍歷的emp表 ,1 + t.the_level ---- 遞歸層次，在原來的基礎上加1。這相當于CONNECT BY查詢中的LEVEL偽列 ,t.path||'\'||e.ename ---- 把新的一截路徑拼上去 ,t.top_manager ---- 直接繼承原來的數據，因為每個路徑的根節點只有一個 FROM t, emp e ---- 典型寫法，把子查詢本身和要遍歷的表作一個連接 WHERE t.empno = e.mgr ---- 原來的CONNECT BY條件 ) ---- WITH定義結束 SELECT * FROM T ;
查詢結果：
EMPNO ENAME JOB MGR DEPTNO THE_LEVEL PATH TOP_MANAGE ------ ---------- --------- ------ ------- ---------- -------------------------- ---------- 7839 KING PRESIDENT 10 1 \KING KING 7566 JONES MANAGER 7839 20 2 \KING\JONES KING 7698 BLAKE MANAGER 7839 30 2 \KING\BLAKE KING 7782 CLARK MANAGER 7839 10 2 \KING\CLARK KING 7499 ALLEN SALESMAN 7698 30 3 \KING\BLAKE\ALLEN KING 7521 WARD SALESMAN 7698 30 3 \KING\BLAKE\WARD KING 7654 MARTIN SALESMAN 7698 30 3 \KING\BLAKE\MARTIN KING 7788 SCOTT ANALYST 7566 20 3 \KING\JONES\SCOTT KING 7844 TURNER SALESMAN 7698 30 3 \KING\BLAKE\TURNER KING 7900 JAMES CLERK 7698 30 3 \KING\BLAKE\JAMES KING 7902 FORD ANALYST 7566 20 3 \KING\JONES\FORD KING 7934 MILLER CLERK 7782 10 3 \KING\CLARK\MILLER KING 7369 SMITH CLERK 7902 20 4 \KING\JONES\FORD\SMITH KING 7876 ADAMS CLERK 7788 20 4 \KING\JONES\SCOTT\ADAMS KING
14 rows selected.
從結果集的THE_LEVEL和PATH列可以清楚地看到數據是如何被一層一層疊加上去的。
例2:
構造等差數列：
CONNECT BY寫法：
這是一個非常特殊的用法，因為沒有上下級關系，只有遍歷的終止條件。像這類CONNECT BY我強烈推薦在只有一行的結果集上運行（比如FROM DUAL, 比如從一個聚合后的子查詢），在多行的集合上運行比較難以控制，頭腦必須很清醒。
（以下ROWNUM全部可以改成 LEVEL，效果一樣）：
SELECT ROWNUM n ,ROWNUM*2 n2 ,DATE '2010-1-1'+ROWNUM-1 dt ,ADD_MONTHS(DATE '2010-1-1', ROWNUM-1) mon FROM DUAL CONNECT BY ROWNUM<=10;
結果：
N N2 DT MON
---------- ---------- ----------- -----------
1 2 2010-01-01 2010-01-01
2 4 2010-01-02 2010-02-01
3 6 2010-01-03 2010-03-01
4 8 2010-01-04 2010-04-01
5 10 2010-01-05 2010-05-01
6 12 2010-01-06 2010-06-01
7 14 2010-01-07 2010-07-01
8 16 2010-01-08 2010-08-01
9 18 2010-01-09 2010-09-01
10 20 2010-01-10 2010-10-01
10 rows selected.
這個簡潔優雅的寫法最早由Mikito Harakiri(從名字看是個日本人)在asktom網站(http://asktom.oracle.com)發表，現在已經風靡全世界的ORACLE社區。在這個方法被發現之前，一般采用的是從一個大的集合（表或視圖）中獲取ROWNUM的方法：
SELECT ROWNUM n, ROWNUM*2 n2, DATE '2010-1-1'+ROWNUM-1 dt, ADD_MONTHS(DATE '2010-1-1', ROWNUM-1) mon FROM ALL_OBJECTS ---- ALL_OBJECTS是個很大的系統視圖，它包含的行數足夠滿足一般的序列構造 WHERE ROWNUM<=10;
下面嘗試用遞歸WITH的寫法：
WITH t(n,n2,dt,mon) AS (
SELECT 1, 2,TO_DATE('2010-1-1','YYYY-MM-DD'),TO_DATE('2010-1-1','YYYY-MM-DD') FROM DUAL --- 先構造第一個
UNION ALL
SELECT t.n+1 ---- 遞增1
,t.n2+2 ---- 遞增2
,dt+1 ---- 下一日
,ADD_MONTHS(mon,1) ---- 下個月
FROM t ---- 沒有任何連接，因為不需要，所有數據都可以從錨點成員中衍生出來
WHERE t.n<10
)
SELECT * FROM T;
一切都按規矩來，竟然還是出錯了：
,ADD_MONTHS(mon,1) ---- 下個月 * ERROR at line 6: ORA-01790: expression must have same datatype as corresponding expression
改為字符串型看看：
WITH t(n,n2,dt,mon) AS ( SELECT 1, 2,'2010-01-01','2010-01-01' FROM DUAL ---- 用字符串來表示日期 UNION ALL SELECT t.n+1 ---- 遞增1 ,t.n2+2 ---- 遞增2 ,TO_CHAR(TO_DATE(t.dt,'YYYY-MM-DD')+1,'YYYY-MM-DD') ---- 先轉換為日期型，計算后換回字符串型 ,TO_CHAR(ADD_MONTHS(TO_DATE(t.mon,'YYYY-MM-DD'),1),'YYYY-MM-DD') ---- 計算下個月，方法同上 FROM t WHERE t.n<10 ) SELECT * FROM T;
我很驚奇地看到這個結果：
N N2 DT MON
---------- ---------- ---------- ----------
1 2 2010-01-01 2010-01-01
2 4 2009-12-31 2010-02-01 ----- DT竟然是遞減的！
3 6 2009-12-30 2010-03-01
4 8 2009-12-29 2010-04-01
5 10 2009-12-28 2010-05-01
6 12 2009-12-27 2010-06-01
7 14 2009-12-26 2010-07-01
8 16 2009-12-25 2010-08-01
9 18 2009-12-24 2010-09-01
10 20 2009-12-23 2010-10-01
10 rows selected.
這是ORACEL 11.2.0.1.0版本的BUG，后續版本應該會改正。
沒辦法，只好想其他招數繞過去:
WITH t(n) AS ( SELECT 1 FROM DUAL --- 先構造第一個 UNION ALL SELECT t.n+1 ---- 僅僅是整數序列 FROM t WHERE t.n<10 ) SELECT n ,n*2 n2 ,DATE '2010-1-1'+n-1 dt ---- 在最終的查詢中進行日期運算 ,ADD_MONTHS(DATE '2010-1-1', n-1) mon FROM T;
這下子對了：
N N2 DT MON
---------- ---------- ----------- -----------
1 2 2010-01-01 2010-01-01
2 4 2010-01-02 2010-02-01
3 6 2010-01-03 2010-03-01
4 8 2010-01-04 2010-04-01
5 10 2010-01-05 2010-05-01
6 12 2010-01-06 2010-06-01
7 14 2010-01-07 2010-07-01
8 16 2010-01-08 2010-08-01
9 18 2010-01-09 2010-09-01
10 20 2010-01-10 2010-10-01
10 rows selected.
看來對日期的運算有BUG。解決辦法就是先構造整數序列，然后在最終的查詢中再利用這個整數序列來構造日期序列。
從一個單行結果集CONNECT BY的例子:
SELECT ROWNUM rn,cnt FROM (SELECT COUNT(*) cnt FROM emp) ---- 經過聚合的只有一行的結果集 CONNECT BY ROWNUM<=cnt;
結果：
RN CNT
---------- ----------
1 14
2 14
3 14
4 14
5 14
6 14
7 14
8 14
9 14
10 14
11 14
12 14
13 14
14 14
14 rows selected.
遞歸WITH寫法：
WITH t(n,cnt) AS ( SELECT 1,COUNT(*) cnt FROM EMP --- 先構造第一個 UNION ALL SELECT t.n+1 ---- 遞增1 ,t.cnt ---- 這個cnt列不做任何修改，從第一層得來 FROM t ---- 沒有任何連接，因為不需要 WHERE t.n<t.cnt ---- 在這里看到cnt的作用，就是用于終止遍歷 ) SELECT * FROM t;
結果同上（略）。
例3:
獨立事件的排列組合：一個布袋中裝有數量相同的四種顏色的小球。隨機從布袋中取四次，每次取完都放回去?，F在問四次結果總顏色數等于3的概率是多少？
傳統的CONNECT BY寫法：
WITH t AS ( SELECT ROWNUM rn -- 先構造一個1,2,3,4的結果集，每個rn表示一種顏色 FROM DUAL CONNECT BY ROWNUM<=4 ) ,t2 AS ( ---- 集合t2模擬獨立取四次的動作，最終結果會有4*4*4*4=256行 SELECT ROWNUM id ---- 構造唯一ID供下面拆分用 ,REPLACE(SYS_CONNECT_BY_PATH(rn,'@'),'@') path ---- 用一個特殊字符@來作分隔符, 并在最后用REPLACE把它去除 ,COUNT(*) OVER() cnt ---- 利用分析函數算出總行數并把它作為一個列返回 FROM t ---- 這個是有四行的集合 WHERE LEVEL=4 ---- 我們需要的僅僅是最后一層的結果。在PATH里面已經包含了取四次的所有結果組合 CONNECT BY LEVEL<=4 ---- 沒有任何條件，前后都是獨立的 ) ,t3 AS ( ---- 集合t3把t2中的PATH包含的顏色組合拆開為四行 SELECT id,cnt,SUBSTR(PATH,rn,1) color FROM t2,t ---- 笛卡兒積，用于把t2中的一行變為四行 ) SELECT COUNT(COUNT(*))/MAX(cnt) AS prob FROM t3 GROUP BY id,cnt HAVING COUNT(DISTINCT color)=3 --- 每一個id中包含三種顏色 ;
結果：
PROB
----------
.5625
這個例子展示了CONNECT BY來模擬排列組合的技巧。每一層遍歷表示一次抽取的動作，因為每次都是完全獨立的，在CONNECT BY 里面僅僅限制了抽取次數（遍歷層數）而沒有其他條件。SYS_CONNECT_BY_PATH可以把截至當前為止所訪問到的各層次的數據串起來，在LEVEL=N就包含了前N層的排列組合情況。你可以用這個查詢來看看中間生成的結果集t2：
WITH t AS ( SELECT ROWNUM rn -- 先構造一個1,2,3,4的結果集，每個rn表示一種顏色 FROM DUAL CONNECT BY ROWNUM<=4 ) ,t2 AS ( ---- 集合t2模擬獨立取四次的動作，最終結果會有4*4*4*4=256行 SELECT ROWNUM id ---- 構造唯一ID供下面拆分用 ,REPLACE(SYS_CONNECT_BY_PATH(rn,'@'),'@') path ---- 用一個特殊字符@來作分隔符, 并在最后用REPLACE把它去除 ,COUNT(*) OVER() cnt ---- 利用分析函數算出總行數并把它作為一個列返回 FROM t ---- 這個是有四行的集合 WHERE LEVEL=4 ---- 我們需要的僅僅是最后一層的結果。在PATH里面已經包含了取四次的所有結果組合 CONNECT BY LEVEL<=4 ---- 沒有任何條件，前后都是獨立的 ) SELECT * FROM t2;
ID PATH CNT
---------- ---------- ----------
1 1111 256
2 1112 256
3 1113 256
4 1114 256
5 1121 256
6 1122 256
7 1123 256
8 1124 256
9 1131 256
10 1132 256
11 1133 256
......(其余結果略)
256 rows selected.
由此看到PATH列已經包含了四次抽取的所有可能結果，每個結果都被賦予一個唯一的編號ID。
如果你好奇的話可以看看下一步的結果集t3:
WITH t AS ( SELECT ROWNUM rn -- 先構造一個1,2,3,4的結果集，每個rn表示一種顏色 FROM DUAL CONNECT BY ROWNUM<=4 ) ,t2 AS ( ---- 集合t2模擬獨立取四次的動作，最終結果會有4*4*4*4=256行 SELECT ROWNUM id ---- 構造唯一ID供下面拆分用 ,REPLACE(SYS_CONNECT_BY_PATH(rn,'@'),'@') path ---- 用一個特殊字符@來作分隔符, 并在最后用REPLACE把它去除 ,COUNT(*) OVER() cnt ---- 利用分析函數算出總行數并把它作為一個列返回 FROM t ---- 這個是有四行的集合 WHERE LEVEL=4 ---- 我們需要的僅僅是最后一層的結果。在PATH里面已經包含了取四次的所有結果組合 CONNECT BY LEVEL<=4 ---- 沒有任何條件，前后都是獨立的 ) ,t3 AS ( ---- 集合t3把t2中的PATH包含的顏色組合拆開為四行 SELECT id,cnt,SUBSTR(PATH,rn,1) color FROM t2,t ---- 笛卡兒積，用于把t2中的一行變為四行 ) SELECT * FROM t3;
ID CNT COLO
---------- ---------- ----
1 256 1
1 256 1
1 256 1
1 256 1
2 256 1
2 256 1
2 256 1
2 256 2
3 256 1
3 256 1
3 256 1
3 256 3
4 256 1
4 256 1
4 256 1
4 256 4
......(其余結果略)
1024 rows selected.
可以看到t2集合中的每一行都被拆成了四行，這是為了后面的聚合運算。
最后看看算概率的主查詢：
SELECT COUNT(COUNT(*))/MAX(cnt) AS prob FROM t3 GROUP BY id,cnt HAVING COUNT(DISTINCT color)=3;
COUNT(DISTINCT color)可以算出每個ID中包含不重復的顏色數目，放在HAVING中過濾了數目不為3的那些ID。
GROUP BY id,cnt 表示按照id來分組。因為所有行的cnt都是一樣的(都等于256)，我們在分組加入它并不會改變分組的結果，加入cnt的目的是為了在查詢中引用。
最后的連續兩層COUNT函數的意思是要把分組結果再聚合為一行，算出滿足條件的id的行數。除以cnt就得到了我們要的概率。
本例是一個在多行的結果集上進行無條件遍歷的例子，前面說過了要特別小心，因為沒有上下級關系，隨著層數遞增，數據量的增長十分可觀。
遞歸WITH寫法：
WITH T AS ( SELECT ROWNUM rn -- 還是先構造一個1,2,3,4的結果集 FROM DUAL CONNECT BY ROWNUM<=4 ) ,t2(distinct_colors,lvl) AS ( --- 兩個列：所有不重復顏色，層次 SELECT '\'||rn,1 ---- 第一層就是最基礎的四種顏色的表 FROM t UNION ALL SELECT CASE WHEN INSTR(t2.distinct_colors||'\','\'||t.rn||'\')=0 --- 這個顏色沒有出現過 THEN t2.distinct_colors||'\'||t.rn --- 拼上去 ELSE t2.distinct_colors ---- 顏色已經出現，保持原來的 END ,t2.lvl+1 --- 層數遞增 FROM t, t2 WHERE t2.lvl<4 --- 遞歸出口的條件：次數達到限制 ) SELECT COUNT(CASE WHEN LENGTH(distinct_colors) - LENGTH(REPLACE(distinct_colors,'\'))=3 THEN 1 END) --- 出現三個斜杠 /COUNT(*) FROM t2 WHERE lvl=4 ---- 同CONNECT BY類似，我們只需觀察最后一層的數據，在這里面已經包含了所有層次的顏色 ;
在遞歸WITH子查詢t2中，我們看到它用了一個CASE表達式把以前沒出現過的顏色拼接到distinct_colors中。這個CASE是遞歸WITH的妙處，用SYS_CONNECT_BY_PATH沒辦法做到有條件的拼接。
而最后在計算顏色數的時候用了一個技巧，把顏色數轉換為斜杠的個數，因為我們構造數據的時候每種顏色前面都帶一個斜杠。為了求出字符串中某字符出現的次數，我們用了這樣的辦法：
先求出字符串的總長度；
用REPLACE函數從串中去除這個字符，然后再求一次長度；
兩個長度之差就是被去除的字符個數。
CASE函數把出現滿足條件的標記置為1,不滿足則為NULL, 那么再套一個COUNT函數就能算出滿足條件的行數，因為NULL是不被COUNT計入的。
COUNT和CASE的嵌套使用，也是在聚合運算中常用的技巧。
這個顏色數的計算，我們也可以在遞歸的過程中進行有條件累加，這樣最后就可以直接使用：
WITH T AS ( SELECT ROWNUM rn -- 還是先構造一個1,2,3,4的結果集 FROM DUAL CONNECT BY ROWNUM<=4 ) ,t2(distinct_colors,lvl,distinct_colors_cnt) AS ( --- 兩個列：所有不重復顏色，層次，不重復的顏色數 SELECT '\'||rn,1,1 ---- 第一層就是最基礎的四種顏色的表 FROM t UNION ALL SELECT CASE WHEN INSTR(t2.distinct_colors||'\','\'||t.rn||'\')=0 --- 這個顏色沒有出現過 THEN t2.distinct_colors||'\'||t.rn --- 拼上去 ELSE t2.distinct_colors ---- 顏色已經出現，保持原來的 END ,t2.lvl+1 --- 層數遞增 ,CASE WHEN INSTR(t2.distinct_colors||'\','\'||t.rn||'\')=0 --- 這個顏色沒有出現過 THEN t2.distinct_colors_cnt + 1 --- 顏色數累加 ELSE t2.distinct_colors_cnt ---- 顏色已經出現，數目不變 END FROM t, t2 WHERE t2.lvl<4 --- 遞歸出口的條件：次數達到限制 ) SELECT COUNT(CASE WHEN distinct_colors_cnt=3 THEN 1 END) --- 出現三個斜杠 /COUNT(*) FROM t2 WHERE lvl=4 ---- 同CONNECT BY類似，我們只需觀察最后一層的數據，在這里面已經包含了所有層次的顏色 ;
例4:
構造一個二階等差數列：這個數列的各項之差是一個等差數列
比如：1,3,6,10,15,21,...
用CONNECT BY:
SELECT LEVEL, SUM(LEVEL) OVER(ORDER BY LEVEL) n FROM DUAL CONNECT BY LEVEL<=10;
結果：
LEVEL N
---------- ----------
1 1
2 3
3 6
4 10
5 15
6 21
7 28
8 36
9 45
10 55
10 rows selected.
因為只有一條路徑，所以用分析函數SUM很輕易做到了。
遞歸WITH寫法：
WITH t(lvl,n) AS ( SELECT 1,1 FROM DUAL --- 先構造第一個 UNION ALL SELECT t.lvl+1, t.lvl+1+t.n ---- n的增幅本身是一個等差數列，即新的t.lvl FROM t ---- 沒有任何連接，因為不需要 WHERE t.lvl<10 ---- 找到10個就停止 ) SELECT * FROM T;
結果：
LVL N
---------- ----------
1 1
2 3
3 6
4 10
5 15
6 21
7 28
8 36
9 45
10 55
10 rows selected.
例5:
構造斐波那契數列: 指的是這樣一個數列, 從第三項開始，每一項都等于前兩項之和。
1,1,2,3,5,8,13,21,......
傳統的CONNECT BY方法做不出來，但是用10G以上所支持的MODEL可以輕松構造：
SELECT rn,n FROM (SELECT ROWNUM rn FROM DUAL CONNECT BY ROWNUM<=10) MODEL RETURN UPDATED ROWS DIMENSION BY (rn) MEASURES (1 n) RULES ( n[any] order by rn=DECODE(cv(rn),1,1,2,1, n[cv()-2]+n[cv()-1]) ---- 用DECODE構造最初的兩個，其余的則賦值為最近兩項之和 ) /
RN N
---------- ----------
1 1
2 1
3 2
4 3
5 5
6 8
7 13
8 21
9 34
10 55
10 rows selected.
用遞歸WITH的寫法：
WITH t(n,last_n,cnt) AS ( SELECT 1,0,1 FROM DUAL --- 先構造第一個 UNION ALL SELECT t.n+t.last_n, t.n, t.cnt+1 ---- 前兩項之和 FROM t ---- 沒有任何連接，因為不需要 WHERE t.cnt<10 ---- 找到10個就停止 ) SELECT n FROM T;
N
----------
1
1
2
3
5
8
13
21
34
55
10 rows selected.
例6:
排列組合:
從5個數中取3個的所有組合C(3,5):
CONNECT BY寫法：
SELECT SYS_CONNECT_BY_PATH(rn, ',') xmlpath FROM (SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL<6) WHERE LEVEL=3 CONNECT BY rn<PRIOR rn AND LEVEL<=3 ---- 強行按降序排序，這樣就排除了其他相同的、只是順序不同的組合 ;
XMLPATH
--------------
,5,4,3
,5,4,2
,5,4,1
,5,3,2
,5,3,1
,5,2,1
,4,3,2
,4,3,1
,4,2,1
,3,2,1
遞歸WITH寫法：
WITH t AS ( SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL<6 ) ,t2(rn,xmlpath,lvl) AS ( ---- 三個列：當前節點值，路徑，層數 SELECT rn,','||rn,1 FROM t ---- 先構造錨點成員的基礎數據，就是上面生成的6行數據的集合 UNION ALL SELECT t.rn,t2.xmlpath||','||t.rn,t2.lvl+1 --- 把當前節點拼接入路徑，層數則遞增 FROM t2, t WHERE t2.rn<t.rn AND t2.lvl<3 ) SELECT xmlpath FROM t2 WHERE lvl=3;
XMLPATH
-----------
,1,2,3
,1,2,4
,1,2,5
,1,3,4
,1,3,5
,1,4,5
,2,3,4
,2,3,5
,2,4,5
,3,4,5
10 rows selected.
如果要的不是組合而是排列，比如P(3,5)可以這么寫：
SELECT SYS_CONNECT_BY_PATH(rn, ',') xmlpath FROM (SELECT ROWNUM rn FROM DUAL CONNECT BY LEVEL<6) WHERE LEVEL=3 CONNECT BY NOCYCLE rn<>PRIOR rn AND LEVEL<=3;
XMLPATH
----------
,1,2,3
,1,2,4
,1,2,5
,1,3,2
,1,3,4
,1,3,5
,1,4,2
,1,4,3
,1,4,5
,1,5,2
,1,5,3
,1,5,4
,2,1,3
,2,1,4
......(其余結果略)
60 rows selected.
和剛才的組合寫法相比，rn<PRIOR rn變成了NOCYCLE rn<>PRIOR rn, 這表示只要rn沒出現過就行，我們要的是所有的排列順序而不僅僅是降序。注意這里面的NOCYCLE, 這個是10G上才有的。
如果不寫這個NOCYCLE會怎么樣？
SELECT SYS_CONNECT_BY_PATH(rn, ',') xmlpath FROM (SELECT ROWNUM rn FROM DUAL CONNECT BY LEVEL<6) WHERE LEVEL=3 CONNECT BY rn<>PRIOR rn AND LEVEL<=3;
ERROR:
ORA-01436: CONNECT BY loop in user data
可以看到，這個NOCYCLE是很重要的，ORACLE不允許遍歷順序中出現循環。
在遞歸WITH中，NOCYCLE的寫法：
WITH t AS ( SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL<6 ) ,T2(rn,xmlpath,lvl) AS ( ---- 三個列：當前節點值，路徑，層數 SELECT rn,','||rn,1 FROM t ---- 先構造錨點成員的基礎數據，就是上面生成的6行數據的集合 UNION ALL SELECT t.rn,t2.xmlpath||','||t.rn,t2.lvl+1 --- 把當前節點拼接入路徑，層數則遞增 FROM t2, t WHERE t2.rn<>t.rn AND t2.lvl<3 ) CYCLE rn SET cycle_flag TO 'Y' DEFAULT 'N' ---- 這個cycle_flag是自己定義的偽列名和值，可以起到CONNECT_BY_ISCYCLE同樣的作用 SELECT xmlpath FROM t2 WHERE lvl=3 AND cycle_flag='N';
結果：
XMLPA
SQL> with emp_data(ename,empno,mgr,l)
2 as
3 (select ename, empno, mgr, 1 lvl from emp where mgr is null
4 union all
5 select emp.ename, emp.empno, emp.mgr, ed.l+1
6 from emp, emp_data ed
7 where emp.mgr = ed.empno
8 )
9 SEARCH DEPTH FIRST BY ename SET order_by
10 select l,
11 lpad('*' ,2*l, '*')||ename nm
12 from emp_data
13 order by order_by
14 /
L NM
---- ---------------
1 **KING
2 ****BLAKE
3 ******ALLEN
3 ******JAMES
3 ******MARTIN
3 ******TURNER
3 ******WARD
2 ****CLARK
3 ******MILLER
2 ****JONES
3 ******FORD
4 ********SMITH
3 ******SCOTT
4 ********ADAMS
14 rows selected.
不知道真用起來怎么樣，按我的想象可以比原來的SYS_CONNECT_BY_PATH多玩出很多新花樣，比如按路徑累加，更靈活的剪枝條件，
WITH子查詢也稱為CTE (Common Table Expression)，是ANSI SQL-99標準的一部分。ORACLE從9i開始引入WITH子查詢，把它被稱作SUBQUERY FACTORING(分解子查詢)。
WITH子查詢的作用類似于內聯視圖(INLINE VIEW)。內聯視圖的定義寫作SQL的FROM 后面，只能夠引用一次；而WITH子查詢需要在引用之前先定義，一旦定義了在整個查詢的后續部分就可以按名稱來反復引用，從這點來看又很像臨時表。
從版本11GR2開始，ORACLE支持遞歸的WITH, 即允許在WITH子查詢的定義中對自身引用。這不是什么新鮮事，其他數據庫如DB2, Firebird, Microsoft SQL Server, PostgreSQL 都先于ORACLE支持這一特性。但對于ORACLE用戶來說，這一遞歸特性還是很令人期待的，利用它可以輕易實現以往做不到的、或者很難做到的許多新功能。這一章我們就來探索這一令人興奮的新特性，并把它和以往的實現手段（主要是CONNECT BY層次查詢）作比較。
我們先來看看這個遞歸WITH子查詢的語法：
WITH
① query_name ([c_alias [, c_alias]...])
② AS (subquery)
③ [search_clause]
④ [cycle_clause]
⑤ [,query_name ([c_alias [, c_alias]...]) AS (subquery) [search_clause] [cycle_clause]]...
①這是子查詢的名稱，和以往不同的是，必須在括號中把這個子查詢的所有列名寫出來。
②AS后面的subquery就是查詢語句，遞歸部分就寫在這里。
③遍歷順序子句，可以指定深度優先或廣度優先遍歷順序。
④循環子句，用于中止遍歷中出現的死循環。
⑤如果還有其他遞歸子查詢，定義同上。
subquery部分由兩個成員組成：anchor member(錨點成員) 和 recursive member(遞歸成員)。它們之間必須用union all聯合起來，anchor member 必須寫在recursive member前面。
anchor member用來定位遞歸的入口，錨點成員是一個SELECT語句，它不可以包含自身名稱(query_name)。這相當于CONNECT BY查詢中的START WITH，典型寫法就是:
SELECT ... FROM 要遍歷的表 WHERE ... (起始條件)
遞歸成員也是一個SELECT語句，用于定義上下級的關系，它必須包含自身名稱(即query_name)，而且僅僅只能引用一次。遞歸正是體現在對于自身的引用。典型的做法就是把query_name和其他表(一般來說就是你要遍歷的表)做一個連接，連接條件表明了上下級的關系。必須注意，在這個query_name中，并不是截止目前為止的所有數據都是可見的，可見的只是上次遞歸新加入的最近的一層數據。對query_name列的引用相當于CONNECT BY中的PRIOR操作符。當找不到滿足條件的下級，遍歷就會停止；如果你還有其他的遞歸出口條件，也可以一起寫在WHERE中，當WHERE不滿足時，遍歷就會停止，這就是在遍歷樹、圖時候的剪枝操作。越早停止則效率越高。
這個遞歸成員就是程序員發揮創造力的地方，以往在CONNECT BY中做不到的事情，比如沿路徑求和、求積等運算，現在都輕而易舉。而SYS_CONNECT_BY_PATH也很容易用字符串拼接'||'來實現。
搜索子句(search_clause)和循環子句(cycle_clause)我們后面的例子中會見到。
下面我們就來看看遞歸WITH子查詢的用法實例。
例1:
先來一個簡單例子，從scott/tiger的emp表來查找上下級關系：
傳統的CONNECT BY寫法：
SELECT empno ,ename ,job ,mgr ,deptno ,level ,SYS_CONNECT_BY_PATH(ename,'\') AS path ,CONNECT_BY_ROOT(ename) AS top_manager FROM EMP START WITH mgr IS NULL -- mgr列為空，表示沒有上級，該員工已經是最高級別。這是層次查詢的起點 CONNECT BY PRIOR empno= mgr;
新的遞歸WITH寫法:
WITH T(empno, ename, job, mgr, deptno, the_level, path,top_manager) AS ( ---- 必須把結構寫出來 SELECT empno, ename, job, mgr, deptno ---- 先寫錨點查詢，用START WITH的條件 ,1 AS the_level ---- 遞歸起點，第一層 ,'\'||ename ---- 路徑的第一截 ,ename AS top_manager ---- 原來的CONNECT_BY_ROOT FROM EMP WHERE mgr IS NULL ---- 原來的START WITH條件 UNION ALL ---- 下面是遞歸部分 SELECT e.empno, e.ename, e.job, e.mgr, e.deptno ---- 要加入的新一層數據，來自要遍歷的emp表 ,1 + t.the_level ---- 遞歸層次，在原來的基礎上加1。這相當于CONNECT BY查詢中的LEVEL偽列 ,t.path||'\'||e.ename ---- 把新的一截路徑拼上去 ,t.top_manager ---- 直接繼承原來的數據，因為每個路徑的根節點只有一個 FROM t, emp e ---- 典型寫法，把子查詢本身和要遍歷的表作一個連接 WHERE t.empno = e.mgr ---- 原來的CONNECT BY條件 ) ---- WITH定義結束 SELECT * FROM T ;
查詢結果：
EMPNO ENAME JOB MGR DEPTNO THE_LEVEL PATH TOP_MANAGE
------ ---------- --------- ------ ------- ---------- -------------------------- ----------
7839 KING PRESIDENT 10 1 \KING KING
7566 JONES MANAGER 7839 20 2 \KING\JONES KING
7698 BLAKE MANAGER 7839 30 2 \KING\BLAKE KING
7782 CLARK MANAGER 7839 10 2 \KING\CLARK KING
7499 ALLEN SALESMAN 7698 30 3 \KING\BLAKE\ALLEN KING
7521 WARD SALESMAN 7698 30 3 \KING\BLAKE\WARD KING
7654 MARTIN SALESMAN 7698 30 3 \KING\BLAKE\MARTIN KING
7788 SCOTT ANALYST 7566 20 3 \KING\JONES\SCOTT KING
7844 TURNER SALESMAN 7698 30 3 \KING\BLAKE\TURNER KING
7900 JAMES CLERK 7698 30 3 \KING\BLAKE\JAMES KING
7902 FORD ANALYST 7566 20 3 \KING\JONES\FORD KING
7934 MILLER CLERK 7782 10 3 \KING\CLARK\MILLER KING
7369 SMITH CLERK 7902 20 4 \KING\JONES\FORD\SMITH KING
7876 ADAMS CLERK 7788 20 4 \KING\JONES\SCOTT\ADAMS KING
14 rows selected.
從結果集的THE_LEVEL和PATH列可以清楚地看到數據是如何被一層一層疊加上去的。
例2:
構造等差數列：
CONNECT BY寫法：
這是一個非常特殊的用法，因為沒有上下級關系，只有遍歷的終止條件。像這類CONNECT BY我強烈推薦在只有一行的結果集上運行（比如FROM DUAL, 比如從一個聚合后的子查詢），在多行的集合上運行比較難以控制，頭腦必須很清醒。
（以下ROWNUM全部可以改成 LEVEL，效果一樣）： SELECT ROWNUM n ,ROWNUM*2 n2 ,DATE '2010-1-1'+ROWNUM-1 dt ,ADD_MONTHS(DATE '2010-1-1', ROWNUM-1) mon FROM DUAL CONNECT BY ROWNUM<=10;
結果：
N N2 DT MON
---------- ---------- ----------- -----------
1 2 2010-01-01 2010-01-01
2 4 2010-01-02 2010-02-01
3 6 2010-01-03 2010-03-01
4 8 2010-01-04 2010-04-01
5 10 2010-01-05 2010-05-01
6 12 2010-01-06 2010-06-01
7 14 2010-01-07 2010-07-01
8 16 2010-01-08 2010-08-01
9 18 2010-01-09 2010-09-01
10 20 2010-01-10 2010-10-01
10 rows selected.
這個簡潔優雅的寫法最早由Mikito Harakiri(從名字看是個日本人)在asktom網站(http://asktom.oracle.com)發表，現在已經風靡全世界的ORACLE社區。在這個方法被發現之前，一般采用的是從一個大的集合（表或視圖）中獲取ROWNUM的方法：
SELECT ROWNUM n, ROWNUM*2 n2, DATE '2010-1-1'+ROWNUM-1 dt, ADD_MONTHS(DATE '2010-1-1', ROWNUM-1) mon FROM ALL_OBJECTS ---- ALL_OBJECTS是個很大的系統視圖，它包含的行數足夠滿足一般的序列構造 WHERE ROWNUM<=10;
下面嘗試用遞歸WITH的寫法：
WITH t(n,n2,dt,mon) AS (
SELECT 1, 2,TO_DATE('2010-1-1','YYYY-MM-DD'),TO_DATE('2010-1-1','YYYY-MM-DD') FROM DUAL --- 先構造第一個
UNION ALL
SELECT t.n+1 ---- 遞增1
,t.n2+2 ---- 遞增2
,dt+1 ---- 下一日
,ADD_MONTHS(mon,1) ---- 下個月
FROM t ---- 沒有任何連接，因為不需要，所有數據都可以從錨點成員中衍生出來
WHERE t.n<10
)
SELECT * FROM T;
一切都按規矩來，竟然還是出錯了：
,ADD_MONTHS(mon,1) ---- 下個月
*
ERROR at line 6:
ORA-01790: expression must have same datatype as corresponding expression
改為字符串型看看：
WITH t(n,n2,dt,mon) AS ( SELECT 1, 2,'2010-01-01','2010-01-01' FROM DUAL ---- 用字符串來表示日期 UNION ALL SELECT t.n+1 ---- 遞增1 ,t.n2+2 ---- 遞增2 ,TO_CHAR(TO_DATE(t.dt,'YYYY-MM-DD')+1,'YYYY-MM-DD') ---- 先轉換為日期型，計算后換回字符串型 ,TO_CHAR(ADD_MONTHS(TO_DATE(t.mon,'YYYY-MM-DD'),1),'YYYY-MM-DD') ---- 計算下個月，方法同上 FROM t WHERE t.n<10 ) SELECT * FROM T;
我很驚奇地看到這個結果：
N N2 DT MON
---------- ---------- ---------- ----------
1 2 2010-01-01 2010-01-01
2 4 2009-12-31 2010-02-01 ----- DT竟然是遞減的！
3 6 2009-12-30 2010-03-01
4 8 2009-12-29 2010-04-01
5 10 2009-12-28 2010-05-01
6 12 2009-12-27 2010-06-01
7 14 2009-12-26 2010-07-01
8 16 2009-12-25 2010-08-01
9 18 2009-12-24 2010-09-01
10 20 2009-12-23 2010-10-01
10 rows selected.
這是ORACEL 11.2.0.1.0版本的BUG，后續版本應該會改正。
沒辦法，只好想其他招數繞過去:
WITH t(n) AS ( SELECT 1 FROM DUAL --- 先構造第一個 UNION ALL SELECT t.n+1 ---- 僅僅是整數序列 FROM t WHERE t.n<10 ) SELECT n ,n*2 n2 ,DATE '2010-1-1'+n-1 dt ---- 在最終的查詢中進行日期運算 ,ADD_MONTHS(DATE '2010-1-1', n-1) mon FROM T;
這下子對了：
N N2 DT MON
---------- ---------- ----------- -----------
1 2 2010-01-01 2010-01-01
2 4 2010-01-02 2010-02-01
3 6 2010-01-03 2010-03-01
4 8 2010-01-04 2010-04-01
5 10 2010-01-05 2010-05-01
6 12 2010-01-06 2010-06-01
7 14 2010-01-07 2010-07-01
8 16 2010-01-08 2010-08-01
9 18 2010-01-09 2010-09-01
10 20 2010-01-10 2010-10-01
10 rows selected.
看來對日期的運算有BUG。解決辦法就是先構造整數序列，然后在最終的查詢中再利用這個整數序列來構造日期序列。
從一個單行結果集CONNECT BY的例子:
SELECT ROWNUM rn,cnt FROM (SELECT COUNT(*) cnt FROM emp) ---- 經過聚合的只有一行的結果集 CONNECT BY ROWNUM<=cnt;
結果：
RN CNT
---------- ----------
1 14
2 14
3 14
4 14
5 14
6 14
7 14
8 14
9 14
10 14
11 14
12 14
13 14
14 14
14 rows selected.
遞歸WITH寫法：
WITH t(n,cnt) AS ( SELECT 1,COUNT(*) cnt FROM EMP --- 先構造第一個 UNION ALL SELECT t.n+1 ---- 遞增1 ,t.cnt ---- 這個cnt列不做任何修改，從第一層得來 FROM t ---- 沒有任何連接，因為不需要 WHERE t.n<t.cnt ---- 在這里看到cnt的作用，就是用于終止遍歷 ) SELECT * FROM t;
結果同上（略）。
例3:
獨立事件的排列組合：一個布袋中裝有數量相同的四種顏色的小球。隨機從布袋中取四次，每次取完都放回去?，F在問四次結果總顏色數等于3的概率是多少？
傳統的CONNECT BY寫法：
WITH t AS ( SELECT ROWNUM rn -- 先構造一個1,2,3,4的結果集，每個rn表示一種顏色 FROM DUAL CONNECT BY ROWNUM<=4 ) ,t2 AS ( ---- 集合t2模擬獨立取四次的動作，最終結果會有4*4*4*4=256行 SELECT ROWNUM id ---- 構造唯一ID供下面拆分用 ,REPLACE(SYS_CONNECT_BY_PATH(rn,'@'),'@') path ---- 用一個特殊字符@來作分隔符, 并在最后用REPLACE把它去除 ,COUNT(*) OVER() cnt ---- 利用分析函數算出總行數并把它作為一個列返回 FROM t ---- 這個是有四行的集合 WHERE LEVEL=4 ---- 我們需要的僅僅是最后一層的結果。在PATH里面已經包含了取四次的所有結果組合 CONNECT BY LEVEL<=4 ---- 沒有任何條件，前后都是獨立的 ) ,t3 AS ( ---- 集合t3把t2中的PATH包含的顏色組合拆開為四行 SELECT id,cnt,SUBSTR(PATH,rn,1) color FROM t2,t ---- 笛卡兒積，用于把t2中的一行變為四行 ) SELECT COUNT(COUNT(*))/MAX(cnt) AS prob FROM t3 GROUP BY id,cnt HAVING COUNT(DISTINCT color)=3 --- 每一個id中包含三種顏色 ;
結果：
PROB
----------
.5625
這個例子展示了CONNECT BY來模擬排列組合的技巧。每一層遍歷表示一次抽取的動作，因為每次都是完全獨立的，在CONNECT BY 里面僅僅限制了抽取次數（遍歷層數）而沒有其他條件。SYS_CONNECT_BY_PATH可以把截至當前為止所訪問到的各層次的數據串起來，在LEVEL=N就包含了前N層的排列組合情況。你可以用這個查詢來看看中間生成的結果集t2：
WITH t AS ( SELECT ROWNUM rn -- 先構造一個1,2,3,4的結果集，每個rn表示一種顏色 FROM DUAL CONNECT BY ROWNUM<=4 ) ,t2 AS ( ---- 集合t2模擬獨立取四次的動作，最終結果會有4*4*4*4=256行 SELECT ROWNUM id ---- 構造唯一ID供下面拆分用 ,REPLACE(SYS_CONNECT_BY_PATH(rn,'@'),'@') path ---- 用一個特殊字符@來作分隔符, 并在最后用REPLACE把它去除 ,COUNT(*) OVER() cnt ---- 利用分析函數算出總行數并把它作為一個列返回 FROM t ---- 這個是有四行的集合 WHERE LEVEL=4 ---- 我們需要的僅僅是最后一層的結果。在PATH里面已經包含了取四次的所有結果組合 CONNECT BY LEVEL<=4 ---- 沒有任何條件，前后都是獨立的 ) SELECT * FROM t2;
ID PATH CNT
---------- ---------- ----------
1 1111 256
2 1112 256
3 1113 256
4 1114 256
5 1121 256
6 1122 256
7 1123 256
8 1124 256
9 1131 256
10 1132 256
11 1133 256
......(其余結果略)
256 rows selected.
由此看到PATH列已經包含了四次抽取的所有可能結果，每個結果都被賦予一個唯一的編號ID。
如果你好奇的話可以看看下一步的結果集t3:
WITH t AS ( SELECT ROWNUM rn -- 先構造一個1,2,3,4的結果集，每個rn表示一種顏色 FROM DUAL CONNECT BY ROWNUM<=4 ) ,t2 AS ( ---- 集合t2模擬獨立取四次的動作，最終結果會有4*4*4*4=256行 SELECT ROWNUM id ---- 構造唯一ID供下面拆分用 ,REPLACE(SYS_CONNECT_BY_PATH(rn,'@'),'@') path ---- 用一個特殊字符@來作分隔符, 并在最后用REPLACE把它去除 ,COUNT(*) OVER() cnt ---- 利用分析函數算出總行數并把它作為一個列返回 FROM t ---- 這個是有四行的集合 WHERE LEVEL=4 ---- 我們需要的僅僅是最后一層的結果。在PATH里面已經包含了取四次的所有結果組合 CONNECT BY LEVEL<=4 ---- 沒有任何條件，前后都是獨立的 ) ,t3 AS ( ---- 集合t3把t2中的PATH包含的顏色組合拆開為四行 SELECT id,cnt,SUBSTR(PATH,rn,1) color FROM t2,t ---- 笛卡兒積，用于把t2中的一行變為四行 ) SELECT * FROM t3;
ID CNT COLO
---------- ---------- ----
1 256 1
1 256 1
1 256 1
1 256 1
2 256 1
2 256 1
2 256 1
2 256 2
3 256 1
3 256 1
3 256 1
3 256 3
4 256 1
4 256 1
4 256 1
4 256 4
......(其余結果略)
1024 rows selected.
可以看到t2集合中的每一行都被拆成了四行，這是為了后面的聚合運算。
最后看看算概率的主查詢：
SELECT COUNT(COUNT(*))/MAX(cnt) AS prob FROM t3 GROUP BY id,cnt HAVING COUNT(DISTINCT color)=3;
COUNT(DISTINCT color)可以算出每個ID中包含不重復的顏色數目，放在HAVING中過濾了數目不為3的那些ID。
GROUP BY id,cnt 表示按照id來分組。因為所有行的cnt都是一樣的(都等于256)，我們在分組加入它并不會改變分組的結果，加入cnt的目的是為了在查詢中引用。
最后的連續兩層COUNT函數的意思是要把分組結果再聚合為一行，算出滿足條件的id的行數。除以cnt就得到了我們要的概率。
本例是一個在多行的結果集上進行無條件遍歷的例子，前面說過了要特別小心，因為沒有上下級關系，隨著層數遞增，數據量的增長十分可觀。
遞歸WITH寫法：
WITH T AS (
SELECT ROWNUM rn -- 還是先構造一個1,2,3,4的結果集
FROM DUAL
CONNECT BY ROWNUM<=4
)
,t2(distinct_colors,lvl) AS ( --- 兩個列：所有不重復顏色，層次
SELECT '\'||rn,1 ---- 第一層就是最基礎的四種顏色的表
FROM t
UNION ALL
SELECT CASE WHEN INSTR(t2.distinct_colors||'\','\'||t.rn||'\')=0 --- 這個顏色沒有出現過
THEN t2.distinct_colors||'\'||t.rn --- 拼上去
ELSE t2.distinct_colors ---- 顏色已經出現，保持原來的
END
,t2.lvl+1 --- 層數遞增
FROM t, t2
WHERE t2.lvl<4 --- 遞歸出口的條件：次數達到限制
)
SELECT COUNT(CASE WHEN LENGTH(distinct_colors) - LENGTH(REPLACE(distinct_colors,'\'))=3 THEN 1 END) --- 出現三個斜杠
/COUNT(*)
FROM t2
WHERE lvl=4 ---- 同CONNECT BY類似，我們只需觀察最后一層的數據，在這里面已經包含了所有層次的顏色
;
在遞歸WITH子查詢t2中，我們看到它用了一個CASE表達式把以前沒出現過的顏色拼接到distinct_colors中。這個CASE是遞歸WITH的妙處，用SYS_CONNECT_BY_PATH沒辦法做到有條件的拼接。
而最后在計算顏色數的時候用了一個技巧，把顏色數轉換為斜杠的個數，因為我們構造數據的時候每種顏色前面都帶一個斜杠。為了求出字符串中某字符出現的次數，我們用了這樣的辦法：
先求出字符串的總長度；
用REPLACE函數從串中去除這個字符，然后再求一次長度；
兩個長度之差就是被去除的字符個數。
CASE函數把出現滿足條件的標記置為1,不滿足則為NULL, 那么再套一個COUNT函數就能算出滿足條件的行數，因為NULL是不被COUNT計入的。
COUNT和CASE的嵌套使用，也是在聚合運算中常用的技巧。
這個顏色數的計算，我們也可以在遞歸的過程中進行有條件累加，這樣最后就可以直接使用：
WITH T AS (
SELECT ROWNUM rn -- 還是先構造一個1,2,3,4的結果集
FROM DUAL
CONNECT BY ROWNUM<=4
)
,t2(distinct_colors,lvl,distinct_colors_cnt) AS ( --- 兩個列：所有不重復顏色，層次，不重復的顏色數
SELECT '\'||rn,1,1 ---- 第一層就是最基礎的四種顏色的表
FROM t
UNION ALL
SELECT CASE WHEN INSTR(t2.distinct_colors||'\','\'||t.rn||'\')=0 --- 這個顏色沒有出現過
THEN t2.distinct_colors||'\'||t.rn --- 拼上去
ELSE t2.distinct_colors ---- 顏色已經出現，保持原來的
END
,t2.lvl+1 --- 層數遞增
,CASE WHEN INSTR(t2.distinct_colors||'\','\'||t.rn||'\')=0 --- 這個顏色沒有出現過
THEN t2.distinct_colors_cnt + 1 --- 顏色數累加
ELSE t2.distinct_colors_cnt ---- 顏色已經出現，數目不變
END
FROM t, t2
WHERE t2.lvl<4 --- 遞歸出口的條件：次數達到限制
)
SELECT COUNT(CASE WHEN distinct_colors_cnt=3 THEN 1 END) --- 出現三個斜杠
/COUNT(*)
FROM t2
WHERE lvl=4 ---- 同CONNECT BY類似，我們只需觀察最后一層的數據，在這里面已經包含了所有層次的顏色
;
例4:
構造一個二階等差數列：這個數列的各項之差是一個等差數列
比如：1,3,6,10,15,21,...
用CONNECT BY:
SELECT LEVEL, SUM(LEVEL) OVER(ORDER BY LEVEL) n FROM DUAL CONNECT BY LEVEL<=10;
結果：
LEVEL N
---------- ----------
1 1
2 3
3 6
4 10
5 15
6 21
7 28
8 36
9 45
10 55
10 rows selected.
因為只有一條路徑，所以用分析函數SUM很輕易做到了。
遞歸WITH寫法：
WITH t(lvl,n) AS ( SELECT 1,1 FROM DUAL --- 先構造第一個 UNION ALL SELECT t.lvl+1, t.lvl+1+t.n ---- n的增幅本身是一個等差數列，即新的t.lvl FROM t ---- 沒有任何連接，因為不需要 WHERE t.lvl<10 ---- 找到10個就停止 ) SELECT * FROM T;
結果：
LVL N
---------- ----------
1 1
2 3
3 6
4 10
5 15
6 21
7 28
8 36
9 45
10 55
10 rows selected.
例5:
構造斐波那契數列: 指的是這樣一個數列, 從第三項開始，每一項都等于前兩項之和。
1,1,2,3,5,8,13,21,......
傳統的CONNECT BY方法做不出來，但是用10G以上所支持的MODEL可以輕松構造：
SELECT rn,n FROM (SELECT ROWNUM rn FROM DUAL CONNECT BY ROWNUM<=10) MODEL RETURN UPDATED ROWS DIMENSION BY (rn) MEASURES (1 n) RULES ( n[any] order by rn=DECODE(cv(rn),1,1,2,1, n[cv()-2]+n[cv()-1]) ---- 用DECODE構造最初的兩個，其余的則賦值為最近兩項之和 )
/
RN N
---------- ----------
1 1
2 1
3 2
4 3
5 5
6 8
7 13
8 21
9 34
10 55
10 rows selected.
用遞歸WITH的寫法：
WITH t(n,last_n,cnt) AS ( SELECT 1,0,1 FROM DUAL --- 先構造第一個 UNION ALL SELECT t.n+t.last_n, t.n, t.cnt+1 ---- 前兩項之和 FROM t ---- 沒有任何連接，因為不需要 WHERE t.cnt<10 ---- 找到10個就停止 ) SELECT n FROM T;
N
----------
1
1
2
3
5
8
13
21
34
55
10 rows selected.
例6:
排列組合:
從5個數中取3個的所有組合C(3,5):
CONNECT BY寫法：
SELECT SYS_CONNECT_BY_PATH(rn, ',') xmlpath FROM (SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL<6) WHERE LEVEL=3 CONNECT BY rn<PRIOR rn AND LEVEL<=3 ---- 強行按降序排序，這樣就排除了其他相同的、只是順序不同的組合 ;
XMLPATH
--------------
,5,4,3
,5,4,2
,5,4,1
,5,3,2
,5,3,1
,5,2,1
,4,3,2
,4,3,1
,4,2,1
,3,2,1
遞歸WITH寫法：
WITH t AS ( SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL<6 ) ,t2(rn,xmlpath,lvl) AS ( ---- 三個列：當前節點值，路徑，層數 SELECT rn,','||rn,1 FROM t ---- 先構造錨點成員的基礎數據，就是上面生成的6行數據的集合 UNION ALL SELECT t.rn,t2.xmlpath||','||t.rn,t2.lvl+1 --- 把當前節點拼接入路徑，層數則遞增 FROM t2, t WHERE t2.rn<t.rn AND t2.lvl<3 ) SELECT xmlpath FROM t2 WHERE lvl=3;
XMLPATH
-----------
,1,2,3
,1,2,4
,1,2,5
,1,3,4
,1,3,5
,1,4,5
,2,3,4
,2,3,5
,2,4,5
,3,4,5
10 rows selected.
如果要的不是組合而是排列，比如P(3,5)可以這么寫：
SELECT SYS_CONNECT_BY_PATH(rn, ',') xmlpath FROM (SELECT ROWNUM rn FROM DUAL CONNECT BY LEVEL<6) WHERE LEVEL=3 CONNECT BY NOCYCLE rn<>PRIOR rn AND LEVEL<=3;
XMLPATH
----------
,1,2,3
,1,2,4
,1,2,5
,1,3,2
,1,3,4
,1,3,5
,1,4,2
,1,4,3
,1,4,5
,1,5,2
,1,5,3
,1,5,4
,2,1,3
,2,1,4
......(其余結果略)
60 rows selected.
和剛才的組合寫法相比，rn<PRIOR rn變成了NOCYCLE rn<>PRIOR rn, 這表示只要rn沒出現過就行，我們要的是所有的排列順序而不僅僅是降序。注意這里面的NOCYCLE, 這個是10G上才有的。
如果不寫這個NOCYCLE會怎么樣？
SELECT SYS_CONNECT_BY_PATH(rn, ',') xmlpath FROM (SELECT ROWNUM rn FROM DUAL CONNECT BY LEVEL<6) WHERE LEVEL=3 CONNECT BY rn<>PRIOR rn AND LEVEL<=3;
ERROR:
ORA-01436: CONNECT BY loop in user data
可以看到，這個NOCYCLE是很重要的，ORACLE不允許遍歷順序中出現循環。
在遞歸WITH中，NOCYCLE的寫法：
WITH t AS ( SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL<6 ) ,T2(rn,xmlpath,lvl) AS ( ---- 三個列：當前節點值，路徑，層數 SELECT rn,','||rn,1 FROM t ---- 先構造錨點成員的基礎數據，就是上面生成的6行數據的集合 UNION ALL SELECT t.rn,t2.xmlpath||','||t.rn,t2.lvl+1 --- 把當前節點拼接入路徑，層數則遞增 FROM t2, t WHERE t2.rn<>t.rn AND t2.lvl<3 ) CYCLE rn SET cycle_flag TO 'Y' DEFAULT 'N' ---- 這個cycle_flag是自己定義的偽列名和值，可以起到CONNECT_BY_ISCYCLE同樣的作用 SELECT xmlpath FROM t2 WHERE lvl=3 AND cycle_flag='N';
結果：
XMLPA
以上所述是小編給大家介紹的Oracle 11GR2的遞歸WITH子查詢方法，希望對大家有所幫助，如果大家有任何疑問請給我留言，小編會及時回復大家的。在此也非常感謝大家對腳本之家網站的支持！
相關文章
修改計算機名或IP后Oracle10g服務無法啟動的解決方法
修改計算機名或IP后Oracle10g無法啟動服務即windows服務中有一項oracle服務啟動不了，報錯，下面是具體的解決方法2014-01-01
這篇文章主要介紹了Oracle 大小寫轉換函數實例詳解的相關資料,需要的朋友可以參考下2017-06-06
Oracle 數據庫操作技巧集...2007-03-03
這篇文章主要介紹了一些oracle的日期操作語句，幫助大家更好的學習和理解oracle，感興趣的朋友可以了解下2020-08-08
這篇文章主要介紹了ORACLE錯誤一覽表 方便查詢,需要的朋友可以參考下2015-12-12
Mysql使用limit分頁而Oracle使用rownum分頁，下面通過本文給大家介紹Oracle和Mysql分頁的區別，需要的的朋友參考下吧2017-09-09
今天小編就為大家分享一篇關于Oracle基礎:程序中調用sqlplus的方式，小編覺得內容挺不錯的，現在分享給大家，具有很好的參考價值，需要的朋友一起跟隨小編來看看吧2018-12-12![[Oracle] 淺談Lock與Latch](http://img.jbzj.com/images/xgimg/bcimg7.png)
以下是對Oracle中Lock與Latch的相同點以及不同點進行了詳細的分析介紹，需要的朋友參考下2013-07-07
如何將Oracle的一個大數據表快速遷移到 Sqlserver2008數據庫(圖文教程)
這篇文章主要介紹了如何將Oracle的一個大數據表快速遷移到 Sqlserver2008數據庫(圖文教程),本文圖文并茂給大家介紹的非常詳細，需要的的朋友參考下吧2017-05-05
Oracle9i取得建表和索引的DDL語句...2007-03-03
最新評論