Oracle 11GR2的递归WITH子查询方法

下面给大家详细介绍Oracle 11GR2的递归WITH子查询方法，具体内容如下所示：

with emp_data(ename,empno,mgr,l) as (select ename,1 lvl from emp where mgr is null union all select emp.ename,emp.empno,emp.mgr,ed.l+1 from emp,emp_data ed where emp.mgr = ed.empno ) SEARCH DEPTH FIRST BY ename SET order_by select l,lpad('*',2*l,'*')||ename nm from emp_data order by order_by /

L NM
---- ---------------
1 **KING
2 ****BLAKE
3 ******ALLEN
3 ******JAMES
3 ******MARTIN
3 ******TURNER
3 ******WARD
2 ****CLARK
3 ******MILLER
2 ****JONES
3 ******FORD
4 ********SMITH
3 ******SCOTT
4 ********ADAMS

14 rows selected.

不知道真用起来怎么样，按我的想象可以比原来的SYS_CONNECT_BY_PATH多玩出很多新花样，比如按路径累加，更灵活的剪枝条件，

WITH子查询也称为CTE (Common Table Expression)，是ANSI sql-99标准的一部分。ORACLE从9i开始引入WITH子查询，把它被称作SUBQUERY FACTORING(分解子查询)。

WITH子查询的作用类似于内联视图(INLINE VIEW)。内联视图的定义写作sql的FROM 后面，只能够引用一次；而WITH子查询需要在引用之前先定义，一旦定义了在整个查询的后续部分就可以按名称来反复引用，从这点来看又很像临时表。

从版本11GR2开始，ORACLE支持递归的WITH,即允许在WITH子查询的定义中对自身引用。这不是什么新鲜事，其他数据库如DB2,Firebird,Microsoft sql Server,Postgresql 都先于ORACLE支持这一特性。但对于ORACLE用户来说，这一递归特性还是很令人期待的，利用它可以轻易实现以往做不到的、或者很难做到的许多新功能。这一章我们就来探索这一令人兴奋的新特性，并把它和以往的实现手段（主要是CONNECT BY层次查询）作比较。

我们先来看看这个递归WITH子查询的语法：

WITH

① query_name ([c_alias [,c_alias]...])
② AS (subquery)
③ [search_clause]
④ [cycle_clause]
⑤ [,query_name ([c_alias [,c_alias]...]) AS (subquery) [search_clause] [cycle_clause]]...

①这是子查询的名称，和以往不同的是，必须在括号中把这个子查询的所有列名写出来。
②AS后面的subquery就是查询语句，递归部分就写在这里。
③遍历顺序子句，可以指定深度优先或广度优先遍历顺序。
④循环子句，用于中止遍历中出现的死循环。
⑤如果还有其他递归子查询，定义同上。

subquery部分由两个成员组成：anchor member(锚点成员) 和 recursive member(递归成员)。它们之间必须用union all联合起来，anchor member 必须写在recursive member前面。
anchor member用来定位递归的入口，锚点成员是一个SELECT语句，它不可以包含自身名称(query_name)。这相当于CONNECT BY查询中的START WITH，典型写法就是:
SELECT ... FROM 要遍历的表 WHERE ... (起始条件)

递归成员也是一个SELECT语句，用于定义上下级的关系，它必须包含自身名称(即query_name)，而且仅仅只能引用一次。递归正是体现在对于自身的引用。典型的做法就是把query_name和其他表(一般来说就是你要遍历的表)做一个连接，连接条件表明了上下级的关系。必须注意，在这个query_name中，并不是截止目前为止的所有数据都是可见的，可见的只是上次递归新加入的最近的一层数据。对query_name列的引用相当于CONNECT BY中的PRIOR操作符。当找不到满足条件的下级，遍历就会停止；如果你还有其他的递归出口条件，也可以一起写在WHERE中，当WHERE不满足时，遍历就会停止，这就是在遍历树、图时候的剪枝操作。越早停止则效率越高。

这个递归成员就是程序员发挥创造力的地方，以往在CONNECT BY中做不到的事情，比如沿路径求和、求积等运算，现在都轻而易举。而SYS_CONNECT_BY_PATH也很容易用字符串拼接'||'来实现。

搜索子句(search_clause)和循环子句(cycle_clause)我们后面的例子中会见到。

下面我们就来看看递归WITH子查询的用法实例。

例1:

先来一个简单例子，从scott/tiger的emp表来查找上下级关系：

传统的CONNECT BY写法：

sql;"> SELECT empno,ename,job,deptno,level,SYS_CONNECT_BY_PATH(ename,'\') AS path,CONNECT_BY_ROOT(ename) AS top_manager FROM EMP START WITH mgr IS NULL -- mgr列为空，表示没有上级，该员工已经是最高级别。这是层次查询的起点 CONNECT BY PRIOR empno= mgr;

新的递归WITH写法:

sql;"> WITH T(empno,the_level,path,top_manager) AS ( ---- 必须把结构写出来 SELECT empno,deptno ---- 先写锚点查询，用START WITH的条件,1 AS the_level ---- 递归起点，第一层,'\'||ename ---- 路径的第一截,ename AS top_manager ---- 原来的CONNECT_BY_ROOT FROM EMP WHERE mgr IS NULL ---- 原来的START WITH条件 UNION ALL ---- 下面是递归部分 SELECT e.empno,e.ename,e.job,e.mgr,e.deptno ---- 要加入的新一层数据，来自要遍历的emp表,1 + t.the_level ---- 递归层次，在原来的基础上加1。这相当于CONNECT BY查询中的LEVEL伪列,t.path||'\'||e.ename ---- 把新的一截路径拼上去,t.top_manager ---- 直接继承原来的数据，因为每个路径的根节点只有一个 FROM t,emp e ---- 典型写法，把子查询本身和要遍历的表作一个连接 WHERE t.empno = e.mgr ---- 原来的CONNECT BY条件 ) ---- WITH定义结束 SELECT * FROM T ;

查询结果：

sql;"> EMPNO ENAME JOB MGR DEPTNO THE_LEVEL PATH TOP_MANAGE ------ ---------- --------- ------ ------- ---------- -------------------------- ---------- 7839 KING PRESIDENT 10 1 \KING KING 7566 JONES MANAGER 7839 20 2 \KING\JONES KING 7698 BLAKE MANAGER 7839 30 2 \KING\BLAKE KING 7782 CLARK MANAGER 7839 10 2 \KING\CLARK KING 7499 ALLEN SALESMAN 7698 30 3 \KING\BLAKE\ALLEN KING 7521 WARD SALESMAN 7698 30 3 \KING\BLAKE\WARD KING 7654 MARTIN SALESMAN 7698 30 3 \KING\BLAKE\MARTIN KING 7788 SCOTT ANALYST 7566 20 3 \KING\JONES\SCOTT KING 7844 TURNER SALESMAN 7698 30 3 \KING\BLAKE\TURNER KING 7900 JAMES CLERK 7698 30 3 \KING\BLAKE\JAMES KING 7902 FORD ANALYST 7566 20 3 \KING\JONES\FORD KING 7934 MILLER CLERK 7782 10 3 \KING\CLARK\MILLER KING 7369 SMITH CLERK 7902 20 4 \KING\JONES\FORD\SMITH KING 7876 ADAMS CLERK 7788 20 4 \KING\JONES\SCOTT\ADAMS KING

14 rows selected.

从结果集的THE_LEVEL和PATH列可以清楚地看到数据是如何被一层一层叠加上去的。

例2:

构造等差数列：

CONNECT BY写法：

这是一个非常特殊的用法，因为没有上下级关系，只有遍历的终止条件。像这类CONNECT BY我强烈推荐在只有一行的结果集上运行（比如FROM DUAL,比如从一个聚合后的子查询），在多行的集合上运行比较难以控制，头脑必须很清醒。

（以下ROWNUM全部可以改成 LEVEL，效果一样）：

sql;"> SELECT ROWNUM n,ROWNUM*2 n2,DATE '2010-1-1'+ROWNUM-1 dt,ADD_MONTHS(DATE '2010-1-1',ROWNUM-1) mon FROM DUAL CONNECT BY ROWNUM<=10;

结果：

N N2 DT MON
---------- ---------- ----------- -----------
1 2 2010-01-01 2010-01-01
2 4 2010-01-02 2010-02-01
3 6 2010-01-03 2010-03-01
4 8 2010-01-04 2010-04-01
5 10 2010-01-05 2010-05-01
6 12 2010-01-06 2010-06-01
7 14 2010-01-07 2010-07-01
8 16 2010-01-08 2010-08-01
9 18 2010-01-09 2010-09-01
10 20 2010-01-10 2010-10-01

10 rows selected.

这个简洁优雅的写法最早由Mikito Harakiri(从名字看是个日本人)在asktom网站(nofollow" href="http://asktom.oracle.com">http://asktom.oracle.com)发表，现在已经风靡全世界的ORACLE社区。在这个方法被发现之前，一般采用的是从一个大的集合（表或视图）中获取ROWNUM的方法：

sql;"> SELECT ROWNUM n,ROWNUM-1) mon FROM ALL_OBJECTS ---- ALL_OBJECTS是个很大的系统视图，它包含的行数足够满足一般的序列构造 WHERE ROWNUM<=10;

下面尝试用递归WITH的写法：

sql;"> WITH t(n,n2,dt,mon) AS ( SELECT 1,2,TO_DATE('2010-1-1','YYYY-MM-DD'),'YYYY-MM-DD') FROM DUAL --- 先构造第一个 UNION ALL SELECT t.n+1 ---- 递增1,t.n2+2 ---- 递增2,dt+1 ---- 下一日,ADD_MONTHS(mon,1) ---- 下个月 FROM t ---- 没有任何连接，因为不需要，所有数据都可以从锚点成员中衍生出来 WHERE t.n<10 ) SELECT * FROM T;

一切都按规矩来，竟然还是出错了：

sql;">,1) ---- 下个月 * ERROR at line 6: ORA-01790: expression must have same datatype as corresponding expression

改为字符串型看看：

sql;"> WITH t(n,'2010-01-01','2010-01-01' FROM DUAL ---- 用字符串来表示日期 UNION ALL SELECT t.n+1 ---- 递增1,TO_CHAR(TO_DATE(t.dt,'YYYY-MM-DD')+1,'YYYY-MM-DD') ---- 先转换为日期型，计算后换回字符串型,TO_CHAR(ADD_MONTHS(TO_DATE(t.mon,1),'YYYY-MM-DD') ---- 计算下个月，方法同上 FROM t WHERE t.n<10 ) SELECT * FROM T;

我很惊奇地看到这个结果：

N N2 DT MON
---------- ---------- ---------- ----------
1 2 2010-01-01 2010-01-01
2 4 2009-12-31 2010-02-01 ----- DT竟然是递减的！
3 6 2009-12-30 2010-03-01
4 8 2009-12-29 2010-04-01
5 10 2009-12-28 2010-05-01
6 12 2009-12-27 2010-06-01
7 14 2009-12-26 2010-07-01
8 16 2009-12-25 2010-08-01
9 18 2009-12-24 2010-09-01
10 20 2009-12-23 2010-10-01

10 rows selected.

这是ORACEL 11.2.0.1.0版本的BUG，后续版本应该会改正。

没办法，只好想其他招数绕过去:

sql;"> WITH t(n) AS ( SELECT 1 FROM DUAL --- 先构造第一个 UNION ALL SELECT t.n+1 ---- 仅仅是整数序列 FROM t WHERE t.n<10 ) SELECT n,n*2 n2,DATE '2010-1-1'+n-1 dt ---- 在最终的查询中进行日期运算,n-1) mon FROM T;

这下子对了：

10 rows selected.

看来对日期的运算有BUG。解决办法就是先构造整数序列，然后在最终的查询中再利用这个整数序列来构造日期序列。

从一个单行结果集CONNECT BY的例子:

sql;"> SELECT ROWNUM rn,cnt FROM (SELECT COUNT(*) cnt FROM emp) ---- 经过聚合的只有一行的结果集 CONNECT BY ROWNUM<=cnt;

结果：

RN CNT
---------- ----------
1 14
2 14
3 14
4 14
5 14
6 14
7 14
8 14
9 14
10 14
11 14
12 14
13 14
14 14

14 rows selected.

递归WITH写法：

sql;"> WITH t(n,cnt) AS ( SELECT 1,COUNT(*) cnt FROM EMP --- 先构造第一个 UNION ALL SELECT t.n+1 ---- 递增1,t.cnt ---- 这个cnt列不做任何修改，从第一层得来 FROM t ---- 没有任何连接，因为不需要 WHERE t.n

结果同上（略）。

例3:

独立事件的排列组合：一个布袋中装有数量相同的四种颜色的小球。随机从布袋中取四次，每次取完都放回去。现在问四次结果总颜色数等于3的概率是多少？

传统的CONNECT BY写法：

sql;"> WITH t AS ( SELECT ROWNUM rn -- 先构造一个1,3,4的结果集，每个rn表示一种颜色 FROM DUAL CONNECT BY ROWNUM<=4 ),t2 AS ( ---- 集合t2模拟独立取四次的动作，最终结果会有4*4*4*4=256行 SELECT ROWNUM id ---- 构造唯一ID供下面拆分用,REPLACE(SYS_CONNECT_BY_PATH(rn,'@'),'@') path ---- 用一个特殊字符@来作分隔符,并在最后用REPLACE把它去除,COUNT(*) OVER() cnt ---- 利用分析函数算出总行数并把它作为一个列返回 FROM t ---- 这个是有四行的集合 WHERE LEVEL=4 ---- 我们需要的仅仅是最后一层的结果。在PATH里面已经包含了取四次的所有结果组合 CONNECT BY LEVEL<=4 ---- 没有任何条件，前后都是独立的 ),t3 AS ( ---- 集合t3把t2中的PATH包含的颜色组合拆开为四行 SELECT id,cnt,SUBSTR(PATH,rn,1) color FROM t2,t ---- 笛卡儿积，用于把t2中的一行变为四行 ) SELECT COUNT(COUNT(*))/MAX(cnt) AS prob FROM t3 GROUP BY id,cnt HAVING COUNT(DISTINCT color)=3 --- 每一个id中包含三种颜色 ;

结果：

PROB
----------
.5625

这个例子展示了CONNECT BY来模拟排列组合的技巧。每一层遍历表示一次抽取的动作，因为每次都是完全独立的，在CONNECT BY 里面仅仅限制了抽取次数（遍历层数）而没有其他条件。SYS_CONNECT_BY_PATH可以把截至当前为止所访问到的各层次的数据串起来，在LEVEL=N就包含了前N层的排列组合情况。你可以用这个查询来看看中间生成的结果集t2：

sql;"> WITH t AS ( SELECT ROWNUM rn -- 先构造一个1,COUNT(*) OVER() cnt ---- 利用分析函数算出总行数并把它作为一个列返回 FROM t ---- 这个是有四行的集合 WHERE LEVEL=4 ---- 我们需要的仅仅是最后一层的结果。在PATH里面已经包含了取四次的所有结果组合 CONNECT BY LEVEL<=4 ---- 没有任何条件，前后都是独立的 ) SELECT * FROM t2;

ID PATH CNT
---------- ---------- ----------
1 1111 256
2 1112 256
3 1113 256
4 1114 256
5 1121 256
6 1122 256
7 1123 256
8 1124 256
9 1131 256
10 1132 256
11 1133 256
......(其余结果略)

256 rows selected.

由此看到PATH列已经包含了四次抽取的所有可能结果，每个结果都被赋予一个唯一的编号ID。

如果你好奇的话可以看看下一步的结果集t3:

sql;"> WITH t AS ( SELECT ROWNUM rn -- 先构造一个1,t ---- 笛卡儿积，用于把t2中的一行变为四行 ) SELECT * FROM t3;

ID CNT COLO
---------- ---------- ----
1 256 1
1 256 1
1 256 1
1 256 1
2 256 1
2 256 1
2 256 1
2 256 2
3 256 1
3 256 1
3 256 1
3 256 3
4 256 1
4 256 1
4 256 1
4 256 4
......(其余结果略)

1024 rows selected.

可以看到t2集合中的每一行都被拆成了四行，这是为了后面的聚合运算。

最后看看算概率的主查询：

sql;"> SELECT COUNT(COUNT(*))/MAX(cnt) AS prob FROM t3 GROUP BY id,cnt HAVING COUNT(DISTINCT color)=3;

COUNT(DISTINCT color)可以算出每个ID中包含不重复的颜色数目，放在HAVING中过滤了数目不为3的那些ID。

GROUP BY id,cnt 表示按照id来分组。因为所有行的cnt都是一样的(都等于256)，我们在分组加入它并不会改变分组的结果，加入cnt的目的是为了在查询中引用。
最后的连续两层COUNT函数的意思是要把分组结果再聚合为一行，算出满足条件的id的行数。除以cnt就得到了我们要的概率。

本例是一个在多行的结果集上进行无条件遍历的例子，前面说过了要特别小心，因为没有上下级关系，随着层数递增，数据量的增长十分可观。

递归WITH写法：

sql;"> WITH T AS ( SELECT ROWNUM rn -- 还是先构造一个1,4的结果集 FROM DUAL CONNECT BY ROWNUM<=4 ),t2(distinct_colors,lvl) AS ( --- 两个列：所有不重复颜色，层次 SELECT '\'||rn,1 ---- 第一层就是最基础的四种颜色的表 FROM t UNION ALL SELECT CASE WHEN INSTR(t2.distinct_colors||'\','\'||t.rn||'\')=0 --- 这个颜色没有出现过 THEN t2.distinct_colors||'\'||t.rn --- 拼上去 ELSE t2.distinct_colors ---- 颜色已经出现，保持原来的 END,t2.lvl+1 --- 层数递增 FROM t,t2 WHERE t2.lvl<4 --- 递归出口的条件：次数达到限制 ) SELECT COUNT(CASE WHEN LENGTH(distinct_colors) - LENGTH(REPLACE(distinct_colors,'\'))=3 THEN 1 END) --- 出现三个斜杠 /COUNT(*) FROM t2 WHERE lvl=4 ---- 同CONNECT BY类似，我们只需观察最后一层的数据，在这里面已经包含了所有层次的颜色 ;

在递归WITH子查询t2中，我们看到它用了一个CASE表达式把以前没出现过的颜色拼接到distinct_colors中。这个CASE是递归WITH的妙处，用SYS_CONNECT_BY_PATH没办法做到有条件的拼接。

而最后在计算颜色数的时候用了一个技巧，把颜色数转换为斜杠的个数，因为我们构造数据的时候每种颜色前面都带一个斜杠。为了求出字符串中某字符出现的次数，我们用了这样的办法：

先求出字符串的总长度；

用REPLACE函数从串中去除这个字符，然后再求一次长度；

两个长度之差就是被去除的字符个数。

CASE函数把出现满足条件的标记置为1,不满足则为NULL,那么再套一个COUNT函数就能算出满足条件的行数，因为NULL是不被COUNT计入的。

COUNT和CASE的嵌套使用，也是在聚合运算中常用的技巧。

这个颜色数的计算，我们也可以在递归的过程中进行有条件累加，这样最后就可以直接使用：

sql;"> WITH T AS ( SELECT ROWNUM rn -- 还是先构造一个1,lvl,distinct_colors_cnt) AS ( --- 两个列：所有不重复颜色，层次，不重复的颜色数 SELECT '\'||rn,1,t2.lvl+1 --- 层数递增,CASE WHEN INSTR(t2.distinct_colors||'\','\'||t.rn||'\')=0 --- 这个颜色没有出现过 THEN t2.distinct_colors_cnt + 1 --- 颜色数累加 ELSE t2.distinct_colors_cnt ---- 颜色已经出现，数目不变 END FROM t,t2 WHERE t2.lvl<4 --- 递归出口的条件：次数达到限制 ) SELECT COUNT(CASE WHEN distinct_colors_cnt=3 THEN 1 END) --- 出现三个斜杠 /COUNT(*) FROM t2 WHERE lvl=4 ---- 同CONNECT BY类似，我们只需观察最后一层的数据，在这里面已经包含了所有层次的颜色 ;

例4:

构造一个二阶等差数列：这个数列的各项之差是一个等差数列

比如：1,6,10,15,21,...

用CONNECT BY:

sql;"> SELECT LEVEL,SUM(LEVEL) OVER(ORDER BY LEVEL) n FROM DUAL CONNECT BY LEVEL<=10;

结果：

LEVEL N
---------- ----------
1 1
2 3
3 6
4 10
5 15
6 21
7 28
8 36
9 45
10 55

10 rows selected.

因为只有一条路径，所以用分析函数SUM很轻易做到了。

递归WITH写法：

sql;"> WITH t(lvl,n) AS ( SELECT 1,1 FROM DUAL --- 先构造第一个 UNION ALL SELECT t.lvl+1,t.lvl+1+t.n ---- n的增幅本身是一个等差数列，即新的t.lvl FROM t ---- 没有任何连接，因为不需要 WHERE t.lvl<10 ---- 找到10个就停止 ) SELECT * FROM T;

结果：

LVL N
---------- ----------
1 1
2 3
3 6
4 10
5 15
6 21
7 28
8 36
9 45
10 55
10 rows selected.

例5:

构造斐波那契数列: 指的是这样一个数列,从第三项开始，每一项都等于前两项之和。
1,5,8,13,......

传统的CONNECT BY方法做不出来，但是用10G以上所支持的MODEL可以轻松构造：

sql;"> 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),n[cv()-2]+n[cv()-1]) ---- 用DECODE构造最初的两个，其余的则赋值为最近两项之和 ) /

RN N
---------- ----------
1 1
2 1
3 2
4 3
5 5
6 8
7 13
8 21
9 34
10 55

10 rows selected.

用递归WITH的写法：

sql;"> WITH t(n,last_n,1 FROM DUAL --- 先构造第一个 UNION ALL SELECT t.n+t.last_n,t.n,t.cnt+1 ---- 前两项之和 FROM t ---- 没有任何连接，因为不需要 WHERE t.cnt<10 ---- 找到10个就停止 ) SELECT n FROM T;

N
----------
1
1
2
3
5
8
13
21
34
55

10 rows selected.

例6:

排列组合:

从5个数中取3个的所有组合C(3,5):

CONNECT BY写法：

sql;"> SELECT SYS_CONNECT_BY_PATH(rn,',') xmlpath FROM (SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL<6) WHERE LEVEL=3 CONNECT BY rn

XMLPATH
--------------
,4,3
,2
,1
,1

递归WITH写法：

sql;"> WITH t AS ( SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL<6 ),t2(rn,xmlpath,lvl) AS ( ---- 三个列：当前节点值，路径，层数 SELECT rn,'||rn,1 FROM t ---- 先构造锚点成员的基础数据，就是上面生成的6行数据的集合 UNION ALL SELECT t.rn,t2.xmlpath||','||t.rn,t2.lvl+1 --- 把当前节点拼接入路径，层数则递增 FROM t2,t WHERE t2.rn

XMLPATH
-----------
,4
,5
,5

10 rows selected.

如果要的不是组合而是排列，比如P(3,5)可以这么写：

sql;"> 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
----------
,4
......(其余结果略)

60 rows selected.

和刚才的组合写法相比，rnPRIOR rn,这表示只要rn没出现过就行，我们要的是所有的排列顺序而不仅仅是降序。注意这里面的NOCYCLE,这个是10G上才有的。

如果不写这个NOCYCLE会怎么样？

sql;"> SELECT SYS_CONNECT_BY_PATH(rn,') xmlpath FROM (SELECT ROWNUM rn FROM DUAL CONNECT BY LEVEL<6) WHERE LEVEL=3 CONNECT BY rn<>PRIOR rn AND LEVEL<=3;

ERROR:
ORA-01436: CONNECT BY loop in user data

可以看到，这个NOCYCLE是很重要的，ORACLE不允许遍历顺序中出现循环。

在递归WITH中，NOCYCLE的写法：

sql;"> WITH t AS ( SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL<6 ),T2(rn,t WHERE t2.rn<>t.rn AND t2.lvl<3 ) CYCLE rn SET cycle_flag TO 'Y' DEFAULT 'N' ---- 这个cycle_flag是自己定义的伪列名和值，可以起到CONNECT_BY_ISCYCLE同样的作用 SELECT xmlpath FROM t2 WHERE lvl=3 AND cycle_flag='N';

结果：

XMLPA
sql> with emp_data(ename,l)
2 as
3 (select ename,1 lvl from emp where mgr is null
4 union all
5 select emp.ename,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('*','*')||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标准的一部分。ORACLE从9i开始引入WITH子查询，把它被称作SUBQUERY FACTORING(分解子查询)。

从版本11GR2开始，ORACLE支持递归的WITH,Postgresql 都先于ORACLE支持这一特性。但对于ORACLE用户来说，这一递归特性还是很令人期待的，利用它可以轻易实现以往做不到的、或者很难做到的许多新功能。这一章我们就来探索这一令人兴奋的新特性，并把它和以往的实现手段（主要是CONNECT BY层次查询）作比较。

我们先来看看这个递归WITH子查询的语法：

WITH

① query_name ([c_alias [,c_alias]...]) AS (subquery) [search_clause] [cycle_clause]]...

subquery部分由两个成员组成：anchor member(锚点成员) 和 recursive member(递归成员)。它们之间必须用union all联合起来，anchor member 必须写在recursive member前面。

anchor member用来定位递归的入口，锚点成员是一个SELECT语句，它不可以包含自身名称(query_name)。这相当于CONNECT BY查询中的START WITH，典型写法就是:

SELECT ... FROM 要遍历的表 WHERE ... (起始条件)

搜索子句(search_clause)和循环子句(cycle_clause)我们后面的例子中会见到。

下面我们就来看看递归WITH子查询的用法实例。

例1:

先来一个简单例子，从scott/tiger的emp表来查找上下级关系：

传统的CONNECT BY写法：

sql;"> SELECT empno,CONNECT_BY_ROOT(ename) AS top_manager FROM EMP START WITH mgr IS NULL -- mgr列为空，表示没有上级，该员工已经是最高级别。这是层次查询的起点 CONNECT BY PRIOR empno= mgr;

新的递归WITH写法:

sql;"> WITH T(empno,emp e ---- 典型写法，把子查询本身和要遍历的表作一个连接 WHERE t.empno = e.mgr ---- 原来的CONNECT BY条件 ) ---- WITH定义结束 SELECT * FROM T ;

查询结果：

EMPNO ENAME JOB MGR DEPTNO THE_LEVEL PATH TOP_MANAGE
------ ---------- --------- ------ ------- ---------- -------------------------- ----------
7839 KING PRESIDENT 10 1 \KING KING
7566 JONES MANAGER 7839 20 2 \KING\JONES KING
7698 BLAKE MANAGER 7839 30 2 \KING\BLAKE KING
7782 CLARK MANAGER 7839 10 2 \KING\CLARK KING
7499 ALLEN SALESMAN 7698 30 3 \KING\BLAKE\ALLEN KING
7521 WARD SALESMAN 7698 30 3 \KING\BLAKE\WARD KING
7654 MARTIN SALESMAN 7698 30 3 \KING\BLAKE\MARTIN KING
7788 SCOTT ANALYST 7566 20 3 \KING\JONES\SCOTT KING
7844 TURNER SALESMAN 7698 30 3 \KING\BLAKE\TURNER KING
7900 JAMES CLERK 7698 30 3 \KING\BLAKE\JAMES KING
7902 FORD ANALYST 7566 20 3 \KING\JONES\FORD KING
7934 MILLER CLERK 7782 10 3 \KING\CLARK\MILLER KING
7369 SMITH CLERK 7902 20 4 \KING\JONES\FORD\SMITH KING
7876 ADAMS CLERK 7788 20 4 \KING\JONES\SCOTT\ADAMS KING

14 rows selected.

从结果集的THE_LEVEL和PATH列可以清楚地看到数据是如何被一层一层叠加上去的。

例2:

构造等差数列：

CONNECT BY写法：

sql;"> （以下ROWNUM全部可以改成 LEVEL，效果一样）： SELECT ROWNUM n,ROWNUM-1) mon FROM ALL_OBJECTS ---- ALL_OBJECTS是个很大的系统视图，它包含的行数足够满足一般的序列构造 WHERE ROWNUM<=10;

下面尝试用递归WITH的写法：

sql;"> WITH t(n,1) ---- 下个月 FROM t ---- 没有任何连接，因为不需要，所有数据都可以从锚点成员中衍生出来 WHERE t.n<10 ) SELECT * FROM T; 一切都按规矩来，竟然还是出错了：,1) ---- 下个月 * ERROR at line 6: ORA-01790: expression must have same datatype as corresponding expression

改为字符串型看看：

sql;"> WITH t(n,'YYYY-MM-DD') ---- 计算下个月，方法同上 FROM t WHERE t.n<10 ) SELECT * FROM T;

我很惊奇地看到这个结果：
N N2 DT MON
---------- ---------- ---------- ----------
1 2 2010-01-01 2010-01-01
2 4 2009-12-31 2010-02-01 ----- DT竟然是递减的！
3 6 2009-12-30 2010-03-01
4 8 2009-12-29 2010-04-01
5 10 2009-12-28 2010-05-01
6 12 2009-12-27 2010-06-01
7 14 2009-12-26 2010-07-01
8 16 2009-12-25 2010-08-01
9 18 2009-12-24 2010-09-01
10 20 2009-12-23 2010-10-01

10 rows selected.

这是ORACEL 11.2.0.1.0版本的BUG，后续版本应该会改正。

没办法，只好想其他招数绕过去:

sql;"> WITH t(n) AS ( SELECT 1 FROM DUAL --- 先构造第一个 UNION ALL SELECT t.n+1 ---- 仅仅是整数序列 FROM t WHERE t.n<10 ) SELECT n,n-1) mon FROM T;

这下子对了：

10 rows selected.

看来对日期的运算有BUG。解决办法就是先构造整数序列，然后在最终的查询中再利用这个整数序列来构造日期序列。

从一个单行结果集CONNECT BY的例子:

sql;"> SELECT ROWNUM rn,cnt FROM (SELECT COUNT(*) cnt FROM emp) ---- 经过聚合的只有一行的结果集 CONNECT BY ROWNUM<=cnt;

结果：

RN CNT
---------- ----------
1 14
2 14
3 14
4 14
5 14
6 14
7 14
8 14
9 14
10 14
11 14
12 14
13 14
14 14
14 rows selected.

递归WITH写法：

sql;"> WITH t(n,t.cnt ---- 这个cnt列不做任何修改，从第一层得来 FROM t ---- 没有任何连接，因为不需要 WHERE t.n

结果同上（略）。

例3:

传统的CONNECT BY写法：

sql;"> WITH t AS ( SELECT ROWNUM rn -- 先构造一个1,cnt HAVING COUNT(DISTINCT color)=3 --- 每一个id中包含三种颜色 ;

结果：

PROB
----------
.5625

ID PATH CNT
---------- ---------- ----------
1 1111 256
2 1112 256
3 1113 256
4 1114 256
5 1121 256
6 1122 256
7 1123 256
8 1124 256
9 1131 256
10 1132 256
11 1133 256
......(其余结果略)
256 rows selected.

由此看到PATH列已经包含了四次抽取的所有可能结果，每个结果都被赋予一个唯一的编号ID。

如果你好奇的话可以看看下一步的结果集t3:

sql;"> WITH t AS ( SELECT ROWNUM rn -- 先构造一个1,'@') path ---- 用一个特殊字符@来作分隔符,COUNT(*) OVER() cnt ---- 利用分析函数算出总行数并把它作为一个列返回 FROM t ---- 这个是有四行的集合 WHERE LEVEL=4 ---- 我们需要的仅仅是最后一层的结果。在PATH里面已经包含了取四次的所有结果组合 CONNECT BY LEVEL<=4 ---- 没有任何条件，前后都是独立的 ),t ---- 笛卡儿积，用于把t2中的一行变为四行 ) SELECT * FROM t3;

ID CNT COLO
---------- ---------- ----
1 256 1
1 256 1
1 256 1
1 256 1
2 256 1
2 256 1
2 256 1
2 256 2
3 256 1
3 256 1
3 256 1
3 256 3
4 256 1
4 256 1
4 256 1
4 256 4
......(其余结果略)
1024 rows selected.

可以看到t2集合中的每一行都被拆成了四行，这是为了后面的聚合运算。

最后看看算概率的主查询：

sql;"> SELECT COUNT(COUNT(*))/MAX(cnt) AS prob FROM t3 GROUP BY id,cnt 表示按照id来分组。因为所有行的cnt都是一样的(都等于256)，我们在分组加入它并不会改变分组的结果，加入cnt的目的是为了在查询中引用。

最后的连续两层COUNT函数的意思是要把分组结果再聚合为一行，算出满足条件的id的行数。除以cnt就得到了我们要的概率。

本例是一个在多行的结果集上进行无条件遍历的例子，前面说过了要特别小心，因为没有上下级关系，随着层数递增，数据量的增长十分可观。

递归WITH写法：

sql;"> WITH T AS ( SELECT ROWNUM rn -- 还是先构造一个1,1 ---- 第一层就是最基础的四种颜色的表 FROM t UNION ALL SELECT CASE WHEN INSTR(t2.distinct_colors||'\','\'||t.rn||'\')=0 --- 这个颜色没有出现过 THEN t2.distinct_colors||'\'||t.rn --- 拼上去 ELSE t2.distinct_colors ---- 颜色已经出现，保持原来的 END,t2.lvl+1 --- 层数递增 FROM t,'\'))=3 THEN 1 END) --- 出现三个斜杠 /COUNT(*) FROM t2 WHERE lvl=4 ---- 同CONNECT BY类似，我们只需观察最后一层的数据，在这里面已经包含了所有层次的颜色 ;

先求出字符串的总长度；

用REPLACE函数从串中去除这个字符，然后再求一次长度；

两个长度之差就是被去除的字符个数。

CASE函数把出现满足条件的标记置为1,那么再套一个COUNT函数就能算出满足条件的行数，因为NULL是不被COUNT计入的。

COUNT和CASE的嵌套使用，也是在聚合运算中常用的技巧。

这个颜色数的计算，我们也可以在递归的过程中进行有条件累加，这样最后就可以直接使用：

sql;"> WITH T AS ( SELECT ROWNUM rn -- 还是先构造一个1,'\'||t.rn||'\')=0 --- 这个颜色没有出现过 THEN t2.distinct_colors_cnt + 1 --- 颜色数累加 ELSE t2.distinct_colors_cnt ---- 颜色已经出现，数目不变 END FROM t,t2 WHERE t2.lvl<4 --- 递归出口的条件：次数达到限制 ) SELECT COUNT(CASE WHEN distinct_colors_cnt=3 THEN 1 END) --- 出现三个斜杠 /COUNT(*) FROM t2 WHERE lvl=4 ---- 同CONNECT BY类似，我们只需观察最后一层的数据，在这里面已经包含了所有层次的颜色 ;

例4:

构造一个二阶等差数列：这个数列的各项之差是一个等差数列

比如：1,SUM(LEVEL) OVER(ORDER BY LEVEL) n FROM DUAL CONNECT BY LEVEL<=10;

结果：

LEVEL N
---------- ----------
1 1
2 3
3 6
4 10
5 15
6 21
7 28
8 36
9 45
10 55

10 rows selected.

因为只有一条路径，所以用分析函数SUM很轻易做到了。

递归WITH写法：

sql;"> WITH t(lvl,t.lvl+1+t.n ---- n的增幅本身是一个等差数列，即新的t.lvl FROM t ---- 没有任何连接，因为不需要 WHERE t.lvl<10 ---- 找到10个就停止 ) SELECT * FROM T;

结果：

LVL N
---------- ----------
1 1
2 3
3 6
4 10
5 15
6 21
7 28
8 36
9 45
10 55
10 rows selected.

例5:

构造斐波那契数列: 指的是这样一个数列,从第三项开始，每一项都等于前两项之和。

1,......

传统的CONNECT BY方法做不出来，但是用10G以上所支持的MODEL可以轻松构造：

/
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的写法：

sql;"> WITH t(n,t.cnt+1 ---- 前两项之和 FROM t ---- 没有任何连接，因为不需要 WHERE t.cnt<10 ---- 找到10个就停止 ) SELECT n FROM T;

N
----------
1
1
2
3
5
8
13
21
34
55
10 rows selected.

例6:

排列组合:

从5个数中取3个的所有组合C(3,5):

CONNECT BY写法：

sql;"> SELECT SYS_CONNECT_BY_PATH(rn,1

递归WITH写法：

sql;"> WITH t AS ( SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL<6 ),5
10 rows selected.

如果要的不是组合而是排列，比如P(3,') xmlpath FROM (SELECT ROWNUM rn FROM DUAL CONNECT BY LEVEL<6) WHERE LEVEL=3 CONNECT BY rn<>PRIOR rn AND LEVEL<=3;

ERROR:

ORA-01436: CONNECT BY loop in user data

可以看到，这个NOCYCLE是很重要的，ORACLE不允许遍历顺序中出现循环。

在递归WITH中，NOCYCLE的写法：

sql;"> WITH t AS ( SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL<6 ),t WHERE t2.rn<>t.rn AND t2.lvl<3 ) CYCLE rn SET cycle_flag TO 'Y' DEFAULT 'N' ---- 这个cycle_flag是自己定义的伪列名和值，可以起到CONNECT_BY_ISCYCLE同样的作用 SELECT xmlpath FROM t2 WHERE lvl=3 AND cycle_flag='N';

结果：

XMLPA

以上所述是小编给大家介绍的Oracle 11GR2的递归WITH子查询方法，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对编程之家网站的支持！

原文链接：https://www.f2er.com/oracle/64948.html

11g 11g oracle oracle with 递归查询

上一篇：oracle数据库密码到期怎么解决下一篇：oracle 12c创建可插拔数据库（PDB）

Oracle 11GR2的递归WITH子查询方法

猜你在找的Oracle相关文章