主頁 > 知識(shí)庫 > Oracle 11GR2的遞歸WITH子查詢方法

Oracle 11GR2的遞歸WITH子查詢方法

下面給大家詳細(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è)簡(jiǎn)單例子，從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è)簡(jiǎn)潔優(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ì)了：

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ī)從布袋中取四次，每次取完都放回去。現(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)，我們?cè)诜纸M加入它并不會(huì)改變分組的結(jié)果，加入cnt的目的是為了在查詢中引用。
最后的連續(xù)兩層COUNT函數(shù)的意思是要把分組結(jié)果再聚合為一行，算出滿足條件的id的行數(shù)。除以cnt就得到了我們要的概率。

本例是一個(gè)在多行的結(jié)果集上進(jìn)行無條件遍歷的例子，前面說過了要特別小心，因?yàn)闆]有上下級(jí)關(guān)系，隨著層數(shù)遞增，數(shù)據(jù)量的增長(zhǎng)十分可觀。

遞歸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ù)，我們用了這樣的辦法：

先求出字符串的總長(zhǎng)度；

用REPLACE函數(shù)從串中去除這個(gè)字符，然后再求一次長(zhǎng)度；

兩個(gè)長(zhǎng)度之差就是被去除的字符個(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   /

14 rows selected.

不知道真用起來怎么樣，按我的想象可以比原來的SYS_CONNECT_BY_PATH多玩出很多新花樣，比如按路徑累加，更靈活的剪枝條件，

WITH子查詢也稱為CTE (Common Table Expression)，是ANSI SQL-99標(biāo)準(zhǔn)的一部分。ORACLE從9i開始引入WITH子查詢，把它被稱作SUBQUERY FACTORING(分解子查詢)。

我們先來看看這個(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]]...

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 ... (起始條件)

搜索子句(search_clause)和循環(huán)子句(cycle_clause)我們后面的例子中會(huì)見到。

下面我們就來看看遞歸WITH子查詢的用法實(shí)例。

例1:

先來一個(gè)簡(jiǎn)單例子，從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寫法：

（以下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é)果：

10 rows selected.

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ì)了：

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:

傳統(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

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)，我們?cè)诜纸M加入它并不會(huì)改變分組的結(jié)果，加入cnt的目的是為了在查詢中引用。

最后的連續(xù)兩層COUNT函數(shù)的意思是要把分組結(jié)果再聚合為一行，算出滿足條件的id的行數(shù)。除以cnt就得到了我們要的概率。

遞歸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)包含了所有層次的顏色
;

先求出字符串的總長(zhǎng)度；

用REPLACE函數(shù)從串中去除這個(gè)字符，然后再求一次長(zhǎng)度；

兩個(gè)長(zhǎng)度之差就是被去除的字符個(gè)數(shù)。

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é)果：

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é)果：

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

如果不寫這個(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í)例分析

標(biāo)簽：湘潭銅川蘭州衡水仙桃黃山湖南崇左

巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《Oracle 11GR2的遞歸WITH子查詢方法》，本文關(guān)鍵詞；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題，煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無關(guān)。