1. 首页
  2. IT资讯

oracle树的裁剪

我们知道用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 ………MILLERscott@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————————————————————KINGKING->JONESKING->JONES->SCOTTKING->JONES->SCOTT->ADAMSKING->JONES->FORDKING->JONES->FORD->SMITHKING->BLAKEKING->BLAKE->ALLENKING->BLAKE->WARDKING->BLAKE->MARTINKING->BLAKE->TURNERKING->BLAKE->JAMESKING->CLARKKING->CLARK->MILLER14 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 ………MILLER13 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————————————————————KINGKING->JONESKING->JONES->SCOTT->ADAMSKING->JONES->FORDKING->JONES->FORD->SMITHKING->BLAKEKING->BLAKE->ALLENKING->BLAKE->WARDKING->BLAKE->MARTINKING->BLAKE->TURNERKING->BLAKE->JAMESKING->CLARKKING->CLARK->MILLER13 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 ………MILLER12 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————————————————————KINGKING->JONESKING->JONES->FORDKING->JONES->FORD->SMITHKING->BLAKEKING->BLAKE->ALLENKING->BLAKE->WARDKING->BLAKE->MARTINKING->BLAKE->TURNERKING->BLAKE->JAMESKING->CLARKKING->CLARK->MILLER12 rows selected.–用这个表示法更明显地说明SCOTT所在分支已经被去除。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/231499/viewspace-63789/,如需转载,请注明出处,否则将追究法律责任。

主题测试文章,只做测试使用。发布者:布吉卡,转转请注明出处:http://www.cxybcw.com/195607.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code