oracle中connect by和start with的使用

 

 

 

select [level],column,expression,...

from table_name

[where where_clause]

[[start with start_condition][connect by prior prior_condition]];

level:一个伪列，代表位于查询的第几层。

start_condition:定义层次化查询的起点。

prior_condition:定义了父行和子行的关系，如:column1=column2指的是从column2指向column1。

我们以oracle中emp表为例，下图是emp表中数据的树形结构： 

 

1.从根节点开始遍历：

SELECT LEVEL,lpad(' ',2*(LEVEL-1))||ename
FROM emp
START WITH empno=7839
CONNECT BY PRIOR empno=mgr;

     LEVEL LPAD('',2*(LEVEL-1))||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

2.从某个节点反向树根遍历树

    从adams反向遍历

SELECT LEVEL,lpad(' ',2*(LEVEL-1))||ename
FROM emp
START WITH ename='ADAMS'
CONNECT BY PRIOR mgr=empno
ORDER BY LEVEL DESC;

     LEVEL LPAD('',2*(LEVEL-1))||ENAME
---------- -------------------------------
         4       KING
         3     JONES
         2   SCOTT
         1 ADAMS

3.计算树的层数

select count(distinct level)
from emp
start with empno=7839
connect by prior empno=mgr;

COUNT(DISTINCTLEVEL)
--------------------
                   4

4.我们将某个节点及其孩子节点删除

    删除blake及其孩子节点

SELECT LEVEL,lpad(' ',2*(LEVEL-1))||ename
FROM emp
WHERE ename!='BLAKE'
START WITH empno=7839
CONNECT BY PRIOR empno=mgr;

     LEVEL LPAD('',2*(LEVEL-1))||ENAME
---------- -------------------------------
         1 KING
         2   JONES
         3     SCOTT
         4       ADAMS
         3     FORD
         4       SMITH
         3     ALLEN
         3     WARD
         3     MARTIN
         3     TURNER
         3     JAMES
         2   CLARK
         3     MILLER

但是我们发现blake节点的孩子节点并没有被删除，那是我们的过滤条件写得不对，下面来将其删除

SELECT LEVEL,lpad(' ',2*(LEVEL-1))||ename
FROM emp
START WITH empno=7839
CONNECT BY PRIOR empno=mgr
AND ename!='BLAKE';

     LEVEL LPAD('',2*(LEVEL-1))||ENAME
---------- ----------------------------------
         1 KING
         2   JONES
         3     SCOTT
         4       ADAMS
         3     FORD
         4       SMITH
         2   CLARK
         3     MILLER