oracle 层次查询

先看看Oracle官方文档的介绍

 

If a table contains hierarchical data, then you can select rows in a hierarchical order using the hierarchical query clause:

hierarchical_query_clause::=            
{ CONNECT BY [ NOCYCLE ] condition [ START WITH condition ]            
| START WITH condition CONNECT BY [ NOCYCLE ] condition            
}

start WITHspecifies the root row(s) of the hierarchy.   

CONNECT BY specifies the relationship between parent rows and child rows of the hierarchy.   

In a hierarchical query, one expression in condition must be qualified with the PRIOR operator to refer to the parent row. For example,

... PRIOR expr = expr
or
... expr = PRIOR expr     

   

PRIOR is a unary operator and has the same precedence as the unary + and - arithmetic operators. It evaluates the immediately following expression for the parent row of the current row in a hierarchical query.

解释一下：PRIOR是一个一元的操作，和+、-号有相同的优先级。在一个层次查询中，它会求出表达式当前行的父行。

You can further refine a hierarchical query by using the CONNECT_BY_ROOT operator to qualify a column in the select list. This operator extends the functionality of the CONNECT BY [PRIOR] condition of hierarchical queries by returning not only the immediate parent row but all ancestor rows in the hierarchy.   

下面看个例子：order siblings by 定义返回同一个父亲下各个兄弟之间的顺序。   

   

SQL> select empno,ename,mgr,level from emp start with empno=7839 connect by prio      
r empno=mgr order siblings by empno;      

     EMPNO ENAME             MGR      LEVEL
---------- ---------- ---------- ----------      
      7839 KING                           1      
      7566 JONES            7839          2      
      7788 SCOTT            7566          3      
      7876 ADAMS            7788          4      
      7902 FORD             7566          3      
      7369 SMITH            7902          4      
      7698 BLAKE            7839          2      
      7499 ALLEN            7698          3      
      7521 WARD             7698          3      
      7654 MARTIN           7698          3      
      7844 TURNER           7698          3      

     EMPNO ENAME             MGR      LEVEL
---------- ---------- ---------- ----------      
      7900 JAMES            7698          3      
      7782 CLARK            7839          2      
      7934 MILLER           7782          3      

已选择14行。

   

  

下面我们实现一个层次结构，让关系看的更清楚

 

SQL> select empno,ename,mgr,level，rpad(' ',level*3) || ename tree from emp star      
t with empno=7839 connect by prior empno=mgr order siblings by empno;      

     EMPNO ENAME             MGR      LEVEL TREE      
---------- ---------- ---------- ---------- ------------------------------      
      7839 KING                           1    KING      
      7566 JONES            7839          2       JONES      
      7788 SCOTT            7566          3          SCOTT      
      7876 ADAMS            7788          4             ADAMS      
      7902 FORD             7566          3          FORD      
      7369 SMITH            7902          4             SMITH      
      7698 BLAKE            7839          2       BLAKE      
      7499 ALLEN            7698          3          ALLEN      
      7521 WARD             7698          3          WARD      
      7654 MARTIN           7698          3          MARTIN      
      7844 TURNER           7698          3          TURNER      

     EMPNO ENAME             MGR      LEVEL TREE      
---------- ---------- ---------- ---------- ------------------------------      
      7900 JAMES            7698          3          JAMES      
      7782 CLARK            7839          2       CLARK      
      7934 MILLER           7782          3          MILLER      

已选择14行。

   

connect_by_root 必须与字段搭配使用，目的是获取根节点的信息。

connect_by_isleaf 判断当前节点是否为叶子节点，0表示为非叶子节点，1表示为叶子节点。

例子如下：

SQL> select empno,ename,mgr,level，connect_by_isleaf,connect_by_root ename rootn      
ame from emp start with empno=7839 connect by prior empno=mgr order siblings by
empno;      

     EMPNO ENAME             MGR      LEVEL CONNECT_BY_ISLEAF ROOTNAME      
---------- ---------- ---------- ---------- ----------------- ----------      
      7839 KING                           1                 0 KING      
      7566 JONES            7839          2                 0 KING      
      7788 SCOTT            7566          3                 0 KING      
      7876 ADAMS            7788          4                 1 KING      
      7902 FORD             7566          3                 0 KING      
      7369 SMITH            7902          4                 1 KING      
      7698 BLAKE            7839          2                 0 KING      
      7499 ALLEN            7698          3                 1 KING      
      7521 WARD             7698          3                 1 KING      
      7654 MARTIN           7698          3                 1 KING      
      7844 TURNER           7698          3                 1 KING      

     EMPNO ENAME             MGR      LEVEL CONNECT_BY_ISLEAF ROOTNAME      
---------- ---------- ---------- ---------- ----------------- ----------      
      7900 JAMES            7698          3                 1 KING      
      7782 CLARK            7839          2                 0 KING      
      7934 MILLER           7782          3                 1 KING      

已选择14行。

   

 

SYS_CONNECT_BY_PATH 函数,根据遍历的路径加上分割符，看个例子就明白了

SQL> select empno,ename,mgr,level,sys_connect_by_path(ename,'/') tree from emp  
tart with empno=7839 connect by prior empno=mgr order siblings by empno;  

     EMPNO ENAME             MGR      LEVEL TREE  
---------- ---------- ---------- ---------- ------------------------------  
      7839 KING                           1 /KING  
      7566 JONES            7839          2 /KING/JONES  
      7788 SCOTT            7566          3 /KING/JONES/SCOTT  
      7876 ADAMS            7788          4 /KING/JONES/SCOTT/ADAMS  
      7902 FORD             7566          3 /KING/JONES/FORD  
      7369 SMITH            7902          4 /KING/JONES/FORD/SMITH  
      7698 BLAKE            7839          2 /KING/BLAKE  
      7499 ALLEN            7698          3 /KING/BLAKE/ALLEN  
      7521 WARD             7698          3 /KING/BLAKE/WARD  
      7654 MARTIN           7698          3 /KING/BLAKE/MARTIN  
      7844 TURNER           7698          3 /KING/BLAKE/TURNER  

     EMPNO ENAME             MGR      LEVEL TREE  
---------- ---------- ---------- ---------- ------------------------------  
      7900 JAMES            7698          3 /KING/BLAKE/JAMES  
      7782 CLARK            7839          2 /KING/CLARK  
      7934 MILLER           7782          3 /KING/CLARK/MILLER  

已选择14行。

   

 在看一个Oracle官方文档提供的例子

SELECT name, SUM(salary) "Total_Salary" FROM (  
   SELECT CONNECT_BY_ROOT last_name as name, Salary  
      FROM employees  
      WHERE department_id = 110  
      CONNECT BY PRIOR employee_id = manager_id)  
      GROUP BY name
   ORDER BY name, "Total_Salary";  

NAME                      Total_Salary  
------------------------- ------------  
Gietz                             8300  
Higgins                          20300  
King                             20300  
Kochhar                          20300

   

关于层次查询学习如上，参照：Oracle® Database SQL Language Reference 11g Release 2 (11.2)