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 ([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写法：

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  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，效果一样）：

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网站()发表，现在已经风靡全世界的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-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.

看来对日期的运算有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[any] order by rn=decode(cv(rn),1,1,2,1, 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的写法：

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 ([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写法：

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          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，效果一样）：
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网站()发表，现在已经风靡全世界的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-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.

看来对日期的运算有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[any] order by rn=decode(cv(rn),1,1,2,1, 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的写法：

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子查询方法，希望对大家有所帮助