Oracle 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;

===========分页

SELECT * FROM (
    SELECT a.*, ROWNUM rn FROM (
        --核心语句
        SELECT * FROM table t ORDER BY t.A
    ) a WHERE ROWNUM <= 100
) WHERE rn > 0