个人中心
gpt4 book ai didi

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

转载 作者：qq735679552 更新时间：2022-09-29 22:32:09 25 4
gpt4 key购买 nike

CFSDN坚持开源创造价值，我们致力于搭建一个资源共享平台，让每一个IT人在这里找到属于你的精彩世界.

这篇CFSDN的博客文章Oracle 11GR2的递归WITH子查询方法由作者收集整理，如果你对这篇文章有兴趣，记得点赞哟.

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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写法:

?
1
2
3
4
5
6
7
8
9
10
11
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写法

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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
;

查询结果:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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，效果一样）:

?
1
2
3
4
5
6
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网站(http://asktom.oracle.com)发表，现在已经风靡全世界的ORACLE社区。在这个方法被发现之前，一般采用的是从一个大的集合（表或视图）中获取ROWNUM的方法:

?
1
2
3
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的写法:

?
1
2
3
4
5
6
7
8
9
10
11
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;

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

?
1
2
3
4
,ADD_MONTHS(mon,1) ---- 下个月
   *
ERROR at line 6:
ORA-01790: expression must have same datatype as corresponding expression

改为字符串型看看:

?
1
2
3
4
5
6
7
8
9
10
11
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，后续版本应该会改正.

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

?
1
2
3
4
5
6
7
8
9
10
11
12
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的例子

?
1
2
3
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写法:

?
1
2
3
4
5
6
7
8
9
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写法:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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集合中的每一行都被拆成了四行，这是为了后面的聚合运算.

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

?
1
2
3
4
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写法:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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的嵌套使用，也是在聚合运算中常用的技巧.

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
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

?
1
2
3
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写法:

?
1
2
3
4
5
6
7
8
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可以轻松构造:

?
1
2
3
4
5
6
7
8
9
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的写法:

?
1
2
3
4
5
6
7
8
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写法:

?
1
2
3
4
5
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写法:

?
1
2
3
4
5
6
7
8
9
10
11
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)可以这么写:

?
1
2
3
4
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会怎么样?

?
1
2
3
4
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的写法:

?
1
2
3
4
5
6
7
8
9
10
11
12
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写法:

?
1
2
3
4
5
6
7
8
9
10
11
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写法

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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, 比如从一个聚合后的子查询），在多行的集合上运行比较难以控制，头脑必须很清醒.

?
1
2
3
4
5
6
7
（以下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网站(http://asktom.oracle.com)发表，现在已经风靡全世界的ORACLE社区。在这个方法被发现之前，一般采用的是从一个大的集合（表或视图）中获取ROWNUM的方法:

?
1
2
3
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的写法:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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

改为字符串型看看:

?
1
2
3
4
5
6
7
8
9
10
11
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，后续版本应该会改正.

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

?
1
2
3
4
5
6
7
8
9
10
11
12
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的例子

?
1
2
3
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写法:

?
1
2
3
4
5
6
7
8
9
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写法:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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集合中的每一行都被拆成了四行，这是为了后面的聚合运算.

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

?
1
2
3
4
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写法:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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的嵌套使用，也是在聚合运算中常用的技巧.

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
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

?
1
2
3
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写法:

?
1
2
3
4
5
6
7
8
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可以轻松构造:

?
1
2
3
4
5
6
7
8
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的写法:

?
1
2
3
4
5
6
7
8
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写法:

?
1
2
3
4
5
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写法:

?
1
2
3
4
5
6
7
8
9
10
11
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)可以这么写:

?
1
2
3
4
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会怎么样?

?
1
2
3
4
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的写法:

?
1
2
3
4
5
6
7
8
9
10
11
12
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子查询方法，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对我网站的支持！ 。

原文链接：http://blog.csdn.net/cacasi2568/article/details/55224422 。

最后此篇关于Oracle 11GR2的递归WITH子查询方法的文章就讲到这里了,如果你想了解更多关于Oracle 11GR2的递归WITH子查询方法的内容请搜索CFSDN的文章或继续浏览相关文章，希望大家以后支持我的博客！ 。

25 4 0
文章推荐： oracle中not exists对外层查询的影响详解
文章推荐： oracle 12c创建可插拔数据库（PDB）与用户详解
文章推荐： oracle数据库密码到期怎么解决
文章推荐： Oracle 11g数据库详细安装图文教程
qq735679552
个人简介

我是一名优秀的程序员,十分优秀！

滴滴打车优惠券免费领取
滴滴打车优惠券
全站热门文章
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com