下面給大家詳細(xì)介紹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標(biāo)準(zhǔn)的一部分。ORACLE從9i開始引入WITH子查詢，把它被稱作SUBQUERY FACTORING(分解子查詢)。
WITH子查詢的作用類似于內(nèi)聯(lián)視圖(INLINE VIEW)。內(nèi)聯(lián)視圖的定義寫作SQL的FROM 后面，只能夠引用一次；而WITH子查詢需要在引用之前先定義，一旦定義了在整個(gè)查詢的后續(xù)部分就可以按名稱來反復(fù)引用，從這點(diǎn)來看又很像臨時(shí)表。
從版本11GR2開始，ORACLE支持遞歸的WITH, 即允許在WITH子查詢的定義中對(duì)自身引用。這不是什么新鮮事，其他數(shù)據(jù)庫如DB2, Firebird, Microsoft SQL Server, PostgreSQL 都先于ORACLE支持這一特性。但對(duì)于ORACLE用戶來說，這一遞歸特性還是很令人期待的，利用它可以輕易實(shí)現(xiàn)以往做不到的、或者很難做到的許多新功能。這一章我們就來探索這一令人興奮的新特性，并把它和以往的實(shí)現(xiàn)手段（主要是CONNECT BY層次查詢）作比較。
我們先來看看這個(gè)遞歸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]]...
①這是子查詢的名稱，和以往不同的是，必須在括號(hào)中把這個(gè)子查詢的所有列名寫出來。
②AS后面的subquery就是查詢語句，遞歸部分就寫在這里。
③遍歷順序子句，可以指定深度優(yōu)先或廣度優(yōu)先遍歷順序。
④循環(huán)子句，用于中止遍歷中出現(xiàn)的死循環(huán)。
⑤如果還有其他遞歸子查詢，定義同上。
subquery部分由兩個(gè)成員組成：anchor member(錨點(diǎn)成員) 和 recursive member(遞歸成員)。它們之間必須用union all聯(lián)合起來，anchor member 必須寫在recursive member前面。
anchor member用來定位遞歸的入口，錨點(diǎn)成員是一個(gè)SELECT語句，它不可以包含自身名稱(query_name)。這相當(dāng)于CONNECT BY查詢中的START WITH，典型寫法就是:
SELECT ... FROM 要遍歷的表 WHERE ... (起始條件)
遞歸成員也是一個(gè)SELECT語句，用于定義上下級(jí)的關(guān)系，它必須包含自身名稱(即query_name)，而且僅僅只能引用一次。遞歸正是體現(xiàn)在對(duì)于自身的引用。典型的做法就是把query_name和其他表(一般來說就是你要遍歷的表)做一個(gè)連接，連接條件表明了上下級(jí)的關(guān)系。必須注意，在這個(gè)query_name中，并不是截止目前為止的所有數(shù)據(jù)都是可見的，可見的只是上次遞歸新加入的最近的一層數(shù)據(jù)。對(duì)query_name列的引用相當(dāng)于CONNECT BY中的PRIOR操作符。當(dāng)找不到滿足條件的下級(jí)，遍歷就會(huì)停止；如果你還有其他的遞歸出口條件，也可以一起寫在WHERE中，當(dāng)WHERE不滿足時(shí)，遍歷就會(huì)停止，這就是在遍歷樹、圖時(shí)候的剪枝操作。越早停止則效率越高。
這個(gè)遞歸成員就是程序員發(fā)揮創(chuàng)造力的地方，以往在CONNECT BY中做不到的事情，比如沿路徑求和、求積等運(yùn)算，現(xiàn)在都輕而易舉。而SYS_CONNECT_BY_PATH也很容易用字符串拼接'||'來實(shí)現(xiàn)。
搜索子句(search_clause)和循環(huán)子句(cycle_clause)我們后面的例子中會(huì)見到。
下面我們就來看看遞歸WITH子查詢的用法實(shí)例。
例1:
先來一個(gè)簡單例子，從scott/tiger的emp表來查找上下級(jí)關(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í)，該員工已經(jīng)是最高級(jí)別。這是層次查詢的起點(diǎn)
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 ---- 先寫錨點(diǎn)查詢，用START WITH的條件
,1 AS the_level ---- 遞歸起點(diǎn)，第一層
,''||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ù)，因?yàn)槊總€(gè)路徑的根節(jié)點(diǎn)只有一個(gè)
FROM t, emp e ---- 典型寫法，把子查詢本身和要遍歷的表作一個(gè)連接
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 KINGJONES KING
7698 BLAKE MANAGER 7839 30 2 KINGBLAKE KING
7782 CLARK MANAGER 7839 10 2 KINGCLARK KING
7499 ALLEN SALESMAN 7698 30 3 KINGBLAKEALLEN KING
7521 WARD SALESMAN 7698 30 3 KINGBLAKEWARD KING
7654 MARTIN SALESMAN 7698 30 3 KINGBLAKEMARTIN KING
7788 SCOTT ANALYST 7566 20 3 KINGJONESSCOTT KING
7844 TURNER SALESMAN 7698 30 3 KINGBLAKETURNER KING
7900 JAMES CLERK 7698 30 3 KINGBLAKEJAMES KING
7902 FORD ANALYST 7566 20 3 KINGJONESFORD KING
7934 MILLER CLERK 7782 10 3 KINGCLARKMILLER KING
7369 SMITH CLERK 7902 20 4 KINGJONESFORDSMITH KING
7876 ADAMS CLERK 7788 20 4 KINGJONESSCOTTADAMS KING
14 rows selected.
從結(jié)果集的THE_LEVEL和PATH列可以清楚地看到數(shù)據(jù)是如何被一層一層疊加上去的。
例2:
構(gòu)造等差數(shù)列：
CONNECT BY寫法：
這是一個(gè)非常特殊的用法，因?yàn)闆]有上下級(jí)關(guān)系，只有遍歷的終止條件。像這類CONNECT BY我強(qiáng)烈推薦在只有一行的結(jié)果集上運(yùn)行（比如FROM DUAL, 比如從一個(gè)聚合后的子查詢），在多行的集合上運(yùn)行比較難以控制，頭腦必須很清醒。
（以下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.
這個(gè)簡潔優(yōu)雅的寫法最早由Mikito Harakiri(從名字看是個(gè)日本人)在asktom網(wǎng)站(http://asktom.oracle.com)發(fā)表，現(xiàn)在已經(jīng)風(fēng)靡全世界的ORACLE社區(qū)。在這個(gè)方法被發(fā)現(xiàn)之前，一般采用的是從一個(gè)大的集合（表或視圖）中獲取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是個(gè)很大的系統(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)造第一個(gè)
UNION ALL
SELECT t.n+1 ---- 遞增1
,t.n2+2 ---- 遞增2
,dt+1 ---- 下一日
,ADD_MONTHS(mon,1) ---- 下個(gè)月
FROM t ---- 沒有任何連接，因?yàn)椴恍枰?，所有?shù)據(jù)都可以從錨點(diǎn)成員中衍生出來
WHERE t.n10
)
SELECT * FROM T;
一切都按規(guī)矩來，竟然還是出錯(cuò)了：
,ADD_MONTHS(mon,1) ---- 下個(gè)月
*
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)換為日期型，計(jì)算后換回字符串型
,TO_CHAR(ADD_MONTHS(TO_DATE(t.mon,'YYYY-MM-DD'),1),'YYYY-MM-DD') ---- 計(jì)算下個(gè)月，方法同上
FROM t
WHERE t.n10
)
SELECT * FROM T;
我很驚奇地看到這個(gè)結(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)該會(huì)改正。
沒辦法，只好想其他招數(shù)繞過去:
WITH t(n) AS (
SELECT 1 FROM DUAL --- 先構(gòu)造第一個(gè)
UNION ALL
SELECT t.n+1 ---- 僅僅是整數(shù)序列
FROM t
WHERE t.n10
)
SELECT n
,n*2 n2
,DATE '2010-1-1'+n-1 dt ---- 在最終的查詢中進(jìn)行日期運(yùn)算
,ADD_MONTHS(DATE '2010-1-1', n-1) mon
FROM T;
這下子對(duì)了：
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.
看來對(duì)日期的運(yùn)算有BUG。解決辦法就是先構(gòu)造整數(shù)序列，然后在最終的查詢中再利用這個(gè)整數(shù)序列來構(gòu)造日期序列。
從一個(gè)單行結(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)造第一個(gè)
UNION ALL
SELECT t.n+1 ---- 遞增1
,t.cnt ---- 這個(gè)cnt列不做任何修改，從第一層得來
FROM t ---- 沒有任何連接，因?yàn)椴恍枰?
WHERE t.nt.cnt ---- 在這里看到cnt的作用，就是用于終止遍歷
)
SELECT * FROM t;
結(jié)果同上（略）。
例3:
獨(dú)立事件的排列組合：一個(gè)布袋中裝有數(shù)量相同的四種顏色的小球。隨機(jī)從布袋中取四次，每次取完都放回去?，F(xiàn)在問四次結(jié)果總顏色數(shù)等于3的概率是多少？
傳統(tǒng)的CONNECT BY寫法：
WITH t AS (
SELECT ROWNUM rn -- 先構(gòu)造一個(gè)1,2,3,4的結(jié)果集，每個(gè)rn表示一種顏色
FROM DUAL
CONNECT BY ROWNUM=4
)
,t2 AS ( ---- 集合t2模擬獨(dú)立取四次的動(dòng)作，最終結(jié)果會(huì)有4*4*4*4=256行
SELECT ROWNUM id ---- 構(gòu)造唯一ID供下面拆分用
,REPLACE(SYS_CONNECT_BY_PATH(rn,'@'),'@') path ---- 用一個(gè)特殊字符@來作分隔符, 并在最后用REPLACE把它去除
,COUNT(*) OVER() cnt ---- 利用分析函數(shù)算出總行數(shù)并把它作為一個(gè)列返回
FROM t ---- 這個(gè)是有四行的集合
WHERE LEVEL=4 ---- 我們需要的僅僅是最后一層的結(jié)果。在PATH里面已經(jīng)包含了取四次的所有結(jié)果組合
CONNECT BY LEVEL=4 ---- 沒有任何條件，前后都是獨(dú)立的
)
,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 --- 每一個(gè)id中包含三種顏色
;
結(jié)果：
PROB
----------
.5625
這個(gè)例子展示了CONNECT BY來模擬排列組合的技巧。每一層遍歷表示一次抽取的動(dòng)作，因?yàn)槊看味际峭耆?dú)立的，在CONNECT BY 里面僅僅限制了抽取次數(shù)（遍歷層數(shù)）而沒有其他條件。SYS_CONNECT_BY_PATH可以把截至當(dāng)前為止所訪問到的各層次的數(shù)據(jù)串起來，在LEVEL=N就包含了前N層的排列組合情況。你可以用這個(gè)查詢來看看中間生成的結(jié)果集t2：
WITH t AS (
SELECT ROWNUM rn -- 先構(gòu)造一個(gè)1,2,3,4的結(jié)果集，每個(gè)rn表示一種顏色
FROM DUAL
CONNECT BY ROWNUM=4
)
,t2 AS ( ---- 集合t2模擬獨(dú)立取四次的動(dòng)作，最終結(jié)果會(huì)有4*4*4*4=256行
SELECT ROWNUM id ---- 構(gòu)造唯一ID供下面拆分用
,REPLACE(SYS_CONNECT_BY_PATH(rn,'@'),'@') path ---- 用一個(gè)特殊字符@來作分隔符, 并在最后用REPLACE把它去除
,COUNT(*) OVER() cnt ---- 利用分析函數(shù)算出總行數(shù)并把它作為一個(gè)列返回
FROM t ---- 這個(gè)是有四行的集合
WHERE LEVEL=4 ---- 我們需要的僅僅是最后一層的結(jié)果。在PATH里面已經(jīng)包含了取四次的所有結(jié)果組合
CONNECT BY LEVEL=4 ---- 沒有任何條件，前后都是獨(dú)立的
)
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é)果，每個(gè)結(jié)果都被賦予一個(gè)唯一的編號(hào)ID。
如果你好奇的話可以看看下一步的結(jié)果集t3:
WITH t AS (
SELECT ROWNUM rn -- 先構(gòu)造一個(gè)1,2,3,4的結(jié)果集，每個(gè)rn表示一種顏色
FROM DUAL
CONNECT BY ROWNUM=4
)
,t2 AS ( ---- 集合t2模擬獨(dú)立取四次的動(dòng)作，最終結(jié)果會(huì)有4*4*4*4=256行
SELECT ROWNUM id ---- 構(gòu)造唯一ID供下面拆分用
,REPLACE(SYS_CONNECT_BY_PATH(rn,'@'),'@') path ---- 用一個(gè)特殊字符@來作分隔符, 并在最后用REPLACE把它去除
,COUNT(*) OVER() cnt ---- 利用分析函數(shù)算出總行數(shù)并把它作為一個(gè)列返回
FROM t ---- 這個(gè)是有四行的集合
WHERE LEVEL=4 ---- 我們需要的僅僅是最后一層的結(jié)果。在PATH里面已經(jīng)包含了取四次的所有結(jié)果組合
CONNECT BY LEVEL=4 ---- 沒有任何條件，前后都是獨(dú)立的
)
,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集合中的每一行都被拆成了四行，這是為了后面的聚合運(yùn)算。
最后看看算概率的主查詢：
SELECT COUNT(COUNT(*))/MAX(cnt) AS prob
FROM t3
GROUP BY id,cnt
HAVING COUNT(DISTINCT color)=3;
COUNT(DISTINCT color)可以算出每個(gè)ID中包含不重復(fù)的顏色數(shù)目，放在HAVING中過濾了數(shù)目不為3的那些ID。
GROUP BY id,cnt 表示按照id來分組。因?yàn)樗行械腸nt都是一樣的(都等于256)，我們在分組加入它并不會(huì)改變分組的結(jié)果，加入cnt的目的是為了在查詢中引用。
最后的連續(xù)兩層COUNT函數(shù)的意思是要把分組結(jié)果再聚合為一行，算出滿足條件的id的行數(shù)。除以cnt就得到了我們要的概率。
本例是一個(gè)在多行的結(jié)果集上進(jìn)行無條件遍歷的例子，前面說過了要特別小心，因?yàn)闆]有上下級(jí)關(guān)系，隨著層數(shù)遞增，數(shù)據(jù)量的增長十分可觀。
遞歸WITH寫法：
WITH T AS (
SELECT ROWNUM rn -- 還是先構(gòu)造一個(gè)1,2,3,4的結(jié)果集
FROM DUAL
CONNECT BY ROWNUM=4
)
,t2(distinct_colors,lvl) AS ( --- 兩個(gè)列：所有不重復(fù)顏色，層次
SELECT ''||rn,1 ---- 第一層就是最基礎(chǔ)的四種顏色的表
FROM t
UNION ALL
SELECT CASE WHEN INSTR(t2.distinct_colors||'\',''||t.rn||'\')=0 --- 這個(gè)顏色沒有出現(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.lvl4 --- 遞歸出口的條件：次數(shù)達(dá)到限制
)
SELECT COUNT(CASE WHEN LENGTH(distinct_colors) - LENGTH(REPLACE(distinct_colors,'\'))=3 THEN 1 END) --- 出現(xiàn)三個(gè)斜杠
/COUNT(*)
FROM t2
WHERE lvl=4 ---- 同CONNECT BY類似，我們只需觀察最后一層的數(shù)據(jù)，在這里面已經(jīng)包含了所有層次的顏色
;
在遞歸WITH子查詢t2中，我們看到它用了一個(gè)CASE表達(dá)式把以前沒出現(xiàn)過的顏色拼接到distinct_colors中。這個(gè)CASE是遞歸WITH的妙處，用SYS_CONNECT_BY_PATH沒辦法做到有條件的拼接。
而最后在計(jì)算顏色數(shù)的時(shí)候用了一個(gè)技巧，把顏色數(shù)轉(zhuǎn)換為斜杠的個(gè)數(shù)，因?yàn)槲覀儤?gòu)造數(shù)據(jù)的時(shí)候每種顏色前面都帶一個(gè)斜杠。為了求出字符串中某字符出現(xiàn)的次數(shù)，我們用了這樣的辦法：
先求出字符串的總長度；
用REPLACE函數(shù)從串中去除這個(gè)字符，然后再求一次長度；
兩個(gè)長度之差就是被去除的字符個(gè)數(shù)。
CASE函數(shù)把出現(xiàn)滿足條件的標(biāo)記置為1,不滿足則為NULL, 那么再套一個(gè)COUNT函數(shù)就能算出滿足條件的行數(shù)，因?yàn)镹ULL是不被COUNT計(jì)入的。
COUNT和CASE的嵌套使用，也是在聚合運(yùn)算中常用的技巧。
這個(gè)顏色數(shù)的計(jì)算，我們也可以在遞歸的過程中進(jìn)行有條件累加，這樣最后就可以直接使用：
WITH T AS (
SELECT ROWNUM rn -- 還是先構(gòu)造一個(gè)1,2,3,4的結(jié)果集
FROM DUAL
CONNECT BY ROWNUM=4
)
,t2(distinct_colors,lvl,distinct_colors_cnt) AS ( --- 兩個(gè)列：所有不重復(fù)顏色，層次，不重復(fù)的顏色數(shù)
SELECT ''||rn,1,1 ---- 第一層就是最基礎(chǔ)的四種顏色的表
FROM t
UNION ALL
SELECT CASE WHEN INSTR(t2.distinct_colors||'\',''||t.rn||'\')=0 --- 這個(gè)顏色沒有出現(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 --- 這個(gè)顏色沒有出現(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.lvl4 --- 遞歸出口的條件：次數(shù)達(dá)到限制
)
SELECT COUNT(CASE WHEN distinct_colors_cnt=3 THEN 1 END) --- 出現(xiàn)三個(gè)斜杠
/COUNT(*)
FROM t2
WHERE lvl=4 ---- 同CONNECT BY類似，我們只需觀察最后一層的數(shù)據(jù)，在這里面已經(jīng)包含了所有層次的顏色
;
例4:
構(gòu)造一個(gè)二階等差數(shù)列：這個(gè)數(shù)列的各項(xiàng)之差是一個(gè)等差數(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.
因?yàn)橹挥幸粭l路徑，所以用分析函數(shù)SUM很輕易做到了。
遞歸WITH寫法：
WITH t(lvl,n) AS (
SELECT 1,1 FROM DUAL --- 先構(gòu)造第一個(gè)
UNION ALL
SELECT t.lvl+1, t.lvl+1+t.n ---- n的增幅本身是一個(gè)等差數(shù)列，即新的t.lvl
FROM t ---- 沒有任何連接，因?yàn)椴恍枰?
WHERE t.lvl10 ---- 找到10個(gè)就停止
)
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ù)列: 指的是這樣一個(gè)數(shù)列, 從第三項(xiàng)開始，每一項(xiàng)都等于前兩項(xiàng)之和。
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)造最初的兩個(gè)，其余的則賦值為最近兩項(xiàng)之和
)
/
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)造第一個(gè)
UNION ALL
SELECT t.n+t.last_n, t.n, t.cnt+1 ---- 前兩項(xiàng)之和
FROM t ---- 沒有任何連接，因?yàn)椴恍枰?
WHERE t.cnt10 ---- 找到10個(gè)就停止
)
SELECT n FROM T;
N
----------
1
1
2
3
5
8
13
21
34
55
10 rows selected.
例6:
排列組合:
從5個(gè)數(shù)中取3個(gè)的所有組合C(3,5):
CONNECT BY寫法：
SELECT SYS_CONNECT_BY_PATH(rn, ',') xmlpath
FROM (SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL6)
WHERE LEVEL=3
CONNECT BY rnPRIOR rn AND LEVEL=3 ---- 強(qiáng)行按降序排序，這樣就排除了其他相同的、只是順序不同的組合
;
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 LEVEL6
)
,t2(rn,xmlpath,lvl) AS ( ---- 三個(gè)列：當(dāng)前節(jié)點(diǎn)值，路徑，層數(shù)
SELECT rn,','||rn,1 FROM t ---- 先構(gòu)造錨點(diǎn)成員的基礎(chǔ)數(shù)據(jù)，就是上面生成的6行數(shù)據(jù)的集合
UNION ALL
SELECT t.rn,t2.xmlpath||','||t.rn,t2.lvl+1 --- 把當(dāng)前節(jié)點(diǎn)拼接入路徑，層數(shù)則遞增
FROM t2, t
WHERE t2.rnt.rn AND t2.lvl3
)
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 LEVEL6)
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.
和剛才的組合寫法相比，rnPRIOR rn變成了NOCYCLE rn>PRIOR rn, 這表示只要rn沒出現(xiàn)過就行，我們要的是所有的排列順序而不僅僅是降序。注意這里面的NOCYCLE, 這個(gè)是10G上才有的。
如果不寫這個(gè)NOCYCLE會(huì)怎么樣？
SELECT SYS_CONNECT_BY_PATH(rn, ',') xmlpath
FROM (SELECT ROWNUM rn FROM DUAL CONNECT BY LEVEL6)
WHERE LEVEL=3
CONNECT BY rn>PRIOR rn AND LEVEL=3;
ERROR:
ORA-01436: CONNECT BY loop in user data
可以看到，這個(gè)NOCYCLE是很重要的，ORACLE不允許遍歷順序中出現(xiàn)循環(huán)。
在遞歸WITH中，NOCYCLE的寫法：
WITH t AS (
SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL6
)
,T2(rn,xmlpath,lvl) AS ( ---- 三個(gè)列：當(dāng)前節(jié)點(diǎn)值，路徑，層數(shù)
SELECT rn,','||rn,1 FROM t ---- 先構(gòu)造錨點(diǎn)成員的基礎(chǔ)數(shù)據(jù)，就是上面生成的6行數(shù)據(jù)的集合
UNION ALL
SELECT t.rn,t2.xmlpath||','||t.rn,t2.lvl+1 --- 把當(dāng)前節(jié)點(diǎn)拼接入路徑，層數(shù)則遞增
FROM t2, t
WHERE t2.rn>t.rn AND t2.lvl3
)
CYCLE rn SET cycle_flag TO 'Y' DEFAULT 'N' ---- 這個(gè)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標(biāo)準(zhǔn)的一部分。ORACLE從9i開始引入WITH子查詢，把它被稱作SUBQUERY FACTORING(分解子查詢)。
WITH子查詢的作用類似于內(nèi)聯(lián)視圖(INLINE VIEW)。內(nèi)聯(lián)視圖的定義寫作SQL的FROM 后面，只能夠引用一次；而WITH子查詢需要在引用之前先定義，一旦定義了在整個(gè)查詢的后續(xù)部分就可以按名稱來反復(fù)引用，從這點(diǎn)來看又很像臨時(shí)表。
從版本11GR2開始，ORACLE支持遞歸的WITH, 即允許在WITH子查詢的定義中對(duì)自身引用。這不是什么新鮮事，其他數(shù)據(jù)庫如DB2, Firebird, Microsoft SQL Server, PostgreSQL 都先于ORACLE支持這一特性。但對(duì)于ORACLE用戶來說，這一遞歸特性還是很令人期待的，利用它可以輕易實(shí)現(xiàn)以往做不到的、或者很難做到的許多新功能。這一章我們就來探索這一令人興奮的新特性，并把它和以往的實(shí)現(xiàn)手段（主要是CONNECT BY層次查詢）作比較。
我們先來看看這個(gè)遞歸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]]...
①這是子查詢的名稱，和以往不同的是，必須在括號(hào)中把這個(gè)子查詢的所有列名寫出來。
②AS后面的subquery就是查詢語句，遞歸部分就寫在這里。
③遍歷順序子句，可以指定深度優(yōu)先或廣度優(yōu)先遍歷順序。
④循環(huán)子句，用于中止遍歷中出現(xiàn)的死循環(huán)。
⑤如果還有其他遞歸子查詢，定義同上。
subquery部分由兩個(gè)成員組成：anchor member(錨點(diǎn)成員) 和 recursive member(遞歸成員)。它們之間必須用union all聯(lián)合起來，anchor member 必須寫在recursive member前面。
anchor member用來定位遞歸的入口，錨點(diǎn)成員是一個(gè)SELECT語句，它不可以包含自身名稱(query_name)。這相當(dāng)于CONNECT BY查詢中的START WITH，典型寫法就是:
SELECT ... FROM 要遍歷的表 WHERE ... (起始條件)
遞歸成員也是一個(gè)SELECT語句，用于定義上下級(jí)的關(guān)系，它必須包含自身名稱(即query_name)，而且僅僅只能引用一次。遞歸正是體現(xiàn)在對(duì)于自身的引用。典型的做法就是把query_name和其他表(一般來說就是你要遍歷的表)做一個(gè)連接，連接條件表明了上下級(jí)的關(guān)系。必須注意，在這個(gè)query_name中，并不是截止目前為止的所有數(shù)據(jù)都是可見的，可見的只是上次遞歸新加入的最近的一層數(shù)據(jù)。對(duì)query_name列的引用相當(dāng)于CONNECT BY中的PRIOR操作符。當(dāng)找不到滿足條件的下級(jí)，遍歷就會(huì)停止；如果你還有其他的遞歸出口條件，也可以一起寫在WHERE中，當(dāng)WHERE不滿足時(shí)，遍歷就會(huì)停止，這就是在遍歷樹、圖時(shí)候的剪枝操作。越早停止則效率越高。
這個(gè)遞歸成員就是程序員發(fā)揮創(chuàng)造力的地方，以往在CONNECT BY中做不到的事情，比如沿路徑求和、求積等運(yùn)算，現(xiàn)在都輕而易舉。而SYS_CONNECT_BY_PATH也很容易用字符串拼接'||'來實(shí)現(xiàn)。
搜索子句(search_clause)和循環(huán)子句(cycle_clause)我們后面的例子中會(huì)見到。
下面我們就來看看遞歸WITH子查詢的用法實(shí)例。
例1:
先來一個(gè)簡單例子，從scott/tiger的emp表來查找上下級(jí)關(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í)，該員工已經(jīng)是最高級(jí)別。這是層次查詢的起點(diǎn)
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 ---- 先寫錨點(diǎn)查詢，用START WITH的條件
,1 AS the_level ---- 遞歸起點(diǎn)，第一層
,'\'||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ù)，因?yàn)槊總€(gè)路徑的根節(jié)點(diǎn)只有一個(gè)
FROM t, emp e ---- 典型寫法，把子查詢本身和要遍歷的表作一個(gè)連接
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寫法：
這是一個(gè)非常特殊的用法，因?yàn)闆]有上下級(jí)關(guān)系，只有遍歷的終止條件。像這類CONNECT BY我強(qiáng)烈推薦在只有一行的結(jié)果集上運(yùn)行（比如FROM DUAL, 比如從一個(gè)聚合后的子查詢），在多行的集合上運(yùn)行比較難以控制，頭腦必須很清醒。
（以下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.
這個(gè)簡潔優(yōu)雅的寫法最早由Mikito Harakiri(從名字看是個(gè)日本人)在asktom網(wǎng)站(http://asktom.oracle.com)發(fā)表，現(xiàn)在已經(jīng)風(fēng)靡全世界的ORACLE社區(qū)。在這個(gè)方法被發(fā)現(xiàn)之前，一般采用的是從一個(gè)大的集合（表或視圖）中獲取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是個(gè)很大的系統(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)造第一個(gè)
UNION ALL
SELECT t.n+1 ---- 遞增1
,t.n2+2 ---- 遞增2
,dt+1 ---- 下一日
,ADD_MONTHS(mon,1) ---- 下個(gè)月
FROM t ---- 沒有任何連接，因?yàn)椴恍枰?，所有?shù)據(jù)都可以從錨點(diǎn)成員中衍生出來
WHERE t.n10
)
SELECT * FROM T;
一切都按規(guī)矩來，竟然還是出錯(cuò)了：
,ADD_MONTHS(mon,1) ---- 下個(gè)月
*
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)換為日期型，計(jì)算后換回字符串型
,TO_CHAR(ADD_MONTHS(TO_DATE(t.mon,'YYYY-MM-DD'),1),'YYYY-MM-DD') ---- 計(jì)算下個(gè)月，方法同上
FROM t
WHERE t.n10
)
SELECT * FROM T;
我很驚奇地看到這個(gè)結(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)該會(huì)改正。
沒辦法，只好想其他招數(shù)繞過去:
WITH t(n) AS (
SELECT 1 FROM DUAL --- 先構(gòu)造第一個(gè)
UNION ALL
SELECT t.n+1 ---- 僅僅是整數(shù)序列
FROM t
WHERE t.n10
)
SELECT n
,n*2 n2
,DATE '2010-1-1'+n-1 dt ---- 在最終的查詢中進(jìn)行日期運(yùn)算
,ADD_MONTHS(DATE '2010-1-1', n-1) mon
FROM T;
這下子對(duì)了：
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.
看來對(duì)日期的運(yùn)算有BUG。解決辦法就是先構(gòu)造整數(shù)序列，然后在最終的查詢中再利用這個(gè)整數(shù)序列來構(gòu)造日期序列。
從一個(gè)單行結(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)造第一個(gè)
UNION ALL
SELECT t.n+1 ---- 遞增1
,t.cnt ---- 這個(gè)cnt列不做任何修改，從第一層得來
FROM t ---- 沒有任何連接，因?yàn)椴恍枰?
WHERE t.nt.cnt ---- 在這里看到cnt的作用，就是用于終止遍歷
)
SELECT * FROM t;
結(jié)果同上（略）。
例3:
獨(dú)立事件的排列組合：一個(gè)布袋中裝有數(shù)量相同的四種顏色的小球。隨機(jī)從布袋中取四次，每次取完都放回去?，F(xiàn)在問四次結(jié)果總顏色數(shù)等于3的概率是多少？
傳統(tǒng)的CONNECT BY寫法：
WITH t AS (
SELECT ROWNUM rn -- 先構(gòu)造一個(gè)1,2,3,4的結(jié)果集，每個(gè)rn表示一種顏色
FROM DUAL
CONNECT BY ROWNUM=4
)
,t2 AS ( ---- 集合t2模擬獨(dú)立取四次的動(dòng)作，最終結(jié)果會(huì)有4*4*4*4=256行
SELECT ROWNUM id ---- 構(gòu)造唯一ID供下面拆分用
,REPLACE(SYS_CONNECT_BY_PATH(rn,'@'),'@') path ---- 用一個(gè)特殊字符@來作分隔符, 并在最后用REPLACE把它去除
,COUNT(*) OVER() cnt ---- 利用分析函數(shù)算出總行數(shù)并把它作為一個(gè)列返回
FROM t ---- 這個(gè)是有四行的集合
WHERE LEVEL=4 ---- 我們需要的僅僅是最后一層的結(jié)果。在PATH里面已經(jīng)包含了取四次的所有結(jié)果組合
CONNECT BY LEVEL=4 ---- 沒有任何條件，前后都是獨(dú)立的
)
,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 --- 每一個(gè)id中包含三種顏色
;
結(jié)果：
PROB
----------
.5625
這個(gè)例子展示了CONNECT BY來模擬排列組合的技巧。每一層遍歷表示一次抽取的動(dòng)作，因?yàn)槊看味际峭耆?dú)立的，在CONNECT BY 里面僅僅限制了抽取次數(shù)（遍歷層數(shù)）而沒有其他條件。SYS_CONNECT_BY_PATH可以把截至當(dāng)前為止所訪問到的各層次的數(shù)據(jù)串起來，在LEVEL=N就包含了前N層的排列組合情況。你可以用這個(gè)查詢來看看中間生成的結(jié)果集t2：
WITH t AS (
SELECT ROWNUM rn -- 先構(gòu)造一個(gè)1,2,3,4的結(jié)果集，每個(gè)rn表示一種顏色
FROM DUAL
CONNECT BY ROWNUM=4
)
,t2 AS ( ---- 集合t2模擬獨(dú)立取四次的動(dòng)作，最終結(jié)果會(huì)有4*4*4*4=256行
SELECT ROWNUM id ---- 構(gòu)造唯一ID供下面拆分用
,REPLACE(SYS_CONNECT_BY_PATH(rn,'@'),'@') path ---- 用一個(gè)特殊字符@來作分隔符, 并在最后用REPLACE把它去除
,COUNT(*) OVER() cnt ---- 利用分析函數(shù)算出總行數(shù)并把它作為一個(gè)列返回
FROM t ---- 這個(gè)是有四行的集合
WHERE LEVEL=4 ---- 我們需要的僅僅是最后一層的結(jié)果。在PATH里面已經(jīng)包含了取四次的所有結(jié)果組合
CONNECT BY LEVEL=4 ---- 沒有任何條件，前后都是獨(dú)立的
)
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é)果，每個(gè)結(jié)果都被賦予一個(gè)唯一的編號(hào)ID。
如果你好奇的話可以看看下一步的結(jié)果集t3:
WITH t AS (
SELECT ROWNUM rn -- 先構(gòu)造一個(gè)1,2,3,4的結(jié)果集，每個(gè)rn表示一種顏色
FROM DUAL
CONNECT BY ROWNUM=4
)
,t2 AS ( ---- 集合t2模擬獨(dú)立取四次的動(dòng)作，最終結(jié)果會(huì)有4*4*4*4=256行
SELECT ROWNUM id ---- 構(gòu)造唯一ID供下面拆分用
,REPLACE(SYS_CONNECT_BY_PATH(rn,'@'),'@') path ---- 用一個(gè)特殊字符@來作分隔符, 并在最后用REPLACE把它去除
,COUNT(*) OVER() cnt ---- 利用分析函數(shù)算出總行數(shù)并把它作為一個(gè)列返回
FROM t ---- 這個(gè)是有四行的集合
WHERE LEVEL=4 ---- 我們需要的僅僅是最后一層的結(jié)果。在PATH里面已經(jīng)包含了取四次的所有結(jié)果組合
CONNECT BY LEVEL=4 ---- 沒有任何條件，前后都是獨(dú)立的
)
,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集合中的每一行都被拆成了四行，這是為了后面的聚合運(yùn)算。
最后看看算概率的主查詢：
SELECT COUNT(COUNT(*))/MAX(cnt) AS prob
FROM t3
GROUP BY id,cnt
HAVING COUNT(DISTINCT color)=3;
COUNT(DISTINCT color)可以算出每個(gè)ID中包含不重復(fù)的顏色數(shù)目，放在HAVING中過濾了數(shù)目不為3的那些ID。
GROUP BY id,cnt 表示按照id來分組。因?yàn)樗行械腸nt都是一樣的(都等于256)，我們在分組加入它并不會(huì)改變分組的結(jié)果，加入cnt的目的是為了在查詢中引用。
最后的連續(xù)兩層COUNT函數(shù)的意思是要把分組結(jié)果再聚合為一行，算出滿足條件的id的行數(shù)。除以cnt就得到了我們要的概率。
本例是一個(gè)在多行的結(jié)果集上進(jìn)行無條件遍歷的例子，前面說過了要特別小心，因?yàn)闆]有上下級(jí)關(guān)系，隨著層數(shù)遞增，數(shù)據(jù)量的增長十分可觀。
遞歸WITH寫法：
WITH T AS (
SELECT ROWNUM rn -- 還是先構(gòu)造一個(gè)1,2,3,4的結(jié)果集
FROM DUAL
CONNECT BY ROWNUM=4
)
,t2(distinct_colors,lvl) AS ( --- 兩個(gè)列：所有不重復(fù)顏色，層次
SELECT '\'||rn,1 ---- 第一層就是最基礎(chǔ)的四種顏色的表
FROM t
UNION ALL
SELECT CASE WHEN INSTR(t2.distinct_colors||'','\'||t.rn||'')=0 --- 這個(gè)顏色沒有出現(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.lvl4 --- 遞歸出口的條件：次數(shù)達(dá)到限制
)
SELECT COUNT(CASE WHEN LENGTH(distinct_colors) - LENGTH(REPLACE(distinct_colors,''))=3 THEN 1 END) --- 出現(xiàn)三個(gè)斜杠
/COUNT(*)
FROM t2
WHERE lvl=4 ---- 同CONNECT BY類似，我們只需觀察最后一層的數(shù)據(jù)，在這里面已經(jīng)包含了所有層次的顏色
;
在遞歸WITH子查詢t2中，我們看到它用了一個(gè)CASE表達(dá)式把以前沒出現(xiàn)過的顏色拼接到distinct_colors中。這個(gè)CASE是遞歸WITH的妙處，用SYS_CONNECT_BY_PATH沒辦法做到有條件的拼接。
而最后在計(jì)算顏色數(shù)的時(shí)候用了一個(gè)技巧，把顏色數(shù)轉(zhuǎn)換為斜杠的個(gè)數(shù)，因?yàn)槲覀儤?gòu)造數(shù)據(jù)的時(shí)候每種顏色前面都帶一個(gè)斜杠。為了求出字符串中某字符出現(xiàn)的次數(shù)，我們用了這樣的辦法：
先求出字符串的總長度；
用REPLACE函數(shù)從串中去除這個(gè)字符，然后再求一次長度；
兩個(gè)長度之差就是被去除的字符個(gè)數(shù)。
CASE函數(shù)把出現(xiàn)滿足條件的標(biāo)記置為1,不滿足則為NULL, 那么再套一個(gè)COUNT函數(shù)就能算出滿足條件的行數(shù)，因?yàn)镹ULL是不被COUNT計(jì)入的。
COUNT和CASE的嵌套使用，也是在聚合運(yùn)算中常用的技巧。
這個(gè)顏色數(shù)的計(jì)算，我們也可以在遞歸的過程中進(jìn)行有條件累加，這樣最后就可以直接使用：
WITH T AS (
SELECT ROWNUM rn -- 還是先構(gòu)造一個(gè)1,2,3,4的結(jié)果集
FROM DUAL
CONNECT BY ROWNUM=4
)
,t2(distinct_colors,lvl,distinct_colors_cnt) AS ( --- 兩個(gè)列：所有不重復(fù)顏色，層次，不重復(fù)的顏色數(shù)
SELECT '\'||rn,1,1 ---- 第一層就是最基礎(chǔ)的四種顏色的表
FROM t
UNION ALL
SELECT CASE WHEN INSTR(t2.distinct_colors||'','\'||t.rn||'')=0 --- 這個(gè)顏色沒有出現(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 --- 這個(gè)顏色沒有出現(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.lvl4 --- 遞歸出口的條件：次數(shù)達(dá)到限制
)
SELECT COUNT(CASE WHEN distinct_colors_cnt=3 THEN 1 END) --- 出現(xiàn)三個(gè)斜杠
/COUNT(*)
FROM t2
WHERE lvl=4 ---- 同CONNECT BY類似，我們只需觀察最后一層的數(shù)據(jù)，在這里面已經(jīng)包含了所有層次的顏色
;
例4:
構(gòu)造一個(gè)二階等差數(shù)列：這個(gè)數(shù)列的各項(xiàng)之差是一個(gè)等差數(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.
因?yàn)橹挥幸粭l路徑，所以用分析函數(shù)SUM很輕易做到了。
遞歸WITH寫法：
WITH t(lvl,n) AS (
SELECT 1,1 FROM DUAL --- 先構(gòu)造第一個(gè)
UNION ALL
SELECT t.lvl+1, t.lvl+1+t.n ---- n的增幅本身是一個(gè)等差數(shù)列，即新的t.lvl
FROM t ---- 沒有任何連接，因?yàn)椴恍枰?
WHERE t.lvl10 ---- 找到10個(gè)就停止
)
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ù)列: 指的是這樣一個(gè)數(shù)列, 從第三項(xiàng)開始，每一項(xiàng)都等于前兩項(xiàng)之和。
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)造最初的兩個(gè)，其余的則賦值為最近兩項(xiàng)之和
)
/
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)造第一個(gè)
UNION ALL
SELECT t.n+t.last_n, t.n, t.cnt+1 ---- 前兩項(xiàng)之和
FROM t ---- 沒有任何連接，因?yàn)椴恍枰?
WHERE t.cnt10 ---- 找到10個(gè)就停止
)
SELECT n FROM T;
N
----------
1
1
2
3
5
8
13
21
34
55
10 rows selected.
例6:
排列組合:
從5個(gè)數(shù)中取3個(gè)的所有組合C(3,5):
CONNECT BY寫法：
SELECT SYS_CONNECT_BY_PATH(rn, ',') xmlpath
FROM (SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL6)
WHERE LEVEL=3
CONNECT BY rnPRIOR rn AND LEVEL=3 ---- 強(qiáng)行按降序排序，這樣就排除了其他相同的、只是順序不同的組合
;
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 LEVEL6
)
,t2(rn,xmlpath,lvl) AS ( ---- 三個(gè)列：當(dāng)前節(jié)點(diǎn)值，路徑，層數(shù)
SELECT rn,','||rn,1 FROM t ---- 先構(gòu)造錨點(diǎn)成員的基礎(chǔ)數(shù)據(jù)，就是上面生成的6行數(shù)據(jù)的集合
UNION ALL
SELECT t.rn,t2.xmlpath||','||t.rn,t2.lvl+1 --- 把當(dāng)前節(jié)點(diǎn)拼接入路徑，層數(shù)則遞增
FROM t2, t
WHERE t2.rnt.rn AND t2.lvl3
)
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 LEVEL6)
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.
和剛才的組合寫法相比，rnPRIOR rn變成了NOCYCLE rn>PRIOR rn, 這表示只要rn沒出現(xiàn)過就行，我們要的是所有的排列順序而不僅僅是降序。注意這里面的NOCYCLE, 這個(gè)是10G上才有的。
如果不寫這個(gè)NOCYCLE會(huì)怎么樣？
SELECT SYS_CONNECT_BY_PATH(rn, ',') xmlpath
FROM (SELECT ROWNUM rn FROM DUAL CONNECT BY LEVEL6)
WHERE LEVEL=3
CONNECT BY rn>PRIOR rn AND LEVEL=3;
ERROR:
ORA-01436: CONNECT BY loop in user data
可以看到，這個(gè)NOCYCLE是很重要的，ORACLE不允許遍歷順序中出現(xiàn)循環(huán)。
在遞歸WITH中，NOCYCLE的寫法：
WITH t AS (
SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL6
)
,T2(rn,xmlpath,lvl) AS ( ---- 三個(gè)列：當(dāng)前節(jié)點(diǎn)值，路徑，層數(shù)
SELECT rn,','||rn,1 FROM t ---- 先構(gòu)造錨點(diǎn)成員的基礎(chǔ)數(shù)據(jù)，就是上面生成的6行數(shù)據(jù)的集合
UNION ALL
SELECT t.rn,t2.xmlpath||','||t.rn,t2.lvl+1 --- 把當(dāng)前節(jié)點(diǎn)拼接入路徑，層數(shù)則遞增
FROM t2, t
WHERE t2.rn>t.rn AND t2.lvl3
)
CYCLE rn SET cycle_flag TO 'Y' DEFAULT 'N' ---- 這個(gè)cycle_flag是自己定義的偽列名和值，可以起到CONNECT_BY_ISCYCLE同樣的作用
SELECT xmlpath FROM t2 WHERE lvl=3 AND cycle_flag='N';
結(jié)果：
XMLPA
以上所述是小編給大家介紹的Oracle 11GR2的遞歸WITH子查詢方法，希望對(duì)大家有所幫助，如果大家有任何疑問請(qǐng)給我留言，小編會(huì)及時(shí)回復(fù)大家的。在此也非常感謝大家對(duì)腳本之家網(wǎng)站的支持！
您可能感興趣的文章:- Oracle基礎(chǔ)學(xué)習(xí)之子查詢
- Oracle數(shù)據(jù)庫中基本的查詢優(yōu)化與子查詢優(yōu)化講解
- Oracle通過遞歸查詢父子兄弟節(jié)點(diǎn)方法示例
- 一個(gè)oracle+PHP的查詢的例子
- oracle基本查詢用法入門示例
- oracle 查詢表名以及表的列名
- oracle查詢語句大全(oracle 基本命令大全一)
- oracle數(shù)據(jù)庫常用的99條查詢語句
- ORACLE查詢刪除重復(fù)記錄三種方法
- oracle常用sql查詢語句部分集合(圖文)
- oracle基本查詢操作子查詢用法實(shí)例分析
