Oracle 11GR2的递归WITH子查询 【转载】

admin PL/SQL 2017-09-23 241 次浏览 , , 没有评论

原文链接:http://www.itpub.net/thread-1225621-2-1.html

废话不多说,直接上例子感受一下:

with emps (employee_id, name, job_id, salary, lvl) as (  
  select employee_id, first_name || ', '|| last_name name, job_id, salary, 1 as lvl  
  from employees  
  where manager_id is null  
  union all  
  select emp.employee_id, emp.first_name || ', '|| emp.last_name, emp.job_id, emp.salary, root.lvl + 1  
  from employees emp, emps root  
  where emp.manager_id=root.employee_id  
)  
select * from emps;

该SQL的tkprof报告中的执行计划部分:

Rows     Row Source Operation  
-------  ---------------------------------------------------  
    108  VIEW  (cr=22 pr=0 pw=0 time=0 us cost=7 size=497 card=7)  
    108   UNION ALL (RECURSIVE WITH) BREADTH FIRST (cr=22 pr=0 pw=0 time=0 us)  
      1    TABLE ACCESS FULL EMPLOYEES (cr=8 pr=0 pw=0 time=0 us cost=3 size=36 card=1)  
    107    NESTED LOOPS  (cr=14 pr=0 pw=0 time=11232 us)  
    107     NESTED LOOPS  (cr=5 pr=0 pw=0 time=5061 us cost=4 size=372 card=6)  
    108      RECURSIVE WITH PUMP  (cr=0 pr=0 pw=0 time=728 us)  
    107      INDEX RANGE SCAN EMP_MANAGER_IX (cr=5 pr=0 pw=0 time=670 us cost=0 size=0 card=6)(object id 73958)  
    107     TABLE ACCESS BY INDEX ROWID EMPLOYEES (cr=9 pr=0 pw=0 time=0 us cost=1 size=216 card=6)

这个树状查询,如果不用递归,可以用connect_by语句实现:

select emp.employee_id, emp.first_name || ', '|| emp.last_name name, emp.job_id, emp.salary, level  
from employees emp  
start with manager_id is null  
connect by prior employee_id=manager_id;
Rows     Row Source Operation  
-------  ---------------------------------------------------  
    108  CONNECT BY NO FILTERING WITH START-WITH (cr=7 pr=0 pw=0 time=0 us)  
    108   TABLE ACCESS FULL EMPLOYEES (cr=7 pr=0 pw=0 time=749 us cost=3 size=3852 card=107)

Connect by 不是ANSI SQL, 递归With是。Connect by的逻辑读(cr)比递归with小,性能占优。

***************************************具体详细介绍************************************

Code Listing 3: New recursive subquery 

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网站(http://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-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';
结果:
XMLPATH
-----------
,1,2,3
,1,2,4
,1,2,5
,1,3,2
,1,3,4
,1,3,5
,1,4,2
,1,4,3
,1,4,5
,1,5,2
,1,5,3
,1,5,4
,2,1,3
,2,1,4
,2,1,5
......(其余结果略)
60 rows selected.

在这里我们看到了前面例子中没出现过的循环子句(CYCLE CLAUSE)。循环子句的语法如下:

① CYCLE c_alias [, c_alias]…   —- 用哪些列来判断是否发生CYCLE。一般来说就是要遍历的集合能够唯一标识出每行的那些列,例如主键。

②  SET cycle_mark_c_alias TO cycle_value  —- cycle_mark_c_alias如果发生CYCLE了要设置什么值,一般就是Y/N, 1/0 来表示是否CYCLE

③  DEFAULT no_cycle_value                 —- 没有CYCLE要设什么值

① 用哪些列来判断是否发生CYCLE。一般来说就是要遍历的集合能够唯一标识出每行的那些列,例如主键。

② cycle_mark_c_alias是用来存放CYCLE标记的列名。在本例中我们为它取名cycle_flag。

   cycle_value是当发生了循环(下一个节点已经被遍历过)的时候,要把什么值存到cycle_mark_c_alias列中。

   因为是布尔值,我们一般用Y/N, 1/0来表示。在本例中用的是'Y'。

③ 在没有循环的情况下要赋予cycle_mark_c_alia什么值。在本例中用的是'N'。

定义了循环子句之后,ORACLE碰到循环就会自动停止并为你指定的cycle_mark_c_alias列赋予一个标记。在随后的SELECT中可以用这个标记来过滤数据。

如果不写这个循环子句会怎么样?

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 
)
SELECT xmlpath FROM t2 WHERE lvl=3;
WITH t AS (
*
ERROR at line 1:
ORA-32044: cycle detected while executing recursive WITH query

假如在上述SQL的WHERE中加上层数限制,则错误不会出现,但是数据中有重复节点:

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 lvl<3
)
SELECT xmlpath FROM t2 WHERE lvl=3;
结果:
XMLPATH
------------
,1,2,1   ------ 可以看到1被访问了两次
,1,2,3
,1,2,4
,1,2,5
,1,3,1
,1,3,2
,1,3,4
,1,3,5
,1,4,1
,1,4,2
,1,4,3
,1,4,5
,1,5,1
,1,5,2
......(其余结果略)
80 rows selected.

那么这个问题不用循环子句能否解决呢?其实我们在前面的例3中已经用到了一个技巧,就?

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 AND INSTR(t2.xmlpath,t.rn)=0  -------新加入的节点必须是未出现过的
 ) SELECT xmlpath FROM t2 WHERE lvl=3;

例7:

求遍历树中的叶子节点。

在例1中我们看到一个求出所有员工的上级关系的层次查询。现在假设要把最下级的员工求出来:

CONNECT BY写法:
SELECT empno, ename, job, deptno,LEVEL, SYS_CONNECT_BY_PATH(ename,'\') as path
  FROM EMP 
WHERE CONNECT_BY_ISLEAF=1  ----- 叶子节点
START WITH mgr IS NULL -- mgr列为空,表示没有上级,该员工已经是最高级别。这是层次查询的起点
CONNECT BY PRIOR empno= mgr;
结果:
EMPNO ENAME      JOB       DEPTNO LEVEL  PATH
-----------------------------------------------------------------
7876  ADAMS      CLERK     20     4      \KING\JONES\SCOTT\ADAMS
7369  SMITH      CLERK     20     4      \KING\JONES\FORD\SMITH
7499  ALLEN      SALESMAN  30     3      \KING\BLAKE\ALLEN
7521  WARD       SALESMAN  30     3      \KING\BLAKE\WARD
7654  MARTIN     SALESMAN  30     3      \KING\BLAKE\MARTIN
7844  TURNER     SALESMAN  30     3      \KING\BLAKE\TURNER
7900  JAMES      CLERK     30     3      \KING\BLAKE\JAMES
7934  MILLER     CLERK     10     3      \KING\CLARK\MILLER

CONNECT_BY_ISLEAF是10G以上提供的,它是一个伪列,当CONNECT_BY_ISLEAF=1时表示该行为叶子节点。

在9i中没有CONNECT_BY_ISLEAF这个伪列,我们可以根据叶子节点的定义写一个相关子查询:

SELECT empno, ename, job, deptno,LEVEL, SYS_CONNECT_BY_PATH(ename,'\') as path
  FROM EMP e
WHERE NOT EXISTS (SELECT NULL FROM emp WHERE mgr = e.empno)
START WITH mgr IS NULL 
CONNECT BY PRIOR empno= mgr;

在这里,NOT EXISTS相关子查询的意思即不存在下一级数据,这正是叶子节点的定义。

在递归WITH子查询中实现CONNECT_BY_ISLEAF的方法:

WITH T(empno, ename, mgr, the_level, path) AS ( ---- 必须把结构写出来
   SELECT empno, ename, mgr  ---- 先写锚点查询,用START WITH的条件
         ,1 AS the_level    ---- 递归起点,第一层
         ,'\'||ename        ---- 路径的第一截
     FROM EMP
    WHERE mgr IS NULL ---- 原来的START WITH条件
   UNION ALL  ---- 下面是递归部分
   SELECT e.empno, e.ename, e.mgr     ---- 要加入的新一层数据,来自要遍历的emp表
         ,1 + t.the_level             ---- 递归层次,在原来的基础上加1
         ,t.path||'\'||e.ename        ---- 把新的一截路径拼上去
     FROM t, emp e                    ---- 典型写法,把子查询本身和要遍历的表作一个连接
    WHERE t.empno = e.mgr             ---- 原来的CONNECT BY条件
) 
SEARCH DEPTH FIRST BY mgr SET seq   --- 指定深度优先, 按顺序生成序号seq
SELECT * FROM (
SELECT T.* 
      ,(CASE WHEN the_level < LEAD(the_level) OVER (ORDER BY seq) THEN 0 ELSE 1 END) is_leaf ---- 如果层数不增加则为叶子节点。
  FROM T
  )
WHERE is_leaf=1
;
EMPNO ENAME          MGR  THE_LEVEL PATH                                SEQ    IS_LEAF
------ ---------- ------- ---------- ---------------------------- ---------- ----------
  7876 ADAMS         7788          4 \KING\JONES\SCOTT\ADAMS               4          1
  7369 SMITH         7902          4 \KING\JONES\FORD\SMITH                6          1
  7499 ALLEN         7698          3 \KING\BLAKE\ALLEN                     8          1
  7521 WARD          7698          3 \KING\BLAKE\WARD                      9          1
  7654 MARTIN        7698          3 \KING\BLAKE\MARTIN                   10          1
  7844 TURNER        7698          3 \KING\BLAKE\TURNER                   11          1
  7900 JAMES         7698          3 \KING\BLAKE\JAMES                    12          1
  7934 MILLER        7782          3 \KING\CLARK\MILLER                   14          1
8 rows selected.

seq 是整个结果集按照深度优先遍历顺序排序后的序号。按照深度优先的定义,每个节点都要比它的上一节点更深一层,除非已经到底(也就是叶子节点)。因此如果层数没有增加,则我们知道碰到了叶子节点,这就是最后一个CASE表达式的依据。

这个例子中出现了前面没有的搜索子句(SEARCH CLAUSE)。

SEARCH子句的语法:

SEARCH
①   { DEPTH FIRST BY c_alias [, c_alias]...
        [ ASC | DESC ]
        [ NULLS FIRST | NULLS LAST ]
②   | BREADTH FIRST BY c_alias [, c_alias]...
        [ ASC | DESC ]
        [ NULLS FIRST | NULLS LAST ]
     }
③    SET ordering_column

①DEPTH FIRST表示按深度优先的顺序来输出。BY后面的列名及其升降序、空值放置顺序指明了在深度优先的前提下,同一层次的数据的排序情况。这和原来CONNECT BY查询中的ORDER SIBLING BY子句是一样的。

②BREADTH FIRST表示按广度优先的顺序来输出。BY后面的列名及其升降序、空值放置顺序指明了在广度优先的前提下,同一层次的数据的排序情况。

③列名ordering_column用于存放排序后的序号,是一个从1开始的连续正整数。后续的查询中可以利用这个列得知某行数据在整个结果集中的位置。

那么这个搜索子句是不是可以指定ORACLE的搜索顺序呢?我们从递归WITH的写法来看,数据是一层一层叠加上去的,也就是广度优先算法。

为了证明这一点,我们可以写一个自定义函数,根据输出的顺序来判断ORACLE的搜索顺序:

CREATE OR REPLACE FUNCTION f_get_mgr (
       p_empno    IN NUMBER   ---- 当前员工编号
      ,p_mgr      IN NUMBER   ---- 当前员工的经理编号
      ,p_last_emp IN NUMBER   ---- 上一层员工的编号
      ,p_level    IN NUMBER   ---- 当前员工的层数
      ) 
RETURN NUMBER
AS
BEGIN
   IF p_last_emp = p_mgr THEN  
      ---- 假如发生匹配,证明这个节点将被加入,这时我们输出当前员工的编号。从屏幕的输出就可以判断节点被加入的顺序
      ---- 此输出可以观察函数被调用的顺序
      DBMS_OUTPUT.PUT_LINE('level='||p_level||' empno='||p_empno);
   END IF;
   RETURN p_mgr;
END f_get_mgr;
/

把查询修改一下:

WITH T(empno, ename, mgr, the_level, path) AS (
   SELECT empno, ename, mgr 
         ,1 AS the_level    
         ,'\'||ename        
     FROM EMP
    WHERE mgr IS NULL 
   UNION ALL  
   SELECT e.empno, e.ename, e.mgr  
         ,1 + t.the_level          
         ,t.path||'\'||e.ename     
     FROM t, emp e
    WHERE t.empno = f_get_mgr(e.empno,e.mgr,t.empno, t.the_level+1) ----此函数会返回经理编号,并在屏幕上输出员工编号
) 
SEARCH DEPTH FIRST BY mgr SET seq  ---- 强行指定深度优先顺序
SELECT * FROM T
;
输出:              
   EMPNO ENAME         MGR  THE_LEVEL PATH                              SEQ
-------- ---------- ------ ---------- -------------------------- ----------
    7839 KING                       1 \KING                               1
    7566 JONES        7839          2 \KING\JONES                         2
    7788 SCOTT        7566          3 \KING\JONES\SCOTT                   3
    7876 ADAMS        7788          4 \KING\JONES\SCOTT\ADAMS             4
    7902 FORD         7566          3 \KING\JONES\FORD                    5
    7369 SMITH        7902          4 \KING\JONES\FORD\SMITH              6
    7698 BLAKE        7839          2 \KING\BLAKE                         7
    7499 ALLEN        7698          3 \KING\BLAKE\ALLEN                   8
    7521 WARD         7698          3 \KING\BLAKE\WARD                    9
    7654 MARTIN       7698          3 \KING\BLAKE\MARTIN                 10
    7844 TURNER       7698          3 \KING\BLAKE\TURNER                 11
    7900 JAMES        7698          3 \KING\BLAKE\JAMES                  12
    7782 CLARK        7839          2 \KING\CLARK                        13
    7934 MILLER       7782          3 \KING\CLARK\MILLER                 14
14 rows selected.
level=2 empno=7566
level=2 empno=7698
level=2 empno=7782
level=3 empno=7788
level=3 empno=7902
level=3 empno=7499
level=3 empno=7521
level=3 empno=7654
level=3 empno=7844
level=3 empno=7900
level=3 empno=7934
level=4 empno=7876
level=4 empno=7369

尽管前半部分输出已经按照深度优先顺序排列,但从后半部分的输出来看,节点是逐层被加入的,可见遍历还是按广度优先顺序来的。

如果我们把查询改为广度优先,就和函数调用顺序一致了:

WITH T(empno, ename, mgr, the_level, path) AS (
   SELECT empno, ename, mgr 
         ,1 AS the_level    
         ,'\'||ename        
     FROM EMP
    WHERE mgr IS NULL 
   UNION ALL  
   SELECT e.empno, e.ename, e.mgr  
         ,1 + t.the_level          
         ,t.path||'\'||e.ename     
     FROM t, emp e
    WHERE t.empno = f_get_mgr(e.empno,e.mgr,t.empno, t.the_level+1) ----此函数会返回经理编号,并在屏幕上输出员工编号
) 
SEARCH BREADTH FIRST BY mgr SET seq  ---- 强行指定广度优先顺序
SELECT * FROM T
;
输出:
  EMPNO ENAME         MGR  THE_LEVEL PATH                          SEQ
------- ---------- ------ ---------- -------------------------- ------
   7839 KING                       1 \KING                           1
   7566 JONES        7839          2 \KING\JONES                     2
   7698 BLAKE        7839          2 \KING\BLAKE                     3
   7782 CLARK        7839          2 \KING\CLARK                     4
   7788 SCOTT        7566          3 \KING\JONES\SCOTT               5
   7902 FORD         7566          3 \KING\JONES\FORD                6
   7499 ALLEN        7698          3 \KING\BLAKE\ALLEN               7
   7521 WARD         7698          3 \KING\BLAKE\WARD                8
   7654 MARTIN       7698          3 \KING\BLAKE\MARTIN              9
   7844 TURNER       7698          3 \KING\BLAKE\TURNER             10
   7900 JAMES        7698          3 \KING\BLAKE\JAMES              11
   7934 MILLER       7782          3 \KING\CLARK\MILLER             12
   7876 ADAMS        7788          4 \KING\JONES\SCOTT\ADAMS        13
   7369 SMITH        7902          4 \KING\JONES\FORD\SMITH         14
14 rows selected.
level=2 empno=7566
level=2 empno=7698
level=2 empno=7782
level=3 empno=7788
level=3 empno=7902
level=3 empno=7499
level=3 empno=7521
level=3 empno=7654
level=3 empno=7844
level=3 empno=7900
level=3 empno=7934
level=4 empno=7876
level=4 empno=7369

以往也有人在9i中用这个方法制造is_leaf标记,但是该方法依赖于CONNECT BY的遍历顺序生成的ROWNUM,而ORACLE并没有保障ROWNUM一定会按深度优先的顺序生成。现在递归WITH子句支持遍历输出顺序的显式指定,就可以放心使用了。

——————————————————–

例8:

下面的例子来自我的一个老贴:

http://www.itpub.net/thread-1037629-1-1.html

机票路线问题:假设有一张表存储了起飞、到达机场以及这段路线的票价,现在要求出从城市1到城市2的所有飞行线路及其总价格。

测试环境设置:

CREATE TABLE fares   ---- 票价
(
    depart VARCHAR2(3),   --- 起飞机场
    arrive VARCHAR2(3),   --- 到达机场
    price  NUMBER
);
测试数据:
INSERT INTO FARES VALUES('BJ','SH',500);
INSERT INTO FARES VALUES('SH','GZ',1500);
INSERT INTO FARES VALUES('BJ','GZ',1800);
INSERT INTO FARES VALUES('GZ','BJ',1600);
insert into fares values('GZ','SH',1300);
INSERT INTO FARES VALUES('BJ','SZ',100);
INSERT INTO FARES VALUES('SZ','GZ',110);
COMMIT;

用CONNECT BY求出所有北京到上海的线路,转机不超过10次:

WITH f1 AS (
SELECT LEVEL lvl
      ,SYS_CONNECT_BY_PATH(depart,'/') ---- 用SYS_CONNECT_BY_PATH把所有的出发机场拼接起来。
       ||'/'||arrive||'/'  AS path     ---- 在右边拼上最后一段旅程的到达机场,构成完整的路径。最后添加的斜杠是为了下面解析方便。
  FROM fares 
WHERE arrive = 'SH'      ---- 目的地是上海
START WITH depart = 'BJ' ---- 从北京出发
CONNECT BY NOCYCLE depart=prior arrive   ---- 每段路线首位衔接
            AND 'BJ'<>arrive   ----- 目的地不能是北京,否则就绕回去了
            AND LEVEL <= 11    ----- 转机不超过10次
) 
SELECT * FROM f1;
输出:
       LVL PATH
---------- ------------------------------------------
         2 /BJ/GZ/SH/
         1 /BJ/SH/
         3 /BJ/SZ/GZ/SH/

可以看到有三条飞行路线,LEVEL表示了路线分为几段(转机次数)。

现在路线有了,接下来就得算总价格。这就意味这必须和fares做一个连接,路线的每一段都必须拆成起飞、到达机场这样的格式。

为了把一行拆成多行,我们必须构造一个自然数集(从1到最大LEVEL)连接上去;而每段路线的起始点可以用字符串函数INSTR, SUBSTR函数解析出来。

WITH f1 AS (
SELECT LEVEL lvl
      ,SYS_CONNECT_BY_PATH(depart,'/') ---- 用SYS_CONNECT_BY_PATH把所有的出发机场拼接起来。
       ||'/'||arrive||'/'  AS path     ---- 在右边拼上最后一段旅程的到达机场,构成完整的路径。最后添加的斜杠是为了下面解析方便。
  FROM fares 
WHERE arrive = 'SH'      ---- 目的地是上海
START WITH depart = 'BJ' ---- 从北京出发
CONNECT BY NOCYCLE depart=prior arrive   ---- 每段路线首位衔接
            AND 'BJ'<>arrive   ----- 目的地不能是北京,否则就绕回去了
            AND LEVEL <= 11    ----- 转机不超过10次
) 
,f2 AS (
SELECT path
      ,SUBSTR(path, INSTR(path,'/',1,n  )+1, INSTR(path,'/',1,n+1)-INSTR(path,'/',1,n  )-1) AS depart
      ,SUBSTR(path, INSTR(path,'/',1,n+1)+1, INSTR(path,'/',1,n+2)-INSTR(path,'/',1,n+1)-1) AS arrive
FROM f1
     ,(SELECT LEVEL AS n    ------- 利用CONNECT BY构造出一个自然数集合
         FROM DUAL
        CONNECT BY LEVEL<=(SELECT MAX(lvl) FROM f1) ---- 所需要的总行数不超过集合f1里面的最多的路线段数
       ) r
WHERE r.n<=lvl  ------ 每一行包含的路线段数为lvl, 相对于这一行我们需要拆成行数为lvl,即从集合r中取出1~lvl行来进行连接。
)
SELECT * FROM f2
ORDER BY 1;
输出:
PATH                       DEPART     ARRIVE
-------------------------- ---------- ----------
/BJ/GZ/SH/                 GZ         SH
/BJ/GZ/SH/                 BJ         GZ
/BJ/SH/                    BJ         SH
/BJ/SZ/GZ/SH/              SZ         GZ
/BJ/SZ/GZ/SH/              GZ         SH
/BJ/SZ/GZ/SH/              BJ         SZ
6 rows selected.

至此我们不仅拥有了所有北京到上海的飞行线路,而且这条线路的组成也都分解出来了。接下来的事情就是和fares表做一个连接求出票价,然后按PATH来做分组聚合求出线路总成本:

WITH f1 AS (
SELECT LEVEL lvl
      ,SYS_CONNECT_BY_PATH(depart,'/') ---- 用SYS_CONNECT_BY_PATH把所有的出发机场拼接起来。
       ||'/'||arrive||'/'  AS path     ---- 在右边拼上最后一段旅程的到达机场,构成完整的路径。最后添加的斜杠是为了下面解析方便。
  FROM fares 
WHERE arrive = 'SH'      ---- 目的地是上海
START WITH depart = 'BJ' ---- 从北京出发
CONNECT BY NOCYCLE depart=prior arrive   ---- 每段路线首位衔接
            AND 'BJ'<>arrive   ----- 目的地不能是北京,否则就绕回去了
            AND LEVEL <= 11    ----- 转机不超过10次
) 
,f2 AS (
SELECT path
      ,SUBSTR(path, INSTR(path,'/',1,n  )+1, INSTR(path,'/',1,n+1)-INSTR(path,'/',1,n  )-1) AS depart
      ,SUBSTR(path, INSTR(path,'/',1,n+1)+1, INSTR(path,'/',1,n+2)-INSTR(path,'/',1,n+1)-1) AS arrive
FROM f1
     ,(SELECT LEVEL AS n    ------- 利用CONNECT BY构造出一个自然数集合
         FROM DUAL
        CONNECT BY LEVEL<=(SELECT MAX(lvl) FROM f1) ---- 所需要的总行数不超过集合f1里面的最多的路线段数
       ) r
WHERE r.n<=lvl  ------ 每一行包含的路线段数为lvl, 相对于这一行我们需要拆成行数为lvl,即从集合r中取出1~lvl行来进行连接。
)
SELECT SUM(price) AS cost,path
  FROM f2,fares
WHERE f2.depart=fares.depart AND f2.arrive = fares.arrive
GROUP BY path
ORDER BY cost;
结果:
      COST PATH
---------- ------------------------
       500 /BJ/SH/
      1510 /BJ/SZ/GZ/SH/
      3100 /BJ/GZ/SH/

另外也有一个稍微简化的写法。在拼接路径的时候,我们可以把价格也拼起来,最后只要分解开再求和就可以了,无需和fares表再做连接:

WITH f1 AS (
SELECT LEVEL lvl
      ,SYS_CONNECT_BY_PATH(depart,'/') ---- 用SYS_CONNECT_BY_PATH把所有的出发机场拼接起来。
       ||'/'||arrive||'/'  AS path     ---- 在右边拼上最后一段旅程的到达机场,构成完整的路径。最后添加的斜杠是为了下面解析方便。
      ,SYS_CONNECT_BY_PATH(price,'/')||'/'  AS price_path ---- 用SYS_CONNECT_BY_PATH把所有的票价拼接起来。
  FROM fares 
WHERE arrive = 'SH'      ---- 目的地是上海
START WITH depart = 'BJ' ---- 从北京出发
CONNECT BY NOCYCLE depart=prior arrive   ---- 每段路线首位衔接
            AND 'BJ'<>arrive   ----- 目的地不能是北京,否则就绕回去了
            AND LEVEL <= 11    ----- 转机不超过10次
) 
,f2 AS (
SELECT path
      ,SUBSTR(price_path, INSTR(price_path,'/',1,n)+1, INSTR(price_path,'/',1,n+1)-INSTR(price_path,'/',1,n)-1) AS price
FROM f1
     ,(SELECT LEVEL AS n    ------- 利用CONNECT BY构造出一个自然数集合
         FROM DUAL
        CONNECT BY LEVEL<=(SELECT MAX(lvl) FROM f1) ---- 所需要的总行数不超过集合f1里面的最多的路线段数
       ) r
WHERE r.n<=lvl  ------ 每一行包含的路线段数为lvl, 相对于这一行我们需要拆成行数为lvl,即从集合r中取出1~lvl行来进行连接。
)
SELECT SUM(price) AS cost,path
  FROM f2
GROUP BY path
ORDER BY cost;

上述用CONNECT BY求出总票价的方法十分繁琐,因为SYS_CONNECT_BY_PATH必须先分开再聚合。

递归WITH的方法:沿路径求和的实现非常方便。

WITH T (depart,arrive,path,cost,lvl) AS (
SELECT  depart  ---- 构造第一层数据:从起点城市出发
       ,arrive 
       ,'/'||depart AS PATH
       ,price
       ,1
   FROM fares 
  WHERE depart = 'BJ'  ---- 起点是北京
UNION ALL  ------- 递归部分:把衔接的新一段路程拼接进去
SELECT f.depart 
      ,f.arrive
      ,t.path||'/'||f.depart    ----- 把新的路段的起点机场拼接上去
      ,t.cost + f.price         ----- 把新的路段的票价累加到总成本中。这是递归WITH最强大的地方。
      ,t.lvl+1                  ----- 层数递增
  FROM t,fares f
WHERE f.depart=t.arrive        ----- 递归条件:起飞机场是上一段的到达机场
       AND 'BJ'<>f.arrive       ----- 目的地不能是北京,否则就绕回去了
       AND t.arrive<>'SH'       ----- 递归终止条件:如果上一段终点已经是上海,没必要继续遍历了
       AND t.cost + f.price <5000 ------- 控制总成本在5000以内,否则停止遍历。这个剪枝功能是CONNECT BY做不到的。
       AND lvl<=10   -------- 控制转机次数,转机不超过10次
       AND INSTR(t.path,'/'||f.depart)=0  ------ 新一段路程的出发机场在路径中未出现过。相当于CONNECT BY的NOCYCLE功能,或是递归WITH中的CYCLE子句。
)
SELECT t.path||'/'||t.arrive path  ---- 在右边拼上最后一段旅程的到达机场,构成完整的路径。
      ,t.cost
FROM T WHERE arrive='SH';
PATH                      COST
------------------- ----------
/BJ/SH                     500
/BJ/GZ/SH                 3100
/BJ/SZ/GZ/SH              1510

例8:

沿路径求乘积应用例子: BOM

BOM(Bill Of Material)是一个ERP的相关术语,指的是物料清单,又称材料表或配方料表,它反映生产产品与其组件的数量和从属关系。

复杂的版本见:

http://www.itpub.net/thread-1233081-3-1.html

这里是一个简化版。

测试环境设置:
CREATE TABLE BOM (
       item_no VARCHAR2(10)      ---- 部件编号
      ,part_no VARCHAR2(10)      ---- 构成该部件的子部件编号
      ,qty NUMBER                ---- 构成该部件的子部件数量
      ); 
测试数据:
INSERT INTO BOM VALUES ('A','B',3);
INSERT INTO BOM VALUES ('A','C',2);
INSERT INTO BOM VALUES ('A','D',4);
INSERT INTO BOM VALUES ('B','E',2);
INSERT INTO BOM VALUES ('B','F',3);
INSERT INTO BOM VALUES ('D','G',6);
INSERT INTO BOM VALUES ('D','H',5);
INSERT INTO BOM VALUES ('E','I',3);
COMMIT;

例子数据中部件A由3个B, 2个C和4个D组成,一个B由2个E和3个F组成,等等。

现在要求组成A的所有零部件及其数量。

用CONNECT BY的办法十分困难。和上例类似,首先要求出树的遍历路径,然后再把路径拆开为多行,求出每段的数量构成;最后再做聚合运算。因为没有现成的聚合求积函数,必须转换为对数,求出对数之和,再取反对数。

WITH t AS (
SELECT ROWNUM id ----- 为每行分配一个唯一的id, 用于随后的记录拆分和聚合
      ,part_no
      ,SYS_CONNECT_BY_PATH(qty,',') PATH    ----- 把每一段的组成数量拼接起来
      ,LEVEL lvl
  FROM bom
START WITH item_no='A'
CONNECT BY item_no = PRIOR part_no
)
,t2 AS (
SELECT id
      ,part_no
      ,EXP(SUM(LN(REGEXP_SUBSTR(path,'[^,]+',1,rn)) ------ 用规则表达式辨识出分隔符(逗号),把每一段数量qty求出来;用LN函数取自然对数;用SUM求和;用EXP指数函数求出乘积
              )
          ) qty
  FROM t,(SELECT ROWNUM rn   ------- 利用CONNECT BY构造出一个自然数集合
           FROM (SELECT MAX(lvl) lvl FROM t) 
          CONNECT BY ROWNUM<=lvl 
         ) r
WHERE rn <= lvl   ------ 每一行包含的段数为lvl, 相对于这一行我们需要拆成行数为lvl,即从集合r中取出1~lvl行来进行连接。
GROUP BY id,part_no
)
SELECT part_no,SUM(qty) from t2 GROUP BY part_no;
结果:
PART_NO      SUM(QTY)
---------- ----------
H                  20
I                  18
D                   4
B                   3
C                   2
F                   9
E                   6
G                  24
8 rows selected.

用递归WITH的办法可谓赏心悦目:

WITH t(part_no,qty) AS (
SELECT part_no  ---- 取出第一层数据,即需要计算的部件A
      ,qty
  FROM bom
WHERE item_no='A'
UNION ALL
SELECT b.part_no
      ,t.qty*b.qty  ---- 新加入的节点乘上已有的乘积,就是这条路径的总乘积
  FROM t, bom b
WHERE b.item_no = t.part_no
)
SELECT part_no,SUM(qty) from t GROUP BY part_no;
  
结果:
PART_NO      SUM(QTY)
---------- ----------
H                  20
I                  18
D                   4
B                   3
C                   2
E                   6
F                   9
G                  24
8 rows selected.

发表评论

电子邮件地址不会被公开。 必填项已用*标注

回顶部