ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle树的裁剪

oracle树的裁剪

原创 Linux操作系统 作者:space6212 时间:2019-04-02 20:51:07 0 删除 编辑
我们知道用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博客 ” ,链接:http://blog.itpub.net/231499/viewspace-63789/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2005-01-25

  • 博文量
    82
  • 访问量
    57443