层次化查询

        前两天在论坛理看帖子，看到connect by ,level,但我不认识他们。在家翻看读书时的笔记以及文档，才发现这是关于层次化查询的内容，当年导师上课讲时就没太明白，难怪会有不认识的感觉。知识啊，千万不能糊弄的！！！正好这次搞懂了记下来。
      
 层次化查询的语法：
                      select ... from table_name
                      where  conditions
                      start  with conditions
                      connect by prior conditions
                      order by ...
说明 ：
1。 start  with
        指明了查询的根-root.在农场牛的亲子关系的例子中是 start  with ffspring = 'EVE' (Oracle 9i: The Complete reference)。在公司人员隶属关系例子是 start with last_name = 'King'

 2.   connect by
       指明了树中父结点与子节点的关系，不能用于连接查询的where字句。其中prior关键字指明了父结点，关系运算的另一侧则指明了子节点，关系运算符指定了结点间关系。要说 明的是这里的父子关系仅仅指当前查询树中的关系。和实际意义没有必然联系。这样才可以理解各种书中说的walking the tree: from the top down和walking the tree:from the bottom up。
                    
      具体说来，walking the tree: from the top down和walking the tree:from the bottom up针对的是数据的现实意义，农场牛亲子关系树中，EVE是第一代母牛，是这棵亲子树现实的根。一次从EVE开始的查询是from the top down，从任意一头牛开始找其祖代的牛，则是from the bottom up。
       但在某一次查询时，是以start with指定的结点为根，以后查询的结点为孩子。而connect by 中PRIOR总是在父子关系中父结点一侧。

       举个例子：
  SQL> select empno, lpad(' ',6*(Level-1),'-')||ename as ename, job, deptno       from    emp
  2  start with ename = 'KING'
  3  connect by prior empno = mgr
  4  /
 
EMPNO ENAME                                                                            JOB       DEPTNO
----- -------------------------------------------------------------------------------- --------- ------
 7839 KING                                                                             PRESIDENT     10
 7566 ----- JONES                                                                      MANAGER       20
 7788 ----------- SCOTT                                                                ANALYST       20
 7876 ----------------- ADAMS                                                          CLERK         20
 7902 ----------- FORD                                                                 ANALYST       20
 7369 ----------------- SMITH                                                          CLERK         20
 7698 ----- BLAKE                                                                      MANAGER       30
 7499 ----------- ALLEN                                                                SALESMAN      30
 7521 ----------- WARD                                                                 SALESMAN      30
 7654 ----------- MARTIN                                                               SALESMAN      30
 7844 ----------- TURNER                                                               SALESMAN      30
 7900 ----------- JAMES                                                                CLERK         30
 7782 ----- CLARK                                                                      MANAGER       10
 7934 ----------- MILLER                                                               CLERK         10
 
14 rows selected
 
SQL>

上述查询中指出ename='KING'的是根，连接条件是父结点的员工号等于子节点的经理号（connect by prior empno = mgr）

3. Level伪列 
         在使用connect by查询语句中，可以使用level，代表了当前的深度，根的level为1，当然是指查询时确定。这样就可以通过如下语句构造一个数列，作为连接的基础。
           select level from dual connect by level <= num
         前天在论坛上看到的一个帖子是问SQL, 当时也是不得其解，现在看来挺有一番趣味。:
 表：code     name     times
            01         内科        3
            02         外科        2

要得到结果：
01         内科
01         内科
01         内科
02         外科
02         外科

有人给出答案：
select t.code, t.name
from t, (select level lev from dual connect by level <= (select  max(times) from t)) tmp
where t.times>=tmp.lev
order by t.code




4.结点修剪和分支修剪
         通过where限制结点，可以去除某些结点，但相应子结点不受影响。通过connect by限制，则可以去掉某个分支。



:End
                    
       

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21402791/viewspace-623157/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21402791/viewspace-623157/