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