我們知道用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所在分支已經被去除。

oracle樹的裁剪

相關文章