Oracle 11GR2的遞歸WITH子查詢方法
下面給大家詳細介紹Oracle 11GR2的遞歸WITH子查詢方法，具體內(nèi)容如下所示：
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子查詢的作用類似于內(nèi)聯(lián)視圖(INLINE VIEW)。內(nèi)聯(lián)視圖的定義寫作SQL的FROM 后面，只能夠引用一次；而WITH子查詢需要在引用之前先定義，一旦定義了在整個查詢的后續(xù)部分就可以按名稱來反復(fù)引用，從這點來看又很像臨時表。
從版本11GR2開始，ORACLE支持遞歸的WITH, 即允許在WITH子查詢的定義中對自身引用。這不是什么新鮮事，其他數(shù)據(jù)庫如DB2, Firebird, Microsoft SQL Server, PostgreSQL 都先于ORACLE支持這一特性。但對于ORACLE用戶來說，這一遞歸特性還是很令人期待的，利用它可以輕易實現(xiàn)以往做不到的、或者很難做到的許多新功能。這一章我們就來探索這一令人興奮的新特性，并把它和以往的實現(xiàn)手段（主要是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就是查詢語句，遞歸部分就寫在這里。
③遍歷順序子句，可以指定深度優(yōu)先或廣度優(yōu)先遍歷順序。
④循環(huán)子句，用于中止遍歷中出現(xiàn)的死循環(huán)。
⑤如果還有其他遞歸子查詢，定義同上。
subquery部分由兩個成員組成：anchor member(錨點成員) 和 recursive member(遞歸成員)。它們之間必須用union all聯(lián)合起來，anchor member 必須寫在recursive member前面。
anchor member用來定位遞歸的入口，錨點成員是一個SELECT語句，它不可以包含自身名稱(query_name)。這相當(dāng)于CONNECT BY查詢中的START WITH，典型寫法就是:
SELECT ... FROM 要遍歷的表 WHERE ... (起始條件)
遞歸成員也是一個SELECT語句，用于定義上下級的關(guān)系，它必須包含自身名稱(即query_name)，而且僅僅只能引用一次。遞歸正是體現(xiàn)在對于自身的引用。典型的做法就是把query_name和其他表(一般來說就是你要遍歷的表)做一個連接，連接條件表明了上下級的關(guān)系。必須注意，在這個query_name中，并不是截止目前為止的所有數(shù)據(jù)都是可見的，可見的只是上次遞歸新加入的最近的一層數(shù)據(jù)。對query_name列的引用相當(dāng)于CONNECT BY中的PRIOR操作符。當(dāng)找不到滿足條件的下級，遍歷就會停止；如果你還有其他的遞歸出口條件，也可以一起寫在WHERE中，當(dāng)WHERE不滿足時，遍歷就會停止，這就是在遍歷樹、圖時候的剪枝操作。越早停止則效率越高。
這個遞歸成員就是程序員發(fā)揮創(chuàng)造力的地方，以往在CONNECT BY中做不到的事情，比如沿路徑求和、求積等運算，現(xiàn)在都輕而易舉。而SYS_CONNECT_BY_PATH也很容易用字符串拼接'||'來實現(xiàn)。
搜索子句(search_clause)和循環(huán)子句(cycle_clause)我們后面的例子中會見到。
下面我們就來看看遞歸WITH子查詢的用法實例。
例1:
先來一個簡單例子，從scott/tiger的emp表來查找上下級關(guān)系：
傳統(tǒng)的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列為空，表示沒有上級，該員工已經(jīng)是最高級別。這是層次查詢的起點 CONNECT BY PRIOR empno= mgr;
新的遞歸WITH寫法:
WITH T(empno, ename, job, mgr, deptno, the_level, path,top_manager) AS ( ---- 必須把結(jié)構(gòu)寫出來 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 ---- 要加入的新一層數(shù)據(jù)，來自要遍歷的emp表 ,1 + t.the_level ---- 遞歸層次，在原來的基礎(chǔ)上加1。這相當(dāng)于CONNECT BY查詢中的LEVEL偽列 ,t.path||'\'||e.ename ---- 把新的一截路徑拼上去 ,t.top_manager ---- 直接繼承原來的數(shù)據(jù)，因為每個路徑的根節(jié)點只有一個 FROM t, emp e ---- 典型寫法，把子查詢本身和要遍歷的表作一個連接 WHERE t.empno = e.mgr ---- 原來的CONNECT BY條件 ) ---- WITH定義結(jié)束 SELECT * FROM T ;
查詢結(jié)果：
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.
從結(jié)果集的THE_LEVEL和PATH列可以清楚地看到數(shù)據(jù)是如何被一層一層疊加上去的。
例2:
構(gòu)造等差數(shù)列：
CONNECT BY寫法：
這是一個非常特殊的用法，因為沒有上下級關(guān)系，只有遍歷的終止條件。像這類CONNECT BY我強烈推薦在只有一行的結(jié)果集上運行（比如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;
結(jié)果：
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.
這個簡潔優(yōu)雅的寫法最早由Mikito Harakiri(從名字看是個日本人)在asktom網(wǎng)站(http://asktom.oracle.com)發(fā)表，現(xiàn)在已經(jīng)風(fēng)靡全世界的ORACLE社區(qū)。在這個方法被發(fā)現(xiàn)之前，一般采用的是從一個大的集合（表或視圖）中獲取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是個很大的系統(tǒng)視圖，它包含的行數(shù)足夠滿足一般的序列構(gòu)造 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 --- 先構(gòu)造第一個
UNION ALL
SELECT t.n+1 ---- 遞增1
,t.n2+2 ---- 遞增2
,dt+1 ---- 下一日
,ADD_MONTHS(mon,1) ---- 下個月
FROM t ---- 沒有任何連接，因為不需要，所有數(shù)據(jù)都可以從錨點成員中衍生出來
WHERE t.n<10
)
SELECT * FROM T;
一切都按規(guī)矩來，竟然還是出錯了：
,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') ---- 先轉(zhuǎn)換為日期型，計算后換回字符串型 ,TO_CHAR(ADD_MONTHS(TO_DATE(t.mon,'YYYY-MM-DD'),1),'YYYY-MM-DD') ---- 計算下個月，方法同上 FROM t WHERE t.n<10 ) SELECT * FROM T;
我很驚奇地看到這個結(jié)果：
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，后續(xù)版本應(yīng)該會改正。
沒辦法，只好想其他招數(shù)繞過去:
WITH t(n) AS ( SELECT 1 FROM DUAL --- 先構(gòu)造第一個 UNION ALL SELECT t.n+1 ---- 僅僅是整數(shù)序列 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。解決辦法就是先構(gòu)造整數(shù)序列，然后在最終的查詢中再利用這個整數(shù)序列來構(gòu)造日期序列。
從一個單行結(jié)果集CONNECT BY的例子:
SELECT ROWNUM rn,cnt FROM (SELECT COUNT(*) cnt FROM emp) ---- 經(jīng)過聚合的只有一行的結(jié)果集 CONNECT BY ROWNUM<=cnt;
結(jié)果：
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 --- 先構(gòu)造第一個 UNION ALL SELECT t.n+1 ---- 遞增1 ,t.cnt ---- 這個cnt列不做任何修改，從第一層得來 FROM t ---- 沒有任何連接，因為不需要 WHERE t.n<t.cnt ---- 在這里看到cnt的作用，就是用于終止遍歷 ) SELECT * FROM t;
結(jié)果同上（略）。
例3:
獨立事件的排列組合：一個布袋中裝有數(shù)量相同的四種顏色的小球。隨機從布袋中取四次，每次取完都放回去?，F(xiàn)在問四次結(jié)果總顏色數(shù)等于3的概率是多少？
傳統(tǒng)的CONNECT BY寫法：
WITH t AS ( SELECT ROWNUM rn -- 先構(gòu)造一個1,2,3,4的結(jié)果集，每個rn表示一種顏色 FROM DUAL CONNECT BY ROWNUM<=4 ) ,t2 AS ( ---- 集合t2模擬獨立取四次的動作，最終結(jié)果會有4*4*4*4=256行 SELECT ROWNUM id ---- 構(gòu)造唯一ID供下面拆分用 ,REPLACE(SYS_CONNECT_BY_PATH(rn,'@'),'@') path ---- 用一個特殊字符@來作分隔符, 并在最后用REPLACE把它去除 ,COUNT(*) OVER() cnt ---- 利用分析函數(shù)算出總行數(shù)并把它作為一個列返回 FROM t ---- 這個是有四行的集合 WHERE LEVEL=4 ---- 我們需要的僅僅是最后一層的結(jié)果。在PATH里面已經(jīng)包含了取四次的所有結(jié)果組合 CONNECT BY LEVEL<=4 ---- 沒有任何條件，前后都是獨立的 ) ,t3 AS ( ---- 集合t3把t2中的PATH包含的顏色組合拆開為四行 SELECT id,cnt,SUBSTR(PATH,rn,1) color FROM t2,t ---- 笛卡兒積，用于把t2中的一行變?yōu)樗男? ) SELECT COUNT(COUNT(*))/MAX(cnt) AS prob FROM t3 GROUP BY id,cnt HAVING COUNT(DISTINCT color)=3 --- 每一個id中包含三種顏色 ;
結(jié)果：
PROB
----------
.5625
這個例子展示了CONNECT BY來模擬排列組合的技巧。每一層遍歷表示一次抽取的動作，因為每次都是完全獨立的，在CONNECT BY 里面僅僅限制了抽取次數(shù)（遍歷層數(shù)）而沒有其他條件。SYS_CONNECT_BY_PATH可以把截至當(dāng)前為止所訪問到的各層次的數(shù)據(jù)串起來，在LEVEL=N就包含了前N層的排列組合情況。你可以用這個查詢來看看中間生成的結(jié)果集t2：
WITH t AS ( SELECT ROWNUM rn -- 先構(gòu)造一個1,2,3,4的結(jié)果集，每個rn表示一種顏色 FROM DUAL CONNECT BY ROWNUM<=4 ) ,t2 AS ( ---- 集合t2模擬獨立取四次的動作，最終結(jié)果會有4*4*4*4=256行 SELECT ROWNUM id ---- 構(gòu)造唯一ID供下面拆分用 ,REPLACE(SYS_CONNECT_BY_PATH(rn,'@'),'@') path ---- 用一個特殊字符@來作分隔符, 并在最后用REPLACE把它去除 ,COUNT(*) OVER() cnt ---- 利用分析函數(shù)算出總行數(shù)并把它作為一個列返回 FROM t ---- 這個是有四行的集合 WHERE LEVEL=4 ---- 我們需要的僅僅是最后一層的結(jié)果。在PATH里面已經(jīng)包含了取四次的所有結(jié)果組合 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
......(其余結(jié)果略)
256 rows selected.
由此看到PATH列已經(jīng)包含了四次抽取的所有可能結(jié)果，每個結(jié)果都被賦予一個唯一的編號ID。
如果你好奇的話可以看看下一步的結(jié)果集t3:
WITH t AS ( SELECT ROWNUM rn -- 先構(gòu)造一個1,2,3,4的結(jié)果集，每個rn表示一種顏色 FROM DUAL CONNECT BY ROWNUM<=4 ) ,t2 AS ( ---- 集合t2模擬獨立取四次的動作，最終結(jié)果會有4*4*4*4=256行 SELECT ROWNUM id ---- 構(gòu)造唯一ID供下面拆分用 ,REPLACE(SYS_CONNECT_BY_PATH(rn,'@'),'@') path ---- 用一個特殊字符@來作分隔符, 并在最后用REPLACE把它去除 ,COUNT(*) OVER() cnt ---- 利用分析函數(shù)算出總行數(shù)并把它作為一個列返回 FROM t ---- 這個是有四行的集合 WHERE LEVEL=4 ---- 我們需要的僅僅是最后一層的結(jié)果。在PATH里面已經(jīng)包含了取四次的所有結(jié)果組合 CONNECT BY LEVEL<=4 ---- 沒有任何條件，前后都是獨立的 ) ,t3 AS ( ---- 集合t3把t2中的PATH包含的顏色組合拆開為四行 SELECT id,cnt,SUBSTR(PATH,rn,1) color FROM t2,t ---- 笛卡兒積，用于把t2中的一行變?yōu)樗男? ) 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
......(其余結(jié)果略)
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中包含不重復(fù)的顏色數(shù)目，放在HAVING中過濾了數(shù)目不為3的那些ID。
GROUP BY id,cnt 表示按照id來分組。因為所有行的cnt都是一樣的(都等于256)，我們在分組加入它并不會改變分組的結(jié)果，加入cnt的目的是為了在查詢中引用。
最后的連續(xù)兩層COUNT函數(shù)的意思是要把分組結(jié)果再聚合為一行，算出滿足條件的id的行數(shù)。除以cnt就得到了我們要的概率。
本例是一個在多行的結(jié)果集上進行無條件遍歷的例子，前面說過了要特別小心，因為沒有上下級關(guān)系，隨著層數(shù)遞增，數(shù)據(jù)量的增長十分可觀。
遞歸WITH寫法：
WITH T AS ( SELECT ROWNUM rn -- 還是先構(gòu)造一個1,2,3,4的結(jié)果集 FROM DUAL CONNECT BY ROWNUM<=4 ) ,t2(distinct_colors,lvl) AS ( --- 兩個列：所有不重復(fù)顏色，層次 SELECT '\'||rn,1 ---- 第一層就是最基礎(chǔ)的四種顏色的表 FROM t UNION ALL SELECT CASE WHEN INSTR(t2.distinct_colors||'\','\'||t.rn||'\')=0 --- 這個顏色沒有出現(xiàn)過 THEN t2.distinct_colors||'\'||t.rn --- 拼上去 ELSE t2.distinct_colors ---- 顏色已經(jīng)出現(xiàn)，保持原來的 END ,t2.lvl+1 --- 層數(shù)遞增 FROM t, t2 WHERE t2.lvl<4 --- 遞歸出口的條件：次數(shù)達到限制 ) SELECT COUNT(CASE WHEN LENGTH(distinct_colors) - LENGTH(REPLACE(distinct_colors,'\'))=3 THEN 1 END) --- 出現(xiàn)三個斜杠 /COUNT(*) FROM t2 WHERE lvl=4 ---- 同CONNECT BY類似，我們只需觀察最后一層的數(shù)據(jù)，在這里面已經(jīng)包含了所有層次的顏色 ;
在遞歸WITH子查詢t2中，我們看到它用了一個CASE表達式把以前沒出現(xiàn)過的顏色拼接到distinct_colors中。這個CASE是遞歸WITH的妙處，用SYS_CONNECT_BY_PATH沒辦法做到有條件的拼接。
而最后在計算顏色數(shù)的時候用了一個技巧，把顏色數(shù)轉(zhuǎn)換為斜杠的個數(shù)，因為我們構(gòu)造數(shù)據(jù)的時候每種顏色前面都帶一個斜杠。為了求出字符串中某字符出現(xiàn)的次數(shù)，我們用了這樣的辦法：
先求出字符串的總長度；
用REPLACE函數(shù)從串中去除這個字符，然后再求一次長度；
兩個長度之差就是被去除的字符個數(shù)。
CASE函數(shù)把出現(xiàn)滿足條件的標記置為1,不滿足則為NULL, 那么再套一個COUNT函數(shù)就能算出滿足條件的行數(shù)，因為NULL是不被COUNT計入的。
COUNT和CASE的嵌套使用，也是在聚合運算中常用的技巧。
這個顏色數(shù)的計算，我們也可以在遞歸的過程中進行有條件累加，這樣最后就可以直接使用：
WITH T AS ( SELECT ROWNUM rn -- 還是先構(gòu)造一個1,2,3,4的結(jié)果集 FROM DUAL CONNECT BY ROWNUM<=4 ) ,t2(distinct_colors,lvl,distinct_colors_cnt) AS ( --- 兩個列：所有不重復(fù)顏色，層次，不重復(fù)的顏色數(shù) SELECT '\'||rn,1,1 ---- 第一層就是最基礎(chǔ)的四種顏色的表 FROM t UNION ALL SELECT CASE WHEN INSTR(t2.distinct_colors||'\','\'||t.rn||'\')=0 --- 這個顏色沒有出現(xiàn)過 THEN t2.distinct_colors||'\'||t.rn --- 拼上去 ELSE t2.distinct_colors ---- 顏色已經(jīng)出現(xiàn)，保持原來的 END ,t2.lvl+1 --- 層數(shù)遞增 ,CASE WHEN INSTR(t2.distinct_colors||'\','\'||t.rn||'\')=0 --- 這個顏色沒有出現(xiàn)過 THEN t2.distinct_colors_cnt + 1 --- 顏色數(shù)累加 ELSE t2.distinct_colors_cnt ---- 顏色已經(jīng)出現(xiàn)，數(shù)目不變 END FROM t, t2 WHERE t2.lvl<4 --- 遞歸出口的條件：次數(shù)達到限制 ) SELECT COUNT(CASE WHEN distinct_colors_cnt=3 THEN 1 END) --- 出現(xiàn)三個斜杠 /COUNT(*) FROM t2 WHERE lvl=4 ---- 同CONNECT BY類似，我們只需觀察最后一層的數(shù)據(jù)，在這里面已經(jīng)包含了所有層次的顏色 ;
例4:
構(gòu)造一個二階等差數(shù)列：這個數(shù)列的各項之差是一個等差數(shù)列
比如：1,3,6,10,15,21,...
用CONNECT BY:
SELECT LEVEL, SUM(LEVEL) OVER(ORDER BY LEVEL) n FROM DUAL CONNECT BY LEVEL<=10;
結(jié)果：
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.
因為只有一條路徑，所以用分析函數(shù)SUM很輕易做到了。
遞歸WITH寫法：
WITH t(lvl,n) AS ( SELECT 1,1 FROM DUAL --- 先構(gòu)造第一個 UNION ALL SELECT t.lvl+1, t.lvl+1+t.n ---- n的增幅本身是一個等差數(shù)列，即新的t.lvl FROM t ---- 沒有任何連接，因為不需要 WHERE t.lvl<10 ---- 找到10個就停止 ) SELECT * FROM T;
結(jié)果：
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:
構(gòu)造斐波那契數(shù)列: 指的是這樣一個數(shù)列, 從第三項開始，每一項都等于前兩項之和。
1,1,2,3,5,8,13,21,......
傳統(tǒng)的CONNECT BY方法做不出來，但是用10G以上所支持的MODEL可以輕松構(gòu)造：
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構(gòu)造最初的兩個，其余的則賦值為最近兩項之和 ) /
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 --- 先構(gòu)造第一個 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個數(shù)中取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 ( ---- 三個列：當(dāng)前節(jié)點值，路徑，層數(shù) SELECT rn,','||rn,1 FROM t ---- 先構(gòu)造錨點成員的基礎(chǔ)數(shù)據(jù)，就是上面生成的6行數(shù)據(jù)的集合 UNION ALL SELECT t.rn,t2.xmlpath||','||t.rn,t2.lvl+1 --- 把當(dāng)前節(jié)點拼接入路徑，層數(shù)則遞增 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
......(其余結(jié)果略)
60 rows selected.
和剛才的組合寫法相比，rn<PRIOR rn變成了NOCYCLE rn<>PRIOR rn, 這表示只要rn沒出現(xiàn)過就行，我們要的是所有的排列順序而不僅僅是降序。注意這里面的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不允許遍歷順序中出現(xiàn)循環(huán)。
在遞歸WITH中，NOCYCLE的寫法：
WITH t AS ( SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL<6 ) ,T2(rn,xmlpath,lvl) AS ( ---- 三個列：當(dāng)前節(jié)點值，路徑，層數(shù) SELECT rn,','||rn,1 FROM t ---- 先構(gòu)造錨點成員的基礎(chǔ)數(shù)據(jù)，就是上面生成的6行數(shù)據(jù)的集合 UNION ALL SELECT t.rn,t2.xmlpath||','||t.rn,t2.lvl+1 --- 把當(dāng)前節(jié)點拼接入路徑，層數(shù)則遞增 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';
結(jié)果：
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子查詢的作用類似于內(nèi)聯(lián)視圖(INLINE VIEW)。內(nèi)聯(lián)視圖的定義寫作SQL的FROM 后面，只能夠引用一次；而WITH子查詢需要在引用之前先定義，一旦定義了在整個查詢的后續(xù)部分就可以按名稱來反復(fù)引用，從這點來看又很像臨時表。
從版本11GR2開始，ORACLE支持遞歸的WITH, 即允許在WITH子查詢的定義中對自身引用。這不是什么新鮮事，其他數(shù)據(jù)庫如DB2, Firebird, Microsoft SQL Server, PostgreSQL 都先于ORACLE支持這一特性。但對于ORACLE用戶來說，這一遞歸特性還是很令人期待的，利用它可以輕易實現(xiàn)以往做不到的、或者很難做到的許多新功能。這一章我們就來探索這一令人興奮的新特性，并把它和以往的實現(xiàn)手段（主要是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就是查詢語句，遞歸部分就寫在這里。
③遍歷順序子句，可以指定深度優(yōu)先或廣度優(yōu)先遍歷順序。
④循環(huán)子句，用于中止遍歷中出現(xiàn)的死循環(huán)。
⑤如果還有其他遞歸子查詢，定義同上。
subquery部分由兩個成員組成：anchor member(錨點成員) 和 recursive member(遞歸成員)。它們之間必須用union all聯(lián)合起來，anchor member 必須寫在recursive member前面。
anchor member用來定位遞歸的入口，錨點成員是一個SELECT語句，它不可以包含自身名稱(query_name)。這相當(dāng)于CONNECT BY查詢中的START WITH，典型寫法就是:
SELECT ... FROM 要遍歷的表 WHERE ... (起始條件)
遞歸成員也是一個SELECT語句，用于定義上下級的關(guān)系，它必須包含自身名稱(即query_name)，而且僅僅只能引用一次。遞歸正是體現(xiàn)在對于自身的引用。典型的做法就是把query_name和其他表(一般來說就是你要遍歷的表)做一個連接，連接條件表明了上下級的關(guān)系。必須注意，在這個query_name中，并不是截止目前為止的所有數(shù)據(jù)都是可見的，可見的只是上次遞歸新加入的最近的一層數(shù)據(jù)。對query_name列的引用相當(dāng)于CONNECT BY中的PRIOR操作符。當(dāng)找不到滿足條件的下級，遍歷就會停止；如果你還有其他的遞歸出口條件，也可以一起寫在WHERE中，當(dāng)WHERE不滿足時，遍歷就會停止，這就是在遍歷樹、圖時候的剪枝操作。越早停止則效率越高。
這個遞歸成員就是程序員發(fā)揮創(chuàng)造力的地方，以往在CONNECT BY中做不到的事情，比如沿路徑求和、求積等運算，現(xiàn)在都輕而易舉。而SYS_CONNECT_BY_PATH也很容易用字符串拼接'||'來實現(xiàn)。
搜索子句(search_clause)和循環(huán)子句(cycle_clause)我們后面的例子中會見到。
下面我們就來看看遞歸WITH子查詢的用法實例。
例1:
先來一個簡單例子，從scott/tiger的emp表來查找上下級關(guān)系：
傳統(tǒng)的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列為空，表示沒有上級，該員工已經(jīng)是最高級別。這是層次查詢的起點 CONNECT BY PRIOR empno= mgr;
新的遞歸WITH寫法:
WITH T(empno, ename, job, mgr, deptno, the_level, path,top_manager) AS ( ---- 必須把結(jié)構(gòu)寫出來 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 ---- 要加入的新一層數(shù)據(jù)，來自要遍歷的emp表 ,1 + t.the_level ---- 遞歸層次，在原來的基礎(chǔ)上加1。這相當(dāng)于CONNECT BY查詢中的LEVEL偽列 ,t.path||'\'||e.ename ---- 把新的一截路徑拼上去 ,t.top_manager ---- 直接繼承原來的數(shù)據(jù)，因為每個路徑的根節(jié)點只有一個 FROM t, emp e ---- 典型寫法，把子查詢本身和要遍歷的表作一個連接 WHERE t.empno = e.mgr ---- 原來的CONNECT BY條件 ) ---- WITH定義結(jié)束 SELECT * FROM T ;
查詢結(jié)果：
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.
從結(jié)果集的THE_LEVEL和PATH列可以清楚地看到數(shù)據(jù)是如何被一層一層疊加上去的。
例2:
構(gòu)造等差數(shù)列：
CONNECT BY寫法：
這是一個非常特殊的用法，因為沒有上下級關(guān)系，只有遍歷的終止條件。像這類CONNECT BY我強烈推薦在只有一行的結(jié)果集上運行（比如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;
結(jié)果：
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.
這個簡潔優(yōu)雅的寫法最早由Mikito Harakiri(從名字看是個日本人)在asktom網(wǎng)站(http://asktom.oracle.com)發(fā)表，現(xiàn)在已經(jīng)風(fēng)靡全世界的ORACLE社區(qū)。在這個方法被發(fā)現(xiàn)之前，一般采用的是從一個大的集合（表或視圖）中獲取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是個很大的系統(tǒng)視圖，它包含的行數(shù)足夠滿足一般的序列構(gòu)造 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 --- 先構(gòu)造第一個
UNION ALL
SELECT t.n+1 ---- 遞增1
,t.n2+2 ---- 遞增2
,dt+1 ---- 下一日
,ADD_MONTHS(mon,1) ---- 下個月
FROM t ---- 沒有任何連接，因為不需要，所有數(shù)據(jù)都可以從錨點成員中衍生出來
WHERE t.n<10
)
SELECT * FROM T;
一切都按規(guī)矩來，竟然還是出錯了：
,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') ---- 先轉(zhuǎn)換為日期型，計算后換回字符串型 ,TO_CHAR(ADD_MONTHS(TO_DATE(t.mon,'YYYY-MM-DD'),1),'YYYY-MM-DD') ---- 計算下個月，方法同上 FROM t WHERE t.n<10 ) SELECT * FROM T;
我很驚奇地看到這個結(jié)果：
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，后續(xù)版本應(yīng)該會改正。
沒辦法，只好想其他招數(shù)繞過去:
WITH t(n) AS ( SELECT 1 FROM DUAL --- 先構(gòu)造第一個 UNION ALL SELECT t.n+1 ---- 僅僅是整數(shù)序列 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。解決辦法就是先構(gòu)造整數(shù)序列，然后在最終的查詢中再利用這個整數(shù)序列來構(gòu)造日期序列。
從一個單行結(jié)果集CONNECT BY的例子:
SELECT ROWNUM rn,cnt FROM (SELECT COUNT(*) cnt FROM emp) ---- 經(jīng)過聚合的只有一行的結(jié)果集 CONNECT BY ROWNUM<=cnt;
結(jié)果：
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 --- 先構(gòu)造第一個 UNION ALL SELECT t.n+1 ---- 遞增1 ,t.cnt ---- 這個cnt列不做任何修改，從第一層得來 FROM t ---- 沒有任何連接，因為不需要 WHERE t.n<t.cnt ---- 在這里看到cnt的作用，就是用于終止遍歷 ) SELECT * FROM t;
結(jié)果同上（略）。
例3:
獨立事件的排列組合：一個布袋中裝有數(shù)量相同的四種顏色的小球。隨機從布袋中取四次，每次取完都放回去。現(xiàn)在問四次結(jié)果總顏色數(shù)等于3的概率是多少？
傳統(tǒng)的CONNECT BY寫法：
WITH t AS ( SELECT ROWNUM rn -- 先構(gòu)造一個1,2,3,4的結(jié)果集，每個rn表示一種顏色 FROM DUAL CONNECT BY ROWNUM<=4 ) ,t2 AS ( ---- 集合t2模擬獨立取四次的動作，最終結(jié)果會有4*4*4*4=256行 SELECT ROWNUM id ---- 構(gòu)造唯一ID供下面拆分用 ,REPLACE(SYS_CONNECT_BY_PATH(rn,'@'),'@') path ---- 用一個特殊字符@來作分隔符, 并在最后用REPLACE把它去除 ,COUNT(*) OVER() cnt ---- 利用分析函數(shù)算出總行數(shù)并把它作為一個列返回 FROM t ---- 這個是有四行的集合 WHERE LEVEL=4 ---- 我們需要的僅僅是最后一層的結(jié)果。在PATH里面已經(jīng)包含了取四次的所有結(jié)果組合 CONNECT BY LEVEL<=4 ---- 沒有任何條件，前后都是獨立的 ) ,t3 AS ( ---- 集合t3把t2中的PATH包含的顏色組合拆開為四行 SELECT id,cnt,SUBSTR(PATH,rn,1) color FROM t2,t ---- 笛卡兒積，用于把t2中的一行變?yōu)樗男? ) SELECT COUNT(COUNT(*))/MAX(cnt) AS prob FROM t3 GROUP BY id,cnt HAVING COUNT(DISTINCT color)=3 --- 每一個id中包含三種顏色 ;
結(jié)果：
PROB
----------
.5625
這個例子展示了CONNECT BY來模擬排列組合的技巧。每一層遍歷表示一次抽取的動作，因為每次都是完全獨立的，在CONNECT BY 里面僅僅限制了抽取次數(shù)（遍歷層數(shù)）而沒有其他條件。SYS_CONNECT_BY_PATH可以把截至當(dāng)前為止所訪問到的各層次的數(shù)據(jù)串起來，在LEVEL=N就包含了前N層的排列組合情況。你可以用這個查詢來看看中間生成的結(jié)果集t2：
WITH t AS ( SELECT ROWNUM rn -- 先構(gòu)造一個1,2,3,4的結(jié)果集，每個rn表示一種顏色 FROM DUAL CONNECT BY ROWNUM<=4 ) ,t2 AS ( ---- 集合t2模擬獨立取四次的動作，最終結(jié)果會有4*4*4*4=256行 SELECT ROWNUM id ---- 構(gòu)造唯一ID供下面拆分用 ,REPLACE(SYS_CONNECT_BY_PATH(rn,'@'),'@') path ---- 用一個特殊字符@來作分隔符, 并在最后用REPLACE把它去除 ,COUNT(*) OVER() cnt ---- 利用分析函數(shù)算出總行數(shù)并把它作為一個列返回 FROM t ---- 這個是有四行的集合 WHERE LEVEL=4 ---- 我們需要的僅僅是最后一層的結(jié)果。在PATH里面已經(jīng)包含了取四次的所有結(jié)果組合 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
......(其余結(jié)果略)
256 rows selected.
由此看到PATH列已經(jīng)包含了四次抽取的所有可能結(jié)果，每個結(jié)果都被賦予一個唯一的編號ID。
如果你好奇的話可以看看下一步的結(jié)果集t3:
WITH t AS ( SELECT ROWNUM rn -- 先構(gòu)造一個1,2,3,4的結(jié)果集，每個rn表示一種顏色 FROM DUAL CONNECT BY ROWNUM<=4 ) ,t2 AS ( ---- 集合t2模擬獨立取四次的動作，最終結(jié)果會有4*4*4*4=256行 SELECT ROWNUM id ---- 構(gòu)造唯一ID供下面拆分用 ,REPLACE(SYS_CONNECT_BY_PATH(rn,'@'),'@') path ---- 用一個特殊字符@來作分隔符, 并在最后用REPLACE把它去除 ,COUNT(*) OVER() cnt ---- 利用分析函數(shù)算出總行數(shù)并把它作為一個列返回 FROM t ---- 這個是有四行的集合 WHERE LEVEL=4 ---- 我們需要的僅僅是最后一層的結(jié)果。在PATH里面已經(jīng)包含了取四次的所有結(jié)果組合 CONNECT BY LEVEL<=4 ---- 沒有任何條件，前后都是獨立的 ) ,t3 AS ( ---- 集合t3把t2中的PATH包含的顏色組合拆開為四行 SELECT id,cnt,SUBSTR(PATH,rn,1) color FROM t2,t ---- 笛卡兒積，用于把t2中的一行變?yōu)樗男? ) 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
......(其余結(jié)果略)
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中包含不重復(fù)的顏色數(shù)目，放在HAVING中過濾了數(shù)目不為3的那些ID。
GROUP BY id,cnt 表示按照id來分組。因為所有行的cnt都是一樣的(都等于256)，我們在分組加入它并不會改變分組的結(jié)果，加入cnt的目的是為了在查詢中引用。
最后的連續(xù)兩層COUNT函數(shù)的意思是要把分組結(jié)果再聚合為一行，算出滿足條件的id的行數(shù)。除以cnt就得到了我們要的概率。
本例是一個在多行的結(jié)果集上進行無條件遍歷的例子，前面說過了要特別小心，因為沒有上下級關(guān)系，隨著層數(shù)遞增，數(shù)據(jù)量的增長十分可觀。
遞歸WITH寫法：
WITH T AS (
SELECT ROWNUM rn -- 還是先構(gòu)造一個1,2,3,4的結(jié)果集
FROM DUAL
CONNECT BY ROWNUM<=4
)
,t2(distinct_colors,lvl) AS ( --- 兩個列：所有不重復(fù)顏色，層次
SELECT '\'||rn,1 ---- 第一層就是最基礎(chǔ)的四種顏色的表
FROM t
UNION ALL
SELECT CASE WHEN INSTR(t2.distinct_colors||'\','\'||t.rn||'\')=0 --- 這個顏色沒有出現(xiàn)過
THEN t2.distinct_colors||'\'||t.rn --- 拼上去
ELSE t2.distinct_colors ---- 顏色已經(jīng)出現(xiàn)，保持原來的
END
,t2.lvl+1 --- 層數(shù)遞增
FROM t, t2
WHERE t2.lvl<4 --- 遞歸出口的條件：次數(shù)達到限制
)
SELECT COUNT(CASE WHEN LENGTH(distinct_colors) - LENGTH(REPLACE(distinct_colors,'\'))=3 THEN 1 END) --- 出現(xiàn)三個斜杠
/COUNT(*)
FROM t2
WHERE lvl=4 ---- 同CONNECT BY類似，我們只需觀察最后一層的數(shù)據(jù)，在這里面已經(jīng)包含了所有層次的顏色
;
在遞歸WITH子查詢t2中，我們看到它用了一個CASE表達式把以前沒出現(xiàn)過的顏色拼接到distinct_colors中。這個CASE是遞歸WITH的妙處，用SYS_CONNECT_BY_PATH沒辦法做到有條件的拼接。
而最后在計算顏色數(shù)的時候用了一個技巧，把顏色數(shù)轉(zhuǎn)換為斜杠的個數(shù)，因為我們構(gòu)造數(shù)據(jù)的時候每種顏色前面都帶一個斜杠。為了求出字符串中某字符出現(xiàn)的次數(shù)，我們用了這樣的辦法：
先求出字符串的總長度；
用REPLACE函數(shù)從串中去除這個字符，然后再求一次長度；
兩個長度之差就是被去除的字符個數(shù)。
CASE函數(shù)把出現(xiàn)滿足條件的標記置為1,不滿足則為NULL, 那么再套一個COUNT函數(shù)就能算出滿足條件的行數(shù)，因為NULL是不被COUNT計入的。
COUNT和CASE的嵌套使用，也是在聚合運算中常用的技巧。
這個顏色數(shù)的計算，我們也可以在遞歸的過程中進行有條件累加，這樣最后就可以直接使用：
WITH T AS (
SELECT ROWNUM rn -- 還是先構(gòu)造一個1,2,3,4的結(jié)果集
FROM DUAL
CONNECT BY ROWNUM<=4
)
,t2(distinct_colors,lvl,distinct_colors_cnt) AS ( --- 兩個列：所有不重復(fù)顏色，層次，不重復(fù)的顏色數(shù)
SELECT '\'||rn,1,1 ---- 第一層就是最基礎(chǔ)的四種顏色的表
FROM t
UNION ALL
SELECT CASE WHEN INSTR(t2.distinct_colors||'\','\'||t.rn||'\')=0 --- 這個顏色沒有出現(xiàn)過
THEN t2.distinct_colors||'\'||t.rn --- 拼上去
ELSE t2.distinct_colors ---- 顏色已經(jīng)出現(xiàn)，保持原來的
END
,t2.lvl+1 --- 層數(shù)遞增
,CASE WHEN INSTR(t2.distinct_colors||'\','\'||t.rn||'\')=0 --- 這個顏色沒有出現(xiàn)過
THEN t2.distinct_colors_cnt + 1 --- 顏色數(shù)累加
ELSE t2.distinct_colors_cnt ---- 顏色已經(jīng)出現(xiàn)，數(shù)目不變
END
FROM t, t2
WHERE t2.lvl<4 --- 遞歸出口的條件：次數(shù)達到限制
)
SELECT COUNT(CASE WHEN distinct_colors_cnt=3 THEN 1 END) --- 出現(xiàn)三個斜杠
/COUNT(*)
FROM t2
WHERE lvl=4 ---- 同CONNECT BY類似，我們只需觀察最后一層的數(shù)據(jù)，在這里面已經(jīng)包含了所有層次的顏色
;
例4:
構(gòu)造一個二階等差數(shù)列：這個數(shù)列的各項之差是一個等差數(shù)列
比如：1,3,6,10,15,21,...
用CONNECT BY:
SELECT LEVEL, SUM(LEVEL) OVER(ORDER BY LEVEL) n FROM DUAL CONNECT BY LEVEL<=10;
結(jié)果：
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.
因為只有一條路徑，所以用分析函數(shù)SUM很輕易做到了。
遞歸WITH寫法：
WITH t(lvl,n) AS ( SELECT 1,1 FROM DUAL --- 先構(gòu)造第一個 UNION ALL SELECT t.lvl+1, t.lvl+1+t.n ---- n的增幅本身是一個等差數(shù)列，即新的t.lvl FROM t ---- 沒有任何連接，因為不需要 WHERE t.lvl<10 ---- 找到10個就停止 ) SELECT * FROM T;
結(jié)果：
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:
構(gòu)造斐波那契數(shù)列: 指的是這樣一個數(shù)列, 從第三項開始，每一項都等于前兩項之和。
1,1,2,3,5,8,13,21,......
傳統(tǒng)的CONNECT BY方法做不出來，但是用10G以上所支持的MODEL可以輕松構(gòu)造：
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構(gòu)造最初的兩個，其余的則賦值為最近兩項之和 )
/
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 --- 先構(gòu)造第一個 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個數(shù)中取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 ( ---- 三個列：當(dāng)前節(jié)點值，路徑，層數(shù) SELECT rn,','||rn,1 FROM t ---- 先構(gòu)造錨點成員的基礎(chǔ)數(shù)據(jù)，就是上面生成的6行數(shù)據(jù)的集合 UNION ALL SELECT t.rn,t2.xmlpath||','||t.rn,t2.lvl+1 --- 把當(dāng)前節(jié)點拼接入路徑，層數(shù)則遞增 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
......(其余結(jié)果略)
60 rows selected.
和剛才的組合寫法相比，rn<PRIOR rn變成了NOCYCLE rn<>PRIOR rn, 這表示只要rn沒出現(xiàn)過就行，我們要的是所有的排列順序而不僅僅是降序。注意這里面的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不允許遍歷順序中出現(xiàn)循環(huán)。
在遞歸WITH中，NOCYCLE的寫法：
WITH t AS ( SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL<6 ) ,T2(rn,xmlpath,lvl) AS ( ---- 三個列：當(dāng)前節(jié)點值，路徑，層數(shù) SELECT rn,','||rn,1 FROM t ---- 先構(gòu)造錨點成員的基礎(chǔ)數(shù)據(jù)，就是上面生成的6行數(shù)據(jù)的集合 UNION ALL SELECT t.rn,t2.xmlpath||','||t.rn,t2.lvl+1 --- 把當(dāng)前節(jié)點拼接入路徑，層數(shù)則遞增 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';
結(jié)果：
XMLPA
以上所述是小編給大家介紹的Oracle 11GR2的遞歸WITH子查詢方法，希望對大家有所幫助，如果大家有任何疑問請給我留言，小編會及時回復(fù)大家的。在此也非常感謝大家對腳本之家網(wǎng)站的支持！
相關(guān)文章
這篇文章主要介紹了 Oracle 管理員賬號密碼忘記的快速解決方法,本文以oracle12c為例子，給大家介紹的非常詳細，具有一定的參考借鑒價值，需要的朋友可以參考下2020-02-02
Oracle存儲過程的編寫經(jīng)驗與優(yōu)化措施(分享)
本篇文章是對Oracle存儲過程的編寫經(jīng)驗與優(yōu)化措施進行了詳細的分析介紹，需要的朋友參考下2013-05-05
Oracle數(shù)據(jù)庫如何獲取當(dāng)前自然周,當(dāng)前周的起始和結(jié)束日期
Oracle數(shù)據(jù)庫如何獲取當(dāng)前自然周，當(dāng)前周的起始和結(jié)束日期問題，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2022-12-12
解決Oracle ORA-01017:invalid username/password:logon
這篇文章主要介紹了解決Oracle ORA-01017:invalid username/password:logon denied的問題,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-05-05
oracle 存儲過程返回 結(jié)果集 table形式的案例
這篇文章主要介紹了oracle 存儲過程返回 結(jié)果集 table形式的案例，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
Oracle中instr函數(shù)與substr函數(shù)及自制分割函數(shù)詳解
這篇文章主要介紹了Oracle中instr函數(shù)與substr函數(shù)以及自制分割函數(shù),大家都知道substr函數(shù)就是很簡單明了，就是個截取字符函數(shù)，本文通過實例代碼對這接個函數(shù)詳細介紹，需要的朋友可以參考下2023-07-07
Oracle 11g 數(shù)據(jù)庫的部署的圖文教程
這篇文章主要介紹了Oracle 11g 數(shù)據(jù)庫的部署,文中通過圖文示例代碼介紹的非常詳細，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2020-07-07
Oracle用戶密碼含有特殊字符導(dǎo)致無法登陸解決方法
在客戶端上使用sqlplus用普通用戶可以登錄，但是system以及sys用戶均無法登錄，提示ORA-12154: TNS: 無法解析指定的連接標識符，本文將提供詳細的解決方法，需要了解的朋友可以參考下2012-11-11
這里我們介紹的是 40+ 個非常有用的 Oracle 查詢語句，主要涵蓋了日期操作，獲取服務(wù)器信息，獲取執(zhí)行狀態(tài)，計算數(shù)據(jù)庫大小等等方面的查詢。這些是所有 Oracle 開發(fā)者都必備的技能，所以快快收藏吧2014-04-04
Windows10安裝Oracle19c數(shù)據(jù)庫詳細記錄(圖文詳解)
這篇文章主要介紹了Windows10安裝Oracle19c數(shù)據(jù)庫詳細記錄，文中給大家介紹了修改密碼的方法,本文通過實例代碼給大家介紹的非常詳細，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友可以參考下2020-05-05