Oracle 11GR2的递归WITH子查询方法
下面给大家详细介绍Oracle 11GR2的递归WITH子查询方法，具体内容如下所示：SQL> with emp_data(ename,empno,mgr,l)
as
(select ename, empno, mgr, 1 lvl from emp where mgr is null
union all
select emp.ename, emp.empno, emp.mgr, ed.l+1
from emp, emp_data ed
where emp.mgr = ed.empno
)
SEARCH DEPTH FIRST BY ename SET order_by
select l,
lpad('*' ,2*l, '*')||ename nm
from emp_data
order by order_by
/L NM
-------------------
1 **KING
2 ****BLAKE
3 ******ALLEN
3 ******JAMES
3 ******MARTIN
3 ******TURNER
3 ******WARD
2 ****CLARK
3 ******MILLER
2 ****JONES
3 ******FORD
4 ********SMITH
3 ******SCOTT
4 ********ADAMS
14 rows selected.
不知道真用起来怎么样，按我的想象可以比原来的SYS_CONNECT_BY_PATH多玩出很多新花样，比如按路径累加，更灵活的剪枝条件，
WITH子查询也称为CTE (Common Table Expression)，是ANSI SQL-99标准的一部分。ORACLE从9i开始引入WITH子查询，把它被称作SUBQUERY FACTORING(分解子查询)。
WITH子查询的作用类似于内联视图(INLINE VIEW)。内联视图的定义写作SQL的FROM 后面，只能够引用一次；而WITH子查询需要在引用之前先定义，一旦定义了在整个查询的后续部分就可以按名称来反复引用，从这点来看又很像临时表。
从版本11GR2开始，ORACLE支持递归的WITH, 即允许在WITH子查询的定义中对自身引用。这不是什么新鲜事，其他数据库如DB2, Firebird, Microsoft SQL Server, PostgreSQL 都先于ORACLE支持这一特性。但对于ORACLE用户来说，这一递归特性还是很令人期待的，利用它可以轻易实现以往做不到的、或者很难做到的许多新功能。这一章我们就来探索这一令人兴奋的新特性，并把它和以往的实现手段（主要是CONNECT BY层次查询）作比较。
我们先来看看这个递归WITH子查询的语法：
WITH
①query_name (...])
②AS (subquery)
③
④
⑤[,query_name (...]) AS (subquery) ]...
①这是子查询的名称，和以往不同的是，必须在括号中把这个子查询的所有列名写出来。
②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写法：
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列为空，表示没有上级，该员工已经是最高级别。这是层次查询的起点
CONNECT BY PRIOR empno= mgr;新的递归WITH写法:
WITH T(empno, ename, job, mgr, deptno, the_level, path,top_manager) AS ( ---- 必须把结构写出来
SELECT empno, ename, job, mgr, deptno ---- 先写锚点查询，用START WITH的条件
,1 AS the_level ---- 递归起点，第一层
,'\'||ename ---- 路径的第一截
,ename AS top_manager ---- 原来的CONNECT_BY_ROOT
FROM EMP
WHERE mgr IS NULL ---- 原来的START WITH条件
UNION ALL ---- 下面是递归部分
SELECT e.empno, e.ename, e.job, e.mgr, e.deptno ---- 要加入的新一层数据，来自要遍历的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
;查询结果：
EMPNO ENAME JOBMGR DEPTNO THE_LEVEL PATH TOP_MANAGE
------ ---------- --------- ------ ------- ---------- -------------------------- ----------
7839 KING PRESIDENT101 \KING KING
7566 JONES MANAGER 7839 202 \KING\JONESKING
7698 BLAKE MANAGER 7839 302 \KING\BLAKEKING
7782 CLARK MANAGER 7839 102 \KING\CLARKKING
7499 ALLEN SALESMAN 7698 303 \KING\BLAKE\ALLENKING
7521 WARD SALESMAN 7698 303 \KING\BLAKE\WARDKING
7654 MARTIN SALESMAN 7698 303 \KING\BLAKE\MARTINKING
7788 SCOTT ANALYST 7566 203 \KING\JONES\SCOTTKING
7844 TURNER SALESMAN 7698 303 \KING\BLAKE\TURNERKING
7900 JAMES CLERK 7698 303 \KING\BLAKE\JAMESKING
7902 FORD ANALYST 7566 203 \KING\JONES\FORDKING
7934 MILLER CLERK 7782 103 \KING\CLARK\MILLERKING
7369 SMITH CLERK 7902 204 \KING\JONES\FORD\SMITH KING
7876 ADAMS CLERK 7788 204 \KING\JONES\SCOTT\ADAMS KING14 rows selected.
从结果集的THE_LEVEL和PATH列可以清楚地看到数据是如何被一层一层叠加上去的。
例2:
构造等差数列：
CONNECT BY写法：
这是一个非常特殊的用法，因为没有上下级关系，只有遍历的终止条件。像这类CONNECT BY我强烈推荐在只有一行的结果集上运行（比如FROM DUAL, 比如从一个聚合后的子查询），在多行的集合上运行比较难以控制，头脑必须很清醒。
（以下ROWNUM全部可以改成 LEVEL，效果一样）：
SELECT ROWNUM n
,ROWNUM*2 n2
,DATE '2010-1-1'+ROWNUM-1 dt
,ADD_MONTHS(DATE '2010-1-1', ROWNUM-1) mon
FROM DUAL
CONNECT BY ROWNUM<=10;结果：
N N2 DT MON
---------- ---------- ----------- -----------
1 2 2010-01-012010-01-01
2 4 2010-01-022010-02-01
3 6 2010-01-032010-03-01
4 8 2010-01-042010-04-01
5 10 2010-01-052010-05-01
6 12 2010-01-062010-06-01
7 14 2010-01-072010-07-01
8 16 2010-01-082010-08-01
9 18 2010-01-092010-09-01
10 20 2010-01-102010-10-01
10 rows selected.
这个简洁优雅的写法最早由Mikito Harakiri(从名字看是个日本人)在asktom网站(https://asktom.oracle.com)发表，现在已经风靡全世界的ORACLE社区。在这个方法被发现之前，一般采用的是从一个大的集合（表或视图）中获取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是个很大的系统视图，它包含的行数足够满足一般的序列构造
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 --- 先构造第一个
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;一切都按规矩来，竟然还是出错了：
,ADD_MONTHS(mon,1) ---- 下个月
*
ERROR at line 6:
ORA-01790: expression must have same datatype as corresponding expression改为字符串型看看：
WITH t(n,n2,dt,mon) AS (
SELECT 1, 2,'2010-01-01','2010-01-01' FROM DUAL ---- 用字符串来表示日期
UNION ALL
SELECT t.n+1 ---- 递增1
,t.n2+2 ---- 递增2
,TO_CHAR(TO_DATE(t.dt,'YYYY-MM-DD')+1,'YYYY-MM-DD') ---- 先转换为日期型，计算后换回字符串型
,TO_CHAR(ADD_MONTHS(TO_DATE(t.mon,'YYYY-MM-DD'),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，后续版本应该会改正。
没办法，只好想其他招数绕过去:
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 ---- 在最终的查询中进行日期运算
,ADD_MONTHS(DATE '2010-1-1', n-1) mon
FROM T;这下子对了：
N N2 DT MON
---------- ---------- ----------- -----------
1 2 2010-01-012010-01-01
2 4 2010-01-022010-02-01
3 6 2010-01-032010-03-01
4 8 2010-01-042010-04-01
5 10 2010-01-052010-05-01
6 12 2010-01-062010-06-01
7 14 2010-01-072010-07-01
8 16 2010-01-082010-08-01
9 18 2010-01-092010-09-01
10 20 2010-01-102010-10-01
10 rows selected.
看来对日期的运算有BUG。解决办法就是先构造整数序列，然后在最终的查询中再利用这个整数序列来构造日期序列。
从一个单行结果集CONNECT BY的例子:
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写法：
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<t.cnt ---- 在这里看到cnt的作用，就是用于终止遍历
)
SELECT * FROM t;结果同上（略）。
例3:
独立事件的排列组合：一个布袋中装有数量相同的四种颜色的小球。随机从布袋中取四次，每次取完都放回去。现在问四次结果总颜色数等于3的概率是多少？
传统的CONNECT BY写法：
WITH t AS (
SELECT ROWNUM rn -- 先构造一个1,2,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：
WITH t AS (
SELECT ROWNUM rn -- 先构造一个1,2,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 ---- 没有任何条件，前后都是独立的
)
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:
WITH t AS (
SELECT ROWNUM rn -- 先构造一个1,2,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 * 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集合中的每一行都被拆成了四行，这是为了后面的聚合运算。
最后看看算概率的主查询：
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写法：
WITH T AS (
SELECT ROWNUM rn -- 还是先构造一个1,2,3,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的嵌套使用，也是在聚合运算中常用的技巧。
这个颜色数的计算，我们也可以在递归的过程中进行有条件累加，这样最后就可以直接使用：
WITH T AS (
SELECT ROWNUM rn -- 还是先构造一个1,2,3,4的结果集
FROM DUAL
CONNECT BY ROWNUM<=4
)
,t2(distinct_colors,lvl,distinct_colors_cnt) AS ( --- 两个列：所有不重复颜色，层次，不重复的颜色数
SELECT '\'||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 --- 层数递增
,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,3,6,10,15,21,...
用CONNECT BY:
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写法：
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,1,2,3,5,8,13,21,......
传统的CONNECT BY方法做不出来，但是用10G以上所支持的MODEL可以轻松构造：
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 order by rn=DECODE(cv(rn),1,1,2,1, n+n) ---- 用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的写法：
WITH t(n,last_n,cnt) AS (
SELECT 1,0,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写法：
SELECT SYS_CONNECT_BY_PATH(rn, ',') xmlpath
FROM (SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL<6)
WHERE LEVEL=3
CONNECT BY rn<PRIOR rn AND LEVEL<=3 ---- 强行按降序排序，这样就排除了其他相同的、只是顺序不同的组合
;XMLPATH
--------------
,5,4,3
,5,4,2
,5,4,1
,5,3,2
,5,3,1
,5,2,1
,4,3,2
,4,3,1
,4,2,1
,3,2,1
递归WITH写法：
WITH t AS (
SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL<6
)
,t2(rn,xmlpath,lvl) AS ( ---- 三个列：当前节点值，路径，层数
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<t.rn AND t2.lvl<3
)
SELECT xmlpath FROM t2 WHERE lvl=3;XMLPATH
-----------
,1,2,3
,1,2,4
,1,2,5
,1,3,4
,1,3,5
,1,4,5
,2,3,4
,2,3,5
,2,4,5
,3,4,5
10 rows selected.
如果要的不是组合而是排列，比如P(3,5)可以这么写：
SELECT SYS_CONNECT_BY_PATH(rn, ',') xmlpath
FROM (SELECT ROWNUM rn FROM DUAL CONNECT BY LEVEL<6)
WHERE LEVEL=3
CONNECT BY NOCYCLE rn<>PRIOR rn AND LEVEL<=3;XMLPATH
----------
,1,2,3
,1,2,4
,1,2,5
,1,3,2
,1,3,4
,1,3,5
,1,4,2
,1,4,3
,1,4,5
,1,5,2
,1,5,3
,1,5,4
,2,1,3
,2,1,4
......(其余结果略)
60 rows selected.
和刚才的组合写法相比，rn<PRIOR rn变成了NOCYCLE rn<>PRIOR rn, 这表示只要rn没出现过就行，我们要的是所有的排列顺序而不仅仅是降序。注意这里面的NOCYCLE, 这个是10G上才有的。
如果不写这个NOCYCLE会怎么样？
SELECT SYS_CONNECT_BY_PATH(rn, ',') xmlpath
FROM (SELECT ROWNUM rn FROM DUAL CONNECT BY LEVEL<6)
WHERE LEVEL=3
CONNECT BY rn<>PRIOR rn AND LEVEL<=3;ERROR:
ORA-01436: CONNECT BY loop in user data
可以看到，这个NOCYCLE是很重要的，ORACLE不允许遍历顺序中出现循环。
在递归WITH中，NOCYCLE的写法：
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<>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,empno,mgr,l)
2 as
3 (select ename, empno, mgr, 1 lvl from emp where mgr is null
4 union all
5 select emp.ename, emp.empno, emp.mgr, ed.l+1
6 from emp, emp_data ed
7 where emp.mgr = ed.empno
8 )
9 SEARCH DEPTH FIRST BY ename SET order_by
10 select l,
11 lpad('*' ,2*l, '*')||ename nm
12 from emp_data
13 order by order_by
14 /
L NM
-------------------
1 **KING
2 ****BLAKE
3 ******ALLEN
3 ******JAMES
3 ******MARTIN
3 ******TURNER
3 ******WARD
2 ****CLARK
3 ******MILLER
2 ****JONES
3 ******FORD
4 ********SMITH
3 ******SCOTT
4 ********ADAMS
14 rows selected.
不知道真用起来怎么样，按我的想象可以比原来的SYS_CONNECT_BY_PATH多玩出很多新花样，比如按路径累加，更灵活的剪枝条件，
WITH子查询也称为CTE (Common Table Expression)，是ANSI SQL-99标准的一部分。ORACLE从9i开始引入WITH子查询，把它被称作SUBQUERY FACTORING(分解子查询)。
WITH子查询的作用类似于内联视图(INLINE VIEW)。内联视图的定义写作SQL的FROM 后面，只能够引用一次；而WITH子查询需要在引用之前先定义，一旦定义了在整个查询的后续部分就可以按名称来反复引用，从这点来看又很像临时表。
从版本11GR2开始，ORACLE支持递归的WITH, 即允许在WITH子查询的定义中对自身引用。这不是什么新鲜事，其他数据库如DB2, Firebird, Microsoft SQL Server, PostgreSQL 都先于ORACLE支持这一特性。但对于ORACLE用户来说，这一递归特性还是很令人期待的，利用它可以轻易实现以往做不到的、或者很难做到的许多新功能。这一章我们就来探索这一令人兴奋的新特性，并把它和以往的实现手段（主要是CONNECT BY层次查询）作比较。
我们先来看看这个递归WITH子查询的语法：
WITH
①query_name (...])
②AS (subquery)
③
④
⑤[,query_name (...]) AS (subquery) ]...
①这是子查询的名称，和以往不同的是，必须在括号中把这个子查询的所有列名写出来。
②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写法：
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列为空，表示没有上级，该员工已经是最高级别。这是层次查询的起点
CONNECT BY PRIOR empno= mgr;新的递归WITH写法:
WITH T(empno, ename, job, mgr, deptno, the_level, path,top_manager) AS ( ---- 必须把结构写出来
SELECT empno, ename, job, mgr, deptno ---- 先写锚点查询，用START WITH的条件
,1 AS the_level ---- 递归起点，第一层
,'\'||ename ---- 路径的第一截
,ename AS top_manager ---- 原来的CONNECT_BY_ROOT
FROM EMP
WHERE mgr IS NULL ---- 原来的START WITH条件
UNION ALL ---- 下面是递归部分
SELECT e.empno, e.ename, e.job, e.mgr, e.deptno ---- 要加入的新一层数据，来自要遍历的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
;查询结果：
EMPNO ENAME JOB MGRDEPTNOTHE_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，效果一样）：
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-012010-01-01
2 4 2010-01-022010-02-01
3 6 2010-01-032010-03-01
4 8 2010-01-042010-04-01
5 10 2010-01-052010-05-01
6 12 2010-01-062010-06-01
7 14 2010-01-072010-07-01
8 16 2010-01-082010-08-01
9 18 2010-01-092010-09-01
10 20 2010-01-102010-10-01
10 rows selected.
这个简洁优雅的写法最早由Mikito Harakiri(从名字看是个日本人)在asktom网站(https://asktom.oracle.com)发表，现在已经风靡全世界的ORACLE社区。在这个方法被发现之前，一般采用的是从一个大的集合（表或视图）中获取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是个很大的系统视图，它包含的行数足够满足一般的序列构造
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 --- 先构造第一个
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;
一切都按规矩来，竟然还是出错了：
,ADD_MONTHS(mon,1) ---- 下个月
*
ERROR at line 6:
ORA-01790: expression must have same datatype as corresponding expression改为字符串型看看：
WITH t(n,n2,dt,mon) AS (
SELECT 1, 2,'2010-01-01','2010-01-01' FROM DUAL ---- 用字符串来表示日期
UNION ALL
SELECT t.n+1 ---- 递增1
,t.n2+2 ---- 递增2
,TO_CHAR(TO_DATE(t.dt,'YYYY-MM-DD')+1,'YYYY-MM-DD') ---- 先转换为日期型，计算后换回字符串型
,TO_CHAR(ADD_MONTHS(TO_DATE(t.mon,'YYYY-MM-DD'),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，后续版本应该会改正。
没办法，只好想其他招数绕过去:
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 ---- 在最终的查询中进行日期运算
,ADD_MONTHS(DATE '2010-1-1', n-1) mon
FROM T;这下子对了：
N N2 DT MON
---------- ---------- ----------- -----------
1 2 2010-01-012010-01-01
2 4 2010-01-022010-02-01
3 6 2010-01-032010-03-01
4 8 2010-01-042010-04-01
5 10 2010-01-052010-05-01
6 12 2010-01-062010-06-01
7 14 2010-01-072010-07-01
8 16 2010-01-082010-08-01
9 18 2010-01-092010-09-01
10 20 2010-01-102010-10-01
10 rows selected.
看来对日期的运算有BUG。解决办法就是先构造整数序列，然后在最终的查询中再利用这个整数序列来构造日期序列。
从一个单行结果集CONNECT BY的例子:
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写法：
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<t.cnt ---- 在这里看到cnt的作用，就是用于终止遍历
)
SELECT * FROM t;结果同上（略）。
例3:
独立事件的排列组合：一个布袋中装有数量相同的四种颜色的小球。随机从布袋中取四次，每次取完都放回去。现在问四次结果总颜色数等于3的概率是多少？
传统的CONNECT BY写法：
WITH t AS (
SELECT ROWNUM rn -- 先构造一个1,2,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：
WITH t AS (
SELECT ROWNUM rn -- 先构造一个1,2,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 ---- 没有任何条件，前后都是独立的
)
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:
WITH t AS (
SELECT ROWNUM rn -- 先构造一个1,2,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 * 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集合中的每一行都被拆成了四行，这是为了后面的聚合运算。
最后看看算概率的主查询：
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写法：
WITH T AS (
SELECT ROWNUM rn -- 还是先构造一个1,2,3,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的嵌套使用，也是在聚合运算中常用的技巧。
这个颜色数的计算，我们也可以在递归的过程中进行有条件累加，这样最后就可以直接使用：
WITH T AS (
SELECT ROWNUM rn -- 还是先构造一个1,2,3,4的结果集
FROM DUAL
CONNECT BY ROWNUM<=4
)
,t2(distinct_colors,lvl,distinct_colors_cnt) AS ( --- 两个列：所有不重复颜色，层次，不重复的颜色数
SELECT '\'||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 --- 层数递增
,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,3,6,10,15,21,...
用CONNECT BY:
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写法：
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,1,2,3,5,8,13,21,......
传统的CONNECT BY方法做不出来，但是用10G以上所支持的MODEL可以轻松构造：
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 order by rn=DECODE(cv(rn),1,1,2,1, n+n) ---- 用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的写法：
WITH t(n,last_n,cnt) AS (
SELECT 1,0,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写法：
SELECT SYS_CONNECT_BY_PATH(rn, ',') xmlpath
FROM (SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL<6)
WHERE LEVEL=3
CONNECT BY rn<PRIOR rn AND LEVEL<=3 ---- 强行按降序排序，这样就排除了其他相同的、只是顺序不同的组合
;XMLPATH
--------------
,5,4,3
,5,4,2
,5,4,1
,5,3,2
,5,3,1
,5,2,1
,4,3,2
,4,3,1
,4,2,1
,3,2,1
递归WITH写法：
WITH t AS (
SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL<6
)
,t2(rn,xmlpath,lvl) AS ( ---- 三个列：当前节点值，路径，层数
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<t.rn AND t2.lvl<3
)
SELECT xmlpath FROM t2 WHERE lvl=3;XMLPATH
-----------
,1,2,3
,1,2,4
,1,2,5
,1,3,4
,1,3,5
,1,4,5
,2,3,4
,2,3,5
,2,4,5
,3,4,5
10 rows selected.
如果要的不是组合而是排列，比如P(3,5)可以这么写：
SELECT SYS_CONNECT_BY_PATH(rn, ',') xmlpath
FROM (SELECT ROWNUM rn FROM DUAL CONNECT BY LEVEL<6)
WHERE LEVEL=3
CONNECT BY NOCYCLE rn<>PRIOR rn AND LEVEL<=3;XMLPATH
----------
,1,2,3
,1,2,4
,1,2,5
,1,3,2
,1,3,4
,1,3,5
,1,4,2
,1,4,3
,1,4,5
,1,5,2
,1,5,3
,1,5,4
,2,1,3
,2,1,4
......(其余结果略)
60 rows selected.
和刚才的组合写法相比，rn<PRIOR rn变成了NOCYCLE rn<>PRIOR rn, 这表示只要rn没出现过就行，我们要的是所有的排列顺序而不仅仅是降序。注意这里面的NOCYCLE, 这个是10G上才有的。
如果不写这个NOCYCLE会怎么样？
SELECT SYS_CONNECT_BY_PATH(rn, ',') xmlpath
FROM (SELECT ROWNUM rn FROM DUAL CONNECT BY LEVEL<6)
WHERE LEVEL=3
CONNECT BY rn<>PRIOR rn AND LEVEL<=3;ERROR:
ORA-01436: CONNECT BY loop in user data
可以看到，这个NOCYCLE是很重要的，ORACLE不允许遍历顺序中出现循环。
在递归WITH中，NOCYCLE的写法：
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<>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子查询方法，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对CodeAE代码之家网站的支持！
https://www.uoften.com/dbs/oracle/20180415/73714.html
页:
[1]