我们知道用start with ... connect by ...可以生成树。有时候我们需要对生成的树进行裁剪，得到我们真正需要的数据。
对树的裁剪分为去除节点和去除分支两种，它们的差别就是把条件放在不同的位置。
请看例子：

原树：
scott@ORA9I> l
1 SELECT LEVEL,RPAD('................',LEVEL*3) ||ENAME EMPLOYEE
2 FROM EMP
3 CONNECT BY PRIOR EMPNO=MGR
4* START WITH ENAME='KING'
scott@ORA9I> /

LEVEL EMPLOYEE
---------- ------------------------------------------------------------
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

scott@ORA9I> l
1 SELECT substr(sys_connect_by_path(ENAME,'->'),3) EMPLOYEE
2 FROM EMP
3 CONNECT BY PRIOR EMPNO=MGR
4* START WITH ENAME='KING'
scott@ORA9I> /

EMPLOYEE
------------------------------------------------------------
KING
KING->JONES
KING->JONES->SCOTT
KING->JONES->SCOTT->ADAMS
KING->JONES->FORD
KING->JONES->FORD->SMITH
KING->BLAKE
KING->BLAKE->ALLEN
KING->BLAKE->WARD
KING->BLAKE->MARTIN
KING->BLAKE->TURNER
KING->BLAKE->JAMES
KING->CLARK
KING->CLARK->MILLER

14 rows selected.

去除节点：
scott@ORA9I> l
1 SELECT LEVEL,RPAD('................',LEVEL*3) ||ENAME EMPLOYEE
2 FROM EMP
3 WHERE ENAME!='SCOTT'
4 CONNECT BY PRIOR EMPNO=MGR
5* START WITH ENAME='KING'
scott@ORA9I> /

LEVEL EMPLOYEE
---------- ------------------------------------------------------------
1 ...KING
2 ......JONES
4 ............ADAMS
3 .........FORD
4 ............SMITH
2 ......BLAKE
3 .........ALLEN
3 .........WARD
3 .........MARTIN
3 .........TURNER
3 .........JAMES
2 ......CLARK
3 .........MILLER

13 rows selected.
--在此种表示方式中，ENAME='SCOTT'的节点已经不存在了，但SCOTT所在分支仍然存在，且所在分支的其他节点的LEVEL并没有改变，如ADAMS的LEVEL在去除节点前后的LEVEL都是4.
scott@ORA9I> l
1 SELECT substr(sys_connect_by_path(ENAME,'->'),3) EMPLOYEE
2 FROM EMP
3 WHERE ENAME!='SCOTT'
4 CONNECT BY PRIOR EMPNO=MGR
5* START WITH ENAME='KING'
scott@ORA9I> /

EMPLOYEE
------------------------------------------------------------
KING
KING->JONES
KING->JONES->SCOTT->ADAMS
KING->JONES->FORD
KING->JONES->FORD->SMITH
KING->BLAKE
KING->BLAKE->ALLEN
KING->BLAKE->WARD
KING->BLAKE->MARTIN
KING->BLAKE->TURNER
KING->BLAKE->JAMES
KING->CLARK
KING->CLARK->MILLER

13 rows selected.
--用这种表示方法，咋一看，好像SCOTT节点仍然存在。其实不是，用这种表示方法，只需要关注每一行的最后部分（也就是节点），可以看出SCOTT节点也是被去除的。
--第三行之所以还出现了SCOTT是因为ADAMS的LEVEL是4,而sys_connect_by_path需要把分支完全显示。

去除分支：
scott@ORA9I> l
1 SELECT LEVEL,RPAD('................',LEVEL*3) ||ENAME EMPLOYEE
2 FROM EMP
3 CONNECT BY PRIOR EMPNO=MGR
4 AND ENAME!='SCOTT'
5* START WITH ENAME='KING'
scott@ORA9I> /

LEVEL EMPLOYEE
---------- ------------------------------------------------------------
1 ...KING
2 ......JONES
3 .........FORD
4 ............SMITH
2 ......BLAKE
3 .........ALLEN
3 .........WARD
3 .........MARTIN
3 .........TURNER
3 .........JAMES
2 ......CLARK
3 .........MILLER

12 rows selected.
--可以看出，SCOTT节点已经不存在，以SCOTT为父节点的节点也不存在，也就是说，SCOTT所在分支已经被去除。
scott@ORA9I> l
1 SELECT substr(sys_connect_by_path(ENAME,'->'),3) EMPLOYEE
2 FROM EMP
3 CONNECT BY PRIOR EMPNO=MGR
4 AND ENAME!='SCOTT'
5* START WITH ENAME='KING'
scott@ORA9I> /

EMPLOYEE
------------------------------------------------------------
KING
KING->JONES
KING->JONES->FORD
KING->JONES->FORD->SMITH
KING->BLAKE
KING->BLAKE->ALLEN
KING->BLAKE->WARD
KING->BLAKE->MARTIN
KING->BLAKE->TURNER
KING->BLAKE->JAMES
KING->CLARK
KING->CLARK->MILLER

12 rows selected.
--用这个表示法更明显地说明SCOTT所在分支已经被去除。

ITPub博客

oracle树的裁剪

最新文章