|
WITH子查詢也稱(chēng)為CTE (Common Table Expression)，是ANSI SQL-99標(biāo)準(zhǔn)的一部分。ORACLE從9i開(kāi)始引入WITH子查詢，把它被稱(chēng)作SUBQUERY FACTORING(分解子查詢)。 WITH子查詢的作用類(lèi)似于內(nèi)聯(lián)視圖(INLINE VIEW)。內(nèi)聯(lián)視圖的定義寫(xiě)作SQL的FROM 后面，只能夠引用一次；而WITH子查詢需要在引用之前先定義，一旦定義了在整個(gè)查詢的后續(xù)部分就可以按名稱(chēng)來(lái)反復(fù)引用，從這點(diǎn)來(lái)看又很像臨時(shí)表。 從版本11GR2開(kāi)始，ORACLE支持遞歸的WITH, 即允許在WITH子查詢的定義中對(duì)自身引用。這不是什么新鮮事，其他數(shù)據(jù)庫(kù)如DB2, Firebird, Microsoft SQL Server, PostgreSQL 都先于ORACLE支持這一特性。但對(duì)于ORACLE用戶來(lái)說(shuō)，這一遞歸特性還是很令人期待的，利用它可以輕易實(shí)現(xiàn)以往做不到的、或者很難做到的許多新功能。這一章我們就來(lái)探索這一令人興奮的新特性，并把它和以往的實(shí)現(xiàn)手段（主要是CONNECT BY層次查詢）作比較。 我們先來(lái)看看這個(gè)遞歸WITH子查詢的語(yǔ)法： 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]]... ①這是子查詢的名稱(chēng)，和以往不同的是，必須在括號(hào)中把這個(gè)子查詢的所有列名寫(xiě)出來(lái)。 ②AS后面的subquery就是查詢語(yǔ)句，遞歸部分就寫(xiě)在這里。 ③遍歷順序子句，可以指定深度優(yōu)先或廣度優(yōu)先遍歷順序。 ④循環(huán)子句，用于中止遍歷中出現(xiàn)的死循環(huán)。 ⑤如果還有其他遞歸子查詢，定義同上。 subquery部分由兩個(gè)成員組成：anchor member(錨點(diǎn)成員) 和 recursive member(遞歸成員)。它們之間必須用union all聯(lián)合起來(lái)，anchor member 必須寫(xiě)在recursive member前面。 anchor member用來(lái)定位遞歸的入口，錨點(diǎn)成員是一個(gè)SELECT語(yǔ)句，它不可以包含自身名稱(chēng)(query_name)。這相當(dāng)于CONNECT BY查詢中的START WITH，典型寫(xiě)法就是: SELECT ... FROM 要遍歷的表 WHERE ... (起始條件) 遞歸成員也是一個(gè)SELECT語(yǔ)句，用于定義上下級(jí)的關(guān)系，它必須包含自身名稱(chēng)(即query_name)，而且僅僅只能引用一次。遞歸正是體現(xiàn)在對(duì)于自身的引用。典型的做法就是把query_name和其他表(一般來(lái)說(shuō)就是你要遍歷的表)做一個(gè)連接，連接條件表明了上下級(jí)的關(guān)系。必須注意，在這個(gè)query_name中，并不是截止目前為止的所有數(shù)據(jù)都是可見(jiàn)的，可見(jiàn)的只是上次遞歸新加入的最近的一層數(shù)據(jù)。對(duì)query_name列的引用相當(dāng)于CONNECT BY中的PRIOR操作符。當(dāng)找不到滿足條件的下級(jí)，遍歷就會(huì)停止；如果你還有其他的遞歸出口條件，也可以一起寫(xiě)在WHERE中，當(dāng)WHERE不滿足時(shí)，遍歷就會(huì)停止，這就是在遍歷樹(shù)、圖時(shí)候的剪枝操作。越早停止則效率越高。 這個(gè)遞歸成員就是程序員發(fā)揮創(chuàng)造力的地方，以往在CONNECT BY中做不到的事情，比如沿路徑求和、求積等運(yùn)算，現(xiàn)在都輕而易舉。而SYS_CONNECT_BY_PATH也很容易用字符串拼接'||'來(lái)實(shí)現(xiàn)。 搜索子句(search_clause)和循環(huán)子句(cycle_clause)我們后面的例子中會(huì)見(jiàn)到。 下面我們就來(lái)看看遞歸WITH子查詢的用法實(shí)例。 例1: 先來(lái)一個(gè)簡(jiǎn)單例子，從scott/tiger的emp表來(lái)查找上下級(jí)關(guān)系： 傳統(tǒng)的CONNECT BY寫(xiě)法： 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列為空，表示沒(méi)有上級(jí)，該員工已經(jīng)是最高級(jí)別。這是層次查詢的起點(diǎn) CONNECT BY PRIOR empno= mgr; 新的遞歸WITH寫(xiě)法: WITH T(empno, ename, job, mgr, deptno, the_level, path,top_manager) AS ( ---- 必須把結(jié)構(gòu)寫(xiě)出來(lái) SELECT empno, ename, job, mgr, deptno ---- 先寫(xiě)錨點(diǎn)查詢，用START WITH的條件 ,1 AS the_level ---- 遞歸起點(diǎn)，第一層 ,'\'||ename ---- 路徑的第一截 ,ename AS top_manager ---- 原來(lái)的CONNECT_BY_ROOT FROM EMP WHERE mgr IS NULL ---- 原來(lái)的START WITH條件 UNION ALL ---- 下面是遞歸部分 SELECT e.empno, e.ename, e.job, e.mgr, e.deptno ---- 要加入的新一層數(shù)據(jù)，來(lái)自要遍歷的emp表 ,1 + t.the_level ---- 遞歸層次，在原來(lái)的基礎(chǔ)上加1。這相當(dāng)于CONNECT BY查詢中的LEVEL偽列 ,t.path||'\'||e.ename ---- 把新的一截路徑拼上去 ,t.top_manager ---- 直接繼承原來(lái)的數(shù)據(jù)，因?yàn)槊總€(gè)路徑的根節(jié)點(diǎn)只有一個(gè) FROM t, emp e ---- 典型寫(xiě)法，把子查詢本身和要遍歷的表作一個(gè)連接 WHERE t.empno = e.mgr ---- 原來(lái)的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寫(xiě)法： 這是一個(gè)非常特殊的用法，因?yàn)闆](méi)有上下級(jí)關(guān)系，只有遍歷的終止條件。像這類(lèi)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)雅的寫(xiě)法最早由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的寫(xiě)法： 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 ---- 沒(méi)有任何連接，因?yàn)椴恍枰袛?shù)據(jù)都可以從錨點(diǎn)成員中衍生出來(lái) WHERE t.n<10 ) SELECT * FROM T; 一切都按規(guī)矩來(lái)，竟然還是出錯(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 ---- 用字符串來(lái)表示日期 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.n<10 ) 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ì)改正。 沒(méi)辦法，只好想其他招數(shù)繞過(guò)去: WITH t(n) AS ( SELECT 1 FROM DUAL --- 先構(gòu)造第一個(gè) 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 ---- 在最終的查詢中進(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. 看來(lái)對(duì)日期的運(yùn)算有BUG。解決辦法就是先構(gòu)造整數(shù)序列，然后在最終的查詢中再利用這個(gè)整數(shù)序列來(lái)構(gòu)造日期序列。 從一個(gè)單行結(jié)果集CONNECT BY的例子: SELECT ROWNUM rn,cnt FROM (SELECT COUNT(*) cnt FROM emp) ---- 經(jīng)過(guò)聚合的只有一行的結(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寫(xiě)法： 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列不做任何修改，從第一層得來(lái) FROM t ---- 沒(méi)有任何連接，因?yàn)椴恍枰?br> WHERE t.n<t.cnt ---- 在這里看到cnt的作用，就是用于終止遍歷 ) SELECT * FROM t; 結(jié)果同上（略）。 例3: 獨(dú)立事件的排列組合：一個(gè)布袋中裝有數(shù)量相同的四種顏色的小球。隨機(jī)從布袋中取四次，每次取完都放回去?，F(xiàn)在問(wèn)四次結(jié)果總顏色數(shù)等于3的概率是多少？ 傳統(tǒng)的CONNECT BY寫(xiě)法： 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è)特殊字符@來(lái)作分隔符, 并在最后用REPLACE把它去除 ,COUNT(*) OVER() cnt ---- 利用分析函數(shù)算出總行數(shù)并把它作為一個(gè)列返回 FROM t ---- 這個(gè)是有四行的集合 WHERE LEVEL=4 ---- 我們需要的僅僅是最后一層的結(jié)果。在PATH里面已經(jīng)包含了取四次的所有結(jié)果組合 CONNECT BY LEVEL<=4 ---- 沒(méi)有任何條件，前后都是獨(dú)立的 ) ,t3 AS ( ---- 集合t3把t2中的PATH包含的顏色組合拆開(kāi)為四行 SELECT id,cnt,SUBSTR(PATH,rn,1) color FROM t2,t ---- 笛卡兒積，用于把t2中的一行變?yōu)樗男?br> ) 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來(lái)模擬排列組合的技巧。每一層遍歷表示一次抽取的動(dòng)作，因?yàn)槊看味际峭耆?dú)立的，在CONNECT BY 里面僅僅限制了抽取次數(shù)（遍歷層數(shù)）而沒(méi)有其他條件。SYS_CONNECT_BY_PATH可以把截至當(dāng)前為止所訪問(wèn)到的各層次的數(shù)據(jù)串起來(lái)，在LEVEL=N就包含了前N層的排列組合情況。你可以用這個(gè)查詢來(lái)看看中間生成的結(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è)特殊字符@來(lái)作分隔符, 并在最后用REPLACE把它去除 ,COUNT(*) OVER() cnt ---- 利用分析函數(shù)算出總行數(shù)并把它作為一個(gè)列返回 FROM t ---- 這個(gè)是有四行的集合 WHERE LEVEL=4 ---- 我們需要的僅僅是最后一層的結(jié)果。在PATH里面已經(jīng)包含了取四次的所有結(jié)果組合 CONNECT BY LEVEL<=4 ---- 沒(méi)有任何條件，前后都是獨(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è)特殊字符@來(lái)作分隔符, 并在最后用REPLACE把它去除 ,COUNT(*) OVER() cnt ---- 利用分析函數(shù)算出總行數(shù)并把它作為一個(gè)列返回 FROM t ---- 這個(gè)是有四行的集合 WHERE LEVEL=4 ---- 我們需要的僅僅是最后一層的結(jié)果。在PATH里面已經(jīng)包含了取四次的所有結(jié)果組合 CONNECT BY LEVEL<=4 ---- 沒(méi)有任何條件，前后都是獨(dú)立的 ) ,t3 AS ( ---- 集合t3把t2中的PATH包含的顏色組合拆開(kāi)為四行 SELECT id,cnt,SUBSTR(PATH,rn,1) color FROM t2,t ---- 笛卡兒積，用于把t2中的一行變?yōu)樗男?br> ) 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中過(guò)濾了數(shù)目不為3的那些ID。 GROUP BY id,cnt 表示按照id來(lái)分組。因?yàn)樗行械腸nt都是一樣的(都等于256)，我們?cè)诜纸M加入它并不會(huì)改變分組的結(jié)果，加入cnt的目的是為了在查詢中引用。 最后的連續(xù)兩層COUNT函數(shù)的意思是要把分組結(jié)果再聚合為一行，算出滿足條件的id的行數(shù)。除以cnt就得到了我們要的概率。 本例是一個(gè)在多行的結(jié)果集上進(jìn)行無(wú)條件遍歷的例子，前面說(shuō)過(guò)了要特別小心，因?yàn)闆](méi)有上下級(jí)關(guān)系，隨著層數(shù)遞增，數(shù)據(jù)量的增長(zhǎng)十分可觀。 遞歸WITH寫(xiě)法： 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è)顏色沒(méi)有出現(xiàn)過(guò) THEN t2.distinct_colors||'\'||t.rn --- 拼上去 ELSE t2.distinct_colors ---- 顏色已經(jīng)出現(xiàn)，保持原來(lái)的 END ,t2.lvl+1 --- 層數(shù)遞增 FROM t, t2 WHERE t2.lvl<4 --- 遞歸出口的條件：次數(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類(lèi)似，我們只需觀察最后一層的數(shù)據(jù)，在這里面已經(jīng)包含了所有層次的顏色 ; 在遞歸WITH子查詢t2中，我們看到它用了一個(gè)CASE表達(dá)式把以前沒(méi)出現(xiàn)過(guò)的顏色拼接到distinct_colors中。這個(gè)CASE是遞歸WITH的妙處，用SYS_CONNECT_BY_PATH沒(méi)辦法做到有條件的拼接。 而最后在計(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ì)算，我們也可以在遞歸的過(guò)程中進(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è)顏色沒(méi)有出現(xiàn)過(guò) THEN t2.distinct_colors||'\'||t.rn --- 拼上去 ELSE t2.distinct_colors ---- 顏色已經(jīng)出現(xiàn)，保持原來(lái)的 END ,t2.lvl+1 --- 層數(shù)遞增 ,CASE WHEN INSTR(t2.distinct_colors||'\','\'||t.rn||'\')=0 --- 這個(gè)顏色沒(méi)有出現(xiàn)過(guò) 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ù)達(dá)到限制 ) SELECT COUNT(CASE WHEN distinct_colors_cnt=3 THEN 1 END) --- 出現(xiàn)三個(gè)斜杠 /COUNT(*) FROM t2 WHERE lvl=4 ---- 同CONNECT BY類(lèi)似，我們只需觀察最后一層的數(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寫(xiě)法： 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 ---- 沒(méi)有任何連接，因?yàn)椴恍枰?br> WHERE t.lvl<10 ---- 找到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)開(kāi)始，每一項(xiàng)都等于前兩項(xiàng)之和。 1,1,2,3,5,8,13,21,...... 傳統(tǒng)的CONNECT BY方法做不出來(lái)，但是用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的寫(xiě)法： 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 ---- 沒(méi)有任何連接，因?yàn)椴恍枰?br> WHERE t.cnt<10 ---- 找到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寫(xiě)法： 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 ---- 強(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寫(xiě)法： WITH t AS ( SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL<6 ) ,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.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)可以這么寫(xiě)： 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. 和剛才的組合寫(xiě)法相比，rn<PRIOR rn變成了NOCYCLE rn<>PRIOR rn, 這表示只要rn沒(méi)出現(xiàn)過(guò)就行，我們要的是所有的排列順序而不僅僅是降序。注意這里面的NOCYCLE, 這個(gè)是10G上才有的。 如果不寫(xiě)這個(gè)NOCYCLE會(huì)怎么樣？ 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 可以看到，這個(gè)NOCYCLE是很重要的，ORACLE不允許遍歷順序中出現(xiàn)循環(huán)。 在遞歸WITH中，NOCYCLE的寫(xiě)法： WITH t AS ( SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL<6 ) ,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.lvl<3 ) 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é)果： 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 ,2,1,5 ......(其余結(jié)果略) 60 rows selected. 在這里我們看到了前面例子中沒(méi)出現(xiàn)過(guò)的循環(huán)子句（CYCLE CLAUSE）。循環(huán)子句的語(yǔ)法如下： ① CYCLE c_alias [, c_alias]... ---- 用哪些列來(lái)判斷是否發(fā)生CYCLE。一般來(lái)說(shuō)就是要遍歷的集合能夠唯一標(biāo)識(shí)出每行的那些列，例如主鍵。 ② SET cycle_mark_c_alias TO cycle_value ---- cycle_mark_c_alias如果發(fā)生CYCLE了要設(shè)置什么值，一般就是Y/N, 1/0 來(lái)表示是否CYCLE ③ DEFAULT no_cycle_value ---- 沒(méi)有CYCLE要設(shè)什么值 ① 用哪些列來(lái)判斷是否發(fā)生CYCLE。一般來(lái)說(shuō)就是要遍歷的集合能夠唯一標(biāo)識(shí)出每行的那些列，例如主鍵。 ② cycle_mark_c_alias是用來(lái)存放CYCLE標(biāo)記的列名。在本例中我們?yōu)樗∶鹀ycle_flag。 cycle_value是當(dāng)發(fā)生了循環(huán)（下一個(gè)節(jié)點(diǎn)已經(jīng)被遍歷過(guò)）的時(shí)候，要把什么值存到cycle_mark_c_alias列中。 因?yàn)槭遣紶栔?，我們一般用Y/N, 1/0來(lái)表示。在本例中用的是'Y'。 ③ 在沒(méi)有循環(huán)的情況下要賦予cycle_mark_c_alia什么值。在本例中用的是'N'。 定義了循環(huán)子句之后，ORACLE碰到循環(huán)就會(huì)自動(dòng)停止并為你指定的cycle_mark_c_alias列賦予一個(gè)標(biāo)記。在隨后的SELECT中可以用這個(gè)標(biāo)記來(lái)過(guò)濾數(shù)據(jù)。 如果不寫(xiě)這個(gè)循環(huán)子句會(huì)怎么樣？ WITH t AS ( SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL<6 ) ,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 ) SELECT xmlpath FROM t2 WHERE lvl=3; WITH t AS ( * ERROR at line 1: ORA-32044: cycle detected while executing recursive WITH query 假如在上述SQL的WHERE中加上層數(shù)限制，則錯(cuò)誤不會(huì)出現(xiàn)，但是數(shù)據(jù)中有重復(fù)節(jié)點(diǎn)： WITH t AS ( SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL<6 ) ,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 lvl<3 ) SELECT xmlpath FROM t2 WHERE lvl=3; 結(jié)果： XMLPATH ------------ ,1,2,1 ------ 可以看到1被訪問(wèn)了兩次 ,1,2,3 ,1,2,4 ,1,2,5 ,1,3,1 ,1,3,2 ,1,3,4 ,1,3,5 ,1,4,1 ,1,4,2 ,1,4,3 ,1,4,5 ,1,5,1 ,1,5,2 ......(其余結(jié)果略) 80 rows selected. 那么這個(gè)問(wèn)題不用循環(huán)子句能否解決呢？其實(shí)我們?cè)谇懊娴睦?中已經(jīng)用到了一個(gè)技巧，就是在遞歸的部分加上一個(gè)條件，僅當(dāng)該節(jié)點(diǎn)未出現(xiàn)過(guò)時(shí)才加入： WITH t AS ( SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL<6 ) ,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.lvl<3 AND INSTR(t2.xmlpath,t.rn)=0 ------- 新加入的節(jié)點(diǎn)必須是未出現(xiàn)過(guò)的 ) SELECT xmlpath FROM t2 WHERE lvl=3; 例7: 求遍歷樹(shù)中的葉子節(jié)點(diǎn)。 在例1中我們看到一個(gè)求出所有員工的上級(jí)關(guān)系的層次查詢?，F(xiàn)在假設(shè)要把最下級(jí)的員工求出來(lái)： CONNECT BY寫(xiě)法： SELECT empno, ename, job, deptno,LEVEL, SYS_CONNECT_BY_PATH(ename,'\') as path FROM EMP WHERE CONNECT_BY_ISLEAF=1 ----- 葉子節(jié)點(diǎn) START WITH mgr IS NULL -- mgr列為空，表示沒(méi)有上級(jí)，該員工已經(jīng)是最高級(jí)別。這是層次查詢的起點(diǎn) CONNECT BY PRIOR empno= mgr; 結(jié)果： EMPNO ENAME JOB DEPTNO LEVEL PATH ----------------------------------------------------------------- 7876 ADAMS CLERK 20 4 \KING\JONES\SCOTT\ADAMS 7369 SMITH CLERK 20 4 \KING\JONES\FORD\SMITH 7499 ALLEN SALESMAN 30 3 \KING\BLAKE\ALLEN 7521 WARD SALESMAN 30 3 \KING\BLAKE\WARD 7654 MARTIN SALESMAN 30 3 \KING\BLAKE\MARTIN 7844 TURNER SALESMAN 30 3 \KING\BLAKE\TURNER 7900 JAMES CLERK 30 3 \KING\BLAKE\JAMES 7934 MILLER CLERK 10 3 \KING\CLARK\MILLER CONNECT_BY_ISLEAF是10G以上提供的，它是一個(gè)偽列，當(dāng)CONNECT_BY_ISLEAF=1時(shí)表示該行為葉子節(jié)點(diǎn)。 在9i中沒(méi)有CONNECT_BY_ISLEAF這個(gè)偽列，我們可以根據(jù)葉子節(jié)點(diǎn)的定義寫(xiě)一個(gè)相關(guān)子查詢： SELECT empno, ename, job, deptno,LEVEL, SYS_CONNECT_BY_PATH(ename,'\') as path FROM EMP e WHERE NOT EXISTS (SELECT NULL FROM emp WHERE mgr = e.empno) START WITH mgr IS NULL CONNECT BY PRIOR empno= mgr; 在這里，NOT EXISTS相關(guān)子查詢的意思即不存在下一級(jí)數(shù)據(jù)，這正是葉子節(jié)點(diǎn)的定義。 在遞歸WITH子查詢中實(shí)現(xiàn)CONNECT_BY_ISLEAF的方法: WITH T(empno, ename, mgr, the_level, path) AS ( ---- 必須把結(jié)構(gòu)寫(xiě)出來(lái) SELECT empno, ename, mgr ---- 先寫(xiě)錨點(diǎn)查詢，用START WITH的條件 ,1 AS the_level ---- 遞歸起點(diǎn)，第一層 ,'\'||ename ---- 路徑的第一截 FROM EMP WHERE mgr IS NULL ---- 原來(lái)的START WITH條件 UNION ALL ---- 下面是遞歸部分 SELECT e.empno, e.ename, e.mgr ---- 要加入的新一層數(shù)據(jù)，來(lái)自要遍歷的emp表 ,1 + t.the_level ---- 遞歸層次，在原來(lái)的基礎(chǔ)上加1 ,t.path||'\'||e.ename ---- 把新的一截路徑拼上去 FROM t, emp e ---- 典型寫(xiě)法，把子查詢本身和要遍歷的表作一個(gè)連接 WHERE t.empno = e.mgr ---- 原來(lái)的CONNECT BY條件 ) SEARCH DEPTH FIRST BY mgr SET seq --- 指定深度優(yōu)先, 按順序生成序號(hào)seq SELECT * FROM ( SELECT T.* ,(CASE WHEN the_level < LEAD(the_level) OVER (ORDER BY seq) THEN 0 ELSE 1 END) is_leaf ---- 如果層數(shù)不增加則為葉子節(jié)點(diǎn)。 FROM T ) WHERE is_leaf=1 ; EMPNO ENAME MGR THE_LEVEL PATH SEQ IS_LEAF ------ ---------- ------- ---------- ---------------------------- ---------- ---------- 7876 ADAMS 7788 4 \KING\JONES\SCOTT\ADAMS 4 1 7369 SMITH 7902 4 \KING\JONES\FORD\SMITH 6 1 7499 ALLEN 7698 3 \KING\BLAKE\ALLEN 8 1 7521 WARD 7698 3 \KING\BLAKE\WARD 9 1 7654 MARTIN 7698 3 \KING\BLAKE\MARTIN 10 1 7844 TURNER 7698 3 \KING\BLAKE\TURNER 11 1 7900 JAMES 7698 3 \KING\BLAKE\JAMES 12 1 7934 MILLER 7782 3 \KING\CLARK\MILLER 14 1 8 rows selected. seq 是整個(gè)結(jié)果集按照深度優(yōu)先遍歷順序排序后的序號(hào)。按照深度優(yōu)先的定義，每個(gè)節(jié)點(diǎn)都要比它的上一節(jié)點(diǎn)更深一層，除非已經(jīng)到底（也就是葉子節(jié)點(diǎn)）。因此如果層數(shù)沒(méi)有增加，則我們知道碰到了葉子節(jié)點(diǎn)，這就是最后一個(gè)CASE表達(dá)式的依據(jù)。 這個(gè)例子中出現(xiàn)了前面沒(méi)有的搜索子句(SEARCH CLAUSE)。 SEARCH子句的語(yǔ)法： SEARCH ① { DEPTH FIRST BY c_alias [, c_alias]... [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ] ② | BREADTH FIRST BY c_alias [, c_alias]... [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ] } ③ SET ordering_column ①DEPTH FIRST表示按深度優(yōu)先的順序來(lái)輸出。BY后面的列名及其升降序、空值放置順序指明了在深度優(yōu)先的前提下，同一層次的數(shù)據(jù)的排序情況。這和原來(lái)CONNECT BY查詢中的ORDER SIBLING BY子句是一樣的。 ②BREADTH FIRST表示按廣度優(yōu)先的順序來(lái)輸出。BY后面的列名及其升降序、空值放置順序指明了在廣度優(yōu)先的前提下，同一層次的數(shù)據(jù)的排序情況。 ③列名ordering_column用于存放排序后的序號(hào)，是一個(gè)從1開(kāi)始的連續(xù)正整數(shù)。后續(xù)的查詢中可以利用這個(gè)列得知某行數(shù)據(jù)在整個(gè)結(jié)果集中的位置。 那么這個(gè)搜索子句是不是可以指定ORACLE的搜索順序呢？我們從遞歸WITH的寫(xiě)法來(lái)看，數(shù)據(jù)是一層一層疊加上去的，也就是廣度優(yōu)先算法。 為了證明這一點(diǎn)，我們可以寫(xiě)一個(gè)自定義函數(shù)，根據(jù)輸出的順序來(lái)判斷ORACLE的搜索順序： CREATE OR REPLACE FUNCTION f_get_mgr ( p_empno IN NUMBER ---- 當(dāng)前員工編號(hào) ,p_mgr IN NUMBER ---- 當(dāng)前員工的經(jīng)理編號(hào) ,p_last_emp IN NUMBER ---- 上一層員工的編號(hào) ,p_level IN NUMBER ---- 當(dāng)前員工的層數(shù) ) RETURN NUMBER AS BEGIN IF p_last_emp = p_mgr THEN ---- 假如發(fā)生匹配，證明這個(gè)節(jié)點(diǎn)將被加入，這時(shí)我們輸出當(dāng)前員工的編號(hào)。從屏幕的輸出就可以判斷節(jié)點(diǎn)被加入的順序 ---- 此輸出可以觀察函數(shù)被調(diào)用的順序 DBMS_OUTPUT.PUT_LINE('level='||p_level||' empno='||p_empno); END IF; RETURN p_mgr; END f_get_mgr; / 把查詢修改一下： WITH T(empno, ename, mgr, the_level, path) AS ( SELECT empno, ename, mgr ,1 AS the_level ,'\'||ename FROM EMP WHERE mgr IS NULL UNION ALL SELECT e.empno, e.ename, e.mgr ,1 + t.the_level ,t.path||'\'||e.ename FROM t, emp e WHERE t.empno = f_get_mgr(e.empno,e.mgr,t.empno, t.the_level+1) ----此函數(shù)會(huì)返回經(jīng)理編號(hào)，并在屏幕上輸出員工編號(hào) ) SEARCH DEPTH FIRST BY mgr SET seq ---- 強(qiáng)行指定深度優(yōu)先順序 SELECT * FROM T ; 輸出： EMPNO ENAME MGR THE_LEVEL PATH SEQ -------- ---------- ------ ---------- -------------------------- ---------- 7839 KING 1 \KING 1 7566 JONES 7839 2 \KING\JONES 2 7788 SCOTT 7566 3 \KING\JONES\SCOTT 3 7876 ADAMS 7788 4 \KING\JONES\SCOTT\ADAMS 4 7902 FORD 7566 3 \KING\JONES\FORD 5 7369 SMITH 7902 4 \KING\JONES\FORD\SMITH 6 7698 BLAKE 7839 2 \KING\BLAKE 7 7499 ALLEN 7698 3 \KING\BLAKE\ALLEN 8 7521 WARD 7698 3 \KING\BLAKE\WARD 9 7654 MARTIN 7698 3 \KING\BLAKE\MARTIN 10 7844 TURNER 7698 3 \KING\BLAKE\TURNER 11 7900 JAMES 7698 3 \KING\BLAKE\JAMES 12 7782 CLARK 7839 2 \KING\CLARK 13 7934 MILLER 7782 3 \KING\CLARK\MILLER 14 14 rows selected. level=2 empno=7566 level=2 empno=7698 level=2 empno=7782 level=3 empno=7788 level=3 empno=7902 level=3 empno=7499 level=3 empno=7521 level=3 empno=7654 level=3 empno=7844 level=3 empno=7900 level=3 empno=7934 level=4 empno=7876 level=4 empno=7369 盡管前半部分輸出已經(jīng)按照深度優(yōu)先順序排列，但從后半部分的輸出來(lái)看，節(jié)點(diǎn)是逐層被加入的，可見(jiàn)遍歷還是按廣度優(yōu)先順序來(lái)的。 如果我們把查詢改為廣度優(yōu)先，就和函數(shù)調(diào)用順序一致了： WITH T(empno, ename, mgr, the_level, path) AS ( SELECT empno, ename, mgr ,1 AS the_level ,'\'||ename FROM EMP WHERE mgr IS NULL UNION ALL SELECT e.empno, e.ename, e.mgr ,1 + t.the_level ,t.path||'\'||e.ename FROM t, emp e WHERE t.empno = f_get_mgr(e.empno,e.mgr,t.empno, t.the_level+1) ----此函數(shù)會(huì)返回經(jīng)理編號(hào)，并在屏幕上輸出員工編號(hào) ) SEARCH BREADTH FIRST BY mgr SET seq ---- 強(qiáng)行指定廣度優(yōu)先順序 SELECT * FROM T ; 輸出： EMPNO ENAME MGR THE_LEVEL PATH SEQ ------- ---------- ------ ---------- -------------------------- ------ 7839 KING 1 \KING 1 7566 JONES 7839 2 \KING\JONES 2 7698 BLAKE 7839 2 \KING\BLAKE 3 7782 CLARK 7839 2 \KING\CLARK 4 7788 SCOTT 7566 3 \KING\JONES\SCOTT 5 7902 FORD 7566 3 \KING\JONES\FORD 6 7499 ALLEN 7698 3 \KING\BLAKE\ALLEN 7 7521 WARD 7698 3 \KING\BLAKE\WARD 8 7654 MARTIN 7698 3 \KING\BLAKE\MARTIN 9 7844 TURNER 7698 3 \KING\BLAKE\TURNER 10 7900 JAMES 7698 3 \KING\BLAKE\JAMES 11 7934 MILLER 7782 3 \KING\CLARK\MILLER 12 7876 ADAMS 7788 4 \KING\JONES\SCOTT\ADAMS 13 7369 SMITH 7902 4 \KING\JONES\FORD\SMITH 14 14 rows selected. level=2 empno=7566 level=2 empno=7698 level=2 empno=7782 level=3 empno=7788 level=3 empno=7902 level=3 empno=7499 level=3 empno=7521 level=3 empno=7654 level=3 empno=7844 level=3 empno=7900 level=3 empno=7934 level=4 empno=7876 level=4 empno=7369 以往也有人在9i中用這個(gè)方法制造is_leaf標(biāo)記，但是該方法依賴(lài)于CONNECT BY的遍歷順序生成的ROWNUM，而ORACLE并沒(méi)有保障ROWNUM一定會(huì)按深度優(yōu)先的順序生成?，F(xiàn)在遞歸WITH子句支持遍歷輸出順序的顯式指定，就可以放心使用了。 -------------------------------------------------------- 例8: 下面的例子來(lái)自我的一個(gè)老貼： http://www./thread-1037629-1-1.html 機(jī)票路線問(wèn)題：假設(shè)有一張表存儲(chǔ)了起飛、到達(dá)機(jī)場(chǎng)以及這段路線的票價(jià)，現(xiàn)在要求出從城市1到城市2的所有飛行線路及其總價(jià)格。 測(cè)試環(huán)境設(shè)置： CREATE TABLE fares ---- 票價(jià) ( depart VARCHAR2(3), --- 起飛機(jī)場(chǎng) arrive VARCHAR2(3), --- 到達(dá)機(jī)場(chǎng) price NUMBER ); 測(cè)試數(shù)據(jù)： INSERT INTO FARES VALUES('BJ','SH',500); INSERT INTO FARES VALUES('SH','GZ',1500); INSERT INTO FARES VALUES('BJ','GZ',1800); INSERT INTO FARES VALUES('GZ','BJ',1600); insert into fares values('GZ','SH',1300); INSERT INTO FARES VALUES('BJ','SZ',100); INSERT INTO FARES VALUES('SZ','GZ',110); COMMIT; 用CONNECT BY求出所有北京到上海的線路，轉(zhuǎn)機(jī)不超過(guò)10次： WITH f1 AS ( SELECT LEVEL lvl ,SYS_CONNECT_BY_PATH(depart,'/') ---- 用SYS_CONNECT_BY_PATH把所有的出發(fā)機(jī)場(chǎng)拼接起來(lái)。 ||'/'||arrive||'/' AS path ---- 在右邊拼上最后一段旅程的到達(dá)機(jī)場(chǎng)，構(gòu)成完整的路徑。最后添加的斜杠是為了下面解析方便。 FROM fares WHERE arrive = 'SH' ---- 目的地是上海 START WITH depart = 'BJ' ---- 從北京出發(fā) CONNECT BY NOCYCLE depart=prior arrive ---- 每段路線首位銜接 AND 'BJ'<>arrive ----- 目的地不能是北京，否則就繞回去了 AND LEVEL <= 11 ----- 轉(zhuǎn)機(jī)不超過(guò)10次 ) SELECT * FROM f1; 輸出： LVL PATH ---------- ------------------------------------------ 2 /BJ/GZ/SH/ 1 /BJ/SH/ 3 /BJ/SZ/GZ/SH/ 可以看到有三條飛行路線，LEVEL表示了路線分為幾段（轉(zhuǎn)機(jī)次數(shù)）。 現(xiàn)在路線有了，接下來(lái)就得算總價(jià)格。這就意味這必須和fares做一個(gè)連接，路線的每一段都必須拆成起飛、到達(dá)機(jī)場(chǎng)這樣的格式。 為了把一行拆成多行，我們必須構(gòu)造一個(gè)自然數(shù)集(從1到最大LEVEL)連接上去；而每段路線的起始點(diǎn)可以用字符串函數(shù)INSTR, SUBSTR函數(shù)解析出來(lái)。 WITH f1 AS ( SELECT LEVEL lvl ,SYS_CONNECT_BY_PATH(depart,'/') ---- 用SYS_CONNECT_BY_PATH把所有的出發(fā)機(jī)場(chǎng)拼接起來(lái)。 ||'/'||arrive||'/' AS path ---- 在右邊拼上最后一段旅程的到達(dá)機(jī)場(chǎng)，構(gòu)成完整的路徑。最后添加的斜杠是為了下面解析方便。 FROM fares WHERE arrive = 'SH' ---- 目的地是上海 START WITH depart = 'BJ' ---- 從北京出發(fā) CONNECT BY NOCYCLE depart=prior arrive ---- 每段路線首位銜接 AND 'BJ'<>arrive ----- 目的地不能是北京，否則就繞回去了 AND LEVEL <= 11 ----- 轉(zhuǎn)機(jī)不超過(guò)10次 ) ,f2 AS ( SELECT path ,SUBSTR(path, INSTR(path,'/',1,n )+1, INSTR(path,'/',1,n+1)-INSTR(path,'/',1,n )-1) AS depart ,SUBSTR(path, INSTR(path,'/',1,n+1)+1, INSTR(path,'/',1,n+2)-INSTR(path,'/',1,n+1)-1) AS arrive FROM f1 ,(SELECT LEVEL AS n ------- 利用CONNECT BY構(gòu)造出一個(gè)自然數(shù)集合 FROM DUAL CONNECT BY LEVEL<=(SELECT MAX(lvl) FROM f1) ---- 所需要的總行數(shù)不超過(guò)集合f1里面的最多的路線段數(shù) ) r WHERE r.n<=lvl ------ 每一行包含的路線段數(shù)為lvl, 相對(duì)于這一行我們需要拆成行數(shù)為lvl，即從集合r中取出1~lvl行來(lái)進(jìn)行連接。 ) SELECT * FROM f2 ORDER BY 1; 輸出： PATH DEPART ARRIVE -------------------------- ---------- ---------- /BJ/GZ/SH/ GZ SH /BJ/GZ/SH/ BJ GZ /BJ/SH/ BJ SH /BJ/SZ/GZ/SH/ SZ GZ /BJ/SZ/GZ/SH/ GZ SH /BJ/SZ/GZ/SH/ BJ SZ 6 rows selected. 至此我們不僅擁有了所有北京到上海的飛行線路，而且這條線路的組成也都分解出來(lái)了。接下來(lái)的事情就是和fares表做一個(gè)連接求出票價(jià)，然后按PATH來(lái)做分組聚合求出線路總成本： WITH f1 AS ( SELECT LEVEL lvl ,SYS_CONNECT_BY_PATH(depart,'/') ---- 用SYS_CONNECT_BY_PATH把所有的出發(fā)機(jī)場(chǎng)拼接起來(lái)。 ||'/'||arrive||'/' AS path ---- 在右邊拼上最后一段旅程的到達(dá)機(jī)場(chǎng)，構(gòu)成完整的路徑。最后添加的斜杠是為了下面解析方便。 FROM fares WHERE arrive = 'SH' ---- 目的地是上海 START WITH depart = 'BJ' ---- 從北京出發(fā) CONNECT BY NOCYCLE depart=prior arrive ---- 每段路線首位銜接 AND 'BJ'<>arrive ----- 目的地不能是北京，否則就繞回去了 AND LEVEL <= 11 ----- 轉(zhuǎn)機(jī)不超過(guò)10次 ) ,f2 AS ( SELECT path ,SUBSTR(path, INSTR(path,'/',1,n )+1, INSTR(path,'/',1,n+1)-INSTR(path,'/',1,n )-1) AS depart ,SUBSTR(path, INSTR(path,'/',1,n+1)+1, INSTR(path,'/',1,n+2)-INSTR(path,'/',1,n+1)-1) AS arrive FROM f1 ,(SELECT LEVEL AS n ------- 利用CONNECT BY構(gòu)造出一個(gè)自然數(shù)集合 FROM DUAL CONNECT BY LEVEL<=(SELECT MAX(lvl) FROM f1) ---- 所需要的總行數(shù)不超過(guò)集合f1里面的最多的路線段數(shù) ) r WHERE r.n<=lvl ------ 每一行包含的路線段數(shù)為lvl, 相對(duì)于這一行我們需要拆成行數(shù)為lvl，即從集合r中取出1~lvl行來(lái)進(jìn)行連接。 ) SELECT SUM(price) AS cost,path FROM f2,fares WHERE f2.depart=fares.depart AND f2.arrive = fares.arrive GROUP BY path ORDER BY cost; 結(jié)果： COST PATH ---------- ------------------------ 500 /BJ/SH/ 1510 /BJ/SZ/GZ/SH/ 3100 /BJ/GZ/SH/ 另外也有一個(gè)稍微簡(jiǎn)化的寫(xiě)法。在拼接路徑的時(shí)候，我們可以把價(jià)格也拼起來(lái)，最后只要分解開(kāi)再求和就可以了，無(wú)需和fares表再做連接： WITH f1 AS ( SELECT LEVEL lvl ,SYS_CONNECT_BY_PATH(depart,'/') ---- 用SYS_CONNECT_BY_PATH把所有的出發(fā)機(jī)場(chǎng)拼接起來(lái)。 ||'/'||arrive||'/' AS path ---- 在右邊拼上最后一段旅程的到達(dá)機(jī)場(chǎng)，構(gòu)成完整的路徑。最后添加的斜杠是為了下面解析方便。 ,SYS_CONNECT_BY_PATH(price,'/')||'/' AS price_path ---- 用SYS_CONNECT_BY_PATH把所有的票價(jià)拼接起來(lái)。 FROM fares WHERE arrive = 'SH' ---- 目的地是上海 START WITH depart = 'BJ' ---- 從北京出發(fā) CONNECT BY NOCYCLE depart=prior arrive ---- 每段路線首位銜接 AND 'BJ'<>arrive ----- 目的地不能是北京，否則就繞回去了 AND LEVEL <= 11 ----- 轉(zhuǎn)機(jī)不超過(guò)10次 ) ,f2 AS ( SELECT path ,SUBSTR(price_path, INSTR(price_path,'/',1,n)+1, INSTR(price_path,'/',1,n+1)-INSTR(price_path,'/',1,n)-1) AS price FROM f1 ,(SELECT LEVEL AS n ------- 利用CONNECT BY構(gòu)造出一個(gè)自然數(shù)集合 FROM DUAL CONNECT BY LEVEL<=(SELECT MAX(lvl) FROM f1) ---- 所需要的總行數(shù)不超過(guò)集合f1里面的最多的路線段數(shù) ) r WHERE r.n<=lvl ------ 每一行包含的路線段數(shù)為lvl, 相對(duì)于這一行我們需要拆成行數(shù)為lvl，即從集合r中取出1~lvl行來(lái)進(jìn)行連接。 ) SELECT SUM(price) AS cost,path FROM f2 GROUP BY path ORDER BY cost; 上述用CONNECT BY求出總票價(jià)的方法十分繁瑣，因?yàn)镾YS_CONNECT_BY_PATH必須先分開(kāi)再聚合。 遞歸WITH的方法：沿路徑求和的實(shí)現(xiàn)非常方便。 WITH T (depart,arrive,path,cost,lvl) AS ( SELECT depart ---- 構(gòu)造第一層數(shù)據(jù)：從起點(diǎn)城市出發(fā) ,arrive ,'/'||depart AS PATH ,price ,1 FROM fares WHERE depart = 'BJ' ---- 起點(diǎn)是北京 UNION ALL ------- 遞歸部分：把銜接的新一段路程拼接進(jìn)去 SELECT f.depart ,f.arrive ,t.path||'/'||f.depart ----- 把新的路段的起點(diǎn)機(jī)場(chǎng)拼接上去 ,t.cost + f.price ----- 把新的路段的票價(jià)累加到總成本中。這是遞歸WITH最強(qiáng)大的地方。 ,t.lvl+1 ----- 層數(shù)遞增 FROM t,fares f WHERE f.depart=t.arrive ----- 遞歸條件：起飛機(jī)場(chǎng)是上一段的到達(dá)機(jī)場(chǎng) AND 'BJ'<>f.arrive ----- 目的地不能是北京，否則就繞回去了 AND t.arrive<>'SH' ----- 遞歸終止條件：如果上一段終點(diǎn)已經(jīng)是上海，沒(méi)必要繼續(xù)遍歷了 AND t.cost + f.price <5000 ------- 控制總成本在5000以內(nèi)，否則停止遍歷。這個(gè)剪枝功能是CONNECT BY做不到的。 AND lvl<=10 -------- 控制轉(zhuǎn)機(jī)次數(shù)，轉(zhuǎn)機(jī)不超過(guò)10次 AND INSTR(t.path,'/'||f.depart)=0 ------ 新一段路程的出發(fā)機(jī)場(chǎng)在路徑中未出現(xiàn)過(guò)。相當(dāng)于CONNECT BY的NOCYCLE功能，或是遞歸WITH中的CYCLE子句。 ) SELECT t.path||'/'||t.arrive path ---- 在右邊拼上最后一段旅程的到達(dá)機(jī)場(chǎng)，構(gòu)成完整的路徑。 ,t.cost FROM T WHERE arrive='SH'; PATH COST ------------------- ---------- /BJ/SH 500 /BJ/GZ/SH 3100 /BJ/SZ/GZ/SH 1510 例8: 沿路徑求乘積應(yīng)用例子: BOM BOM（Bill Of Material）是一個(gè)ERP的相關(guān)術(shù)語(yǔ)，指的是物料清單，又稱(chēng)材料表或配方料表，它反映生產(chǎn)產(chǎn)品與其組件的數(shù)量和從屬關(guān)系。 復(fù)雜的版本見(jiàn)： http://www./thread-1233081-3-1.html 這里是一個(gè)簡(jiǎn)化版。 測(cè)試環(huán)境設(shè)置： CREATE TABLE BOM ( item_no VARCHAR2(10) ---- 部件編號(hào) ,part_no VARCHAR2(10) ---- 構(gòu)成該部件的子部件編號(hào) ,qty NUMBER ---- 構(gòu)成該部件的子部件數(shù)量 ); 測(cè)試數(shù)據(jù)： INSERT INTO BOM VALUES ('A','B',3); INSERT INTO BOM VALUES ('A','C',2); INSERT INTO BOM VALUES ('A','D',4); INSERT INTO BOM VALUES ('B','E',2); INSERT INTO BOM VALUES ('B','F',3); INSERT INTO BOM VALUES ('D','G',6); INSERT INTO BOM VALUES ('D','H',5); INSERT INTO BOM VALUES ('E','I',3); COMMIT; 例子數(shù)據(jù)中部件A由3個(gè)B, 2個(gè)C和4個(gè)D組成，一個(gè)B由2個(gè)E和3個(gè)F組成，等等。 現(xiàn)在要求組成A的所有零部件及其數(shù)量。 用CONNECT BY的辦法十分困難。和上例類(lèi)似，首先要求出樹(shù)的遍歷路徑，然后再把路徑拆開(kāi)為多行，求出每段的數(shù)量構(gòu)成；最后再做聚合運(yùn)算。因?yàn)闆](méi)有現(xiàn)成的聚合求積函數(shù)，必須轉(zhuǎn)換為對(duì)數(shù)，求出對(duì)數(shù)之和，再取反對(duì)數(shù)。 WITH t AS ( SELECT ROWNUM id ----- 為每行分配一個(gè)唯一的id, 用于隨后的記錄拆分和聚合 ,part_no ,SYS_CONNECT_BY_PATH(qty,',') PATH ----- 把每一段的組成數(shù)量拼接起來(lái) ,LEVEL lvl FROM bom START WITH item_no='A' CONNECT BY item_no = PRIOR part_no ) ,t2 AS ( SELECT id ,part_no ,EXP(SUM(LN(REGEXP_SUBSTR(path,'[^,]+',1,rn)) ------ 用規(guī)則表達(dá)式辨識(shí)出分隔符（逗號(hào)），把每一段數(shù)量qty求出來(lái)；用LN函數(shù)取自然對(duì)數(shù)；用SUM求和；用EXP指數(shù)函數(shù)求出乘積 ) ) qty FROM t,(SELECT ROWNUM rn ------- 利用CONNECT BY構(gòu)造出一個(gè)自然數(shù)集合 FROM (SELECT MAX(lvl) lvl FROM t) CONNECT BY ROWNUM<=lvl ) r WHERE rn <= lvl ------ 每一行包含的段數(shù)為lvl, 相對(duì)于這一行我們需要拆成行數(shù)為lvl，即從集合r中取出1~lvl行來(lái)進(jìn)行連接。 GROUP BY id,part_no ) SELECT part_no,SUM(qty) from t2 GROUP BY part_no; 結(jié)果： PART_NO SUM(QTY) ---------- ---------- H 20 I 18 D 4 B 3 C 2 F 9 E 6 G 24 8 rows selected. 用遞歸WITH的辦法可謂賞心悅目： WITH t(part_no,qty) AS ( SELECT part_no ---- 取出第一層數(shù)據(jù)，即需要計(jì)算的部件A ,qty FROM bom WHERE item_no='A' UNION ALL SELECT b.part_no ,t.qty*b.qty ---- 新加入的節(jié)點(diǎn)乘上已有的乘積，就是這條路徑的總乘積 FROM t, bom b WHERE b.item_no = t.part_no ) SELECT part_no,SUM(qty) from t GROUP BY part_no; 結(jié)果： PART_NO SUM(QTY) ---------- ---------- H 20 I 18 D 4 B 3 C 2 E 6 F 9 G 24 8 rows selected. |
|
|
來(lái)自： ctbtcol > 《待分類(lèi)1》
