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