层次化查询

                               层次化查询(START BY ... CONNECT BY PRIOR)

注：本文整理自网络。

一、概述
层次化查询,即树型结构查询,是SQL中经常用到的功能之一,通常由根节点,父节点,子节点,叶节点组成。
二、主要内容
1.语法结构:
  SELECT [LEVEL] ,columnname...
         FROM table_name
              [WHERE where_clause]
                     [[START WITH start_condition]
              [CONNECT BY PRIOR prior_condition]];
参数说明：
    LEVEL:           为伪列,用于表示树的层次。
    start_condition：层次化查询起始条件。
    prior_condition：定义父节点和子节点之间的关系。

2.使用说明
  (1).使用start with...connect by prior 从根节点开始遍历.
    SQL> select level,empno,mgr,ename from emp
         start with empno = 7839
         connect by prior empno = mgr
         order by level;
结果如下：
 LEVEL EMPNO   MGR ENAME
------- ----- ----- -------
      1  7839       KING
      2  7566  7839 JONES
      2  7698  7839 BLAKE
      2  7782  7839 CLARK
      3  7902  7566 FORD
      3  7521  7698 WARD
      3  7900  7698 JAMES
      3  7934  7782 MILLER
      3  7499  7698 ALLEN
      3  7788  7566 SCOTT
      3  7654  7698 MARTIN
      3  7844  7698 TURNER
      4  7876  7788 ADAMS
      4  7369  7902 SMITH
注：connect by prior empno = mgr表示前一条记录的empno是这条记录的mgr，即从顶至下的查询。
  
(2).获得层次数
    SQL> select count(distinct level) "Level" from emp
        start with ename = 'KING'
        connect by prior empno = mgr;

     Level
    -------
       4 
       
(3).格式化层次查询结果(使用左填充* level - 1个空格)

    SQL> col Ename for a30--指定输出格式，ENAME指定30个字符，多余的显示至下一行。
    SQL> select level,lpad(' ',2 * level - 1) || ename as "Ename" from emp
        start with ename = 'KING'
        connect by prior empno = mgr;
结果如下：
 LEVEL      Ename
---------- ---------------
         1  KING
         2    JONES
         3      SCOTT
         4        ADAMS
         3      FORD
         4        SMITH
         2    BLAKE
         3      ALLEN
         3      WARD
         3      MARTIN
         3      TURNER
         3      JAMES
         2    CLARK
         3      MILLER
(4).从非根节点开始遍历(只需修改start with 中的条件即可)

    SQL> select level,
         lpad(' ',2 * level - 1) || ename as "Ename" from emp
         start with ename = 'SCOTT'
         connect by prior empno = mgr;
结果如下：
     LEVEL Ename
---------- ------------------------------
         1  SCOTT
         2    ADAMS

(5).从下向上遍历(交换connect by prior中的条件即可,使用mgr = empno)
注意connect by prior mgr = empno 的理解
prior表示前一条记录,即下一条返回记录的empno应当等于前一条记录的mgr

    SQL> select level,
         lpad(' ',2 * level - 1) || ename as "Ename" from emp
         start with ename = 'SCOTT'
         connect by prior mgr = empno;

--从下向上遍历(也可以将prior置于等号右边,得到相同的结果)

    SQL> select level,
         lpad(' ',2 * level - 1) || ename as "Ename" from emp
         start with ename = 'SCOTT'
         connect by empno = prior mgr;
  
(6).从层次查询中删除节点和分支

    SQL> select level,
         lpad(' ',2 * level - 1) || ename as "Ename"
        from emp
        where ename != 'SCOTT'    --通过where子句来过滤SCOTT用户，但SCOTT的下属ADAMS并没有过滤掉
        start with empno = 7839   
        connect by prior empno = mgr;

--通过将过滤条件由where 子句的内容移动到connect by prior 子句中过滤掉SCOTT及其下属

    SQL> select level,
         lpad(' ',2 * level - 1) || ename as "Ename" from emp
        start with empno = 7839
        connect by prior empno = mgr and ename != 'SCOTT';