oralce Connect by

--connect by 使用详解

/*START WITH 表示递归的种子，而 CONNECT BY 描述递归步骤，
也就是如何从第 n 步发展到第 (n + 1) 步。由于在归结 name
时需要区分第 n 步和第 (n + 1) 步，因此使用 PRIOR 来表明
empid 属于第 n 步，而 mgrid 属于第 (n + 1) 步。在第 1 步，
empid 为 16，mgrid 也必须是 16，因此第 2 步产生‘Scott’、
‘Henry’和‘Zander’。他们的 empid 将作为第 3 步的 PRIOR，
依此类推。
*/
select ename,job
      ,ename
  from scott.emp emp
start with ename in ('WARD')
connect by empno = prior mgr;

/*LEVEL 伪列
最常见的伪列是 LEVEL。这个列的作用是表明产生行的递归步骤 n 属于第几步。
在这个例子中，它表示‘Goyal’与雇员之间相差的管理等级加 1（因为 LEVEL 一开始为 1）。
下面是原先的 Oracle 例子添加了 LEVEL 列之后的样子：
*/
select level,ename,job
  from scott.emp emp
start with ename in ('WARD','SMITH','ADAMS')
connect by empno = prior mgr;

/*
CONNECT_BY_ROOT 表达式
CONNECT_BY_ROOT 作用在一个列上，并返回当前行最早的（root）祖先的值。
*/
select connect_by_root ename as root
      ,ename
  from scott.emp emp
start with ename in ('WARD','SMITH','ADAMS')
connect by empno = prior mgr;

/*SYS_CONNECT_BY_PATH() 过程
运行查询时一个常见的问题是：“这个元素与递归的开始有怎样的关系？”，
或者换句话说，“这一行的祖先是什么？”在 Oracle 中，
可以使用 SYS_CONNECT_BY_PATH() 来连接来自每个递归步骤的值，
从而形成一个祖先路径。看看下面这个著名的例子，
它展示了‘ADAMS’每个下属的“从属”链：
*/
--根据员工找审批人
select SYS_CONNECT_BY_PATH(ename, ':') AS chain, ename
  from scott.emp emp
start with ename='ADAMS'
connect by empno = prior mgr;
--根据上级找下级
select SYS_CONNECT_BY_PATH(ename, ':') AS chain, ename
  from scott.emp emp
start with ename='JONES'
connect by  prior empno =  mgr;

/*
ORDER SIBLINGS BY 表达式
在 Oracle 中，ORDER SIBLINGS BY 定义返回时同一父亲下各个兄弟之间的顺序。
为了使用 CONNECT BY 递归地按照薪水对所有雇员排序，查询的形式如下：
*/
select SYS_CONNECT_BY_PATH(ename, ':') AS chain,ename
      ,level
      ,sal
  from scott.emp
start with ename = 'KING'
connect by prior empno = mgr
order siblings by sal;

/*NOCYCLE 关键字
至此我已经介绍了记录递归路径和在运行时检测递归的功能，
实际上还可以映射 Oracle 的 CONNECT BY 查询语法中的 NOCYCLE
关键字。简言之，NOCYCLE 防止递归进入循环。对于第 (n + 1) 步，
任何在祖先中已经存在的候选行都将被忽略。在 Oracle 中，使用方法如下：
*/
select ename   
      ,level  
      ,sal
  from scott.emp
start with ename = 'JONES'
CONNECT BY NOCYCLE PRIOR empno = mgr;

/*CONNECT_BY_ISCYCLE
为了“标出”递归中的循环，Oracle 提供了另一个伪列，
即 CONNECT_BY_ISCYCLE。如果当前行是循环的一部分，
则这个列返回 1，否则返回 0。
*/
select ename
      ,level
      ,connect_by_iscycle as cycle
  from scott.emp
start with ename = 'JONES'
connect by nocycle prior empno = mgr;


/*CONNECT_BY_ISLEAF
与 CONNECT_BY_ISCYCLE 相比，CONNECT_BY_ISLEAF 更轻量一些。
这个伪列只是当一个给定行在递归中是叶子的时候返回 1。
换句话说：该行不会产生任何其他的行。在这个例子中，
叶子指不是经理的雇员。
*/
select ename
      ,level
      ,CONNECT_BY_ISLEAF AS isleaf
  from scott.emp
start with ename = 'JONES'
connect by nocycle prior empno = mgr;