转载请注明出处：http://blog.csdn.net/guoyjoe/article/details/9908217

[每日一题] OCP1z0-047 :2013-08-11   描述层次查询（hierarchical query）........................31_OCP1z0



正确答案：BD

 

引用sky850623同学（在3楼）的解释：http://www.itpub.net/thread-1808865-1-1.html

 

A错误,树的遍历可以从上至下，或从下至上
B正确
C正确,可以删除某个某个遍历的分支
例: 删除scott的分支
SQL> select empno,ename,level,lpad('',2*level-1)||sys_connect_by_path(ename,'/') path from emp
  2  start with empno=7566
  3  connect by priorempno=mgr and ename!='SCOTT';
D错误
SQL> select empno,ename,level,lpad('',2*level-1)||sys_connect_by_path(ename,'/') path from emp
  2  where ename!='SCOTT'  
  3  start with empno=7566
  4  connect by priorempno=mgr;
可以使用条件限制输出。
正确答案BC

     EMPNO ENAME          LEVEL PATH
---------- ---------- ----------------------------------------
      7566 JONES              1  /JONES
      7876 ADAMS              3     /JONES/SCOTT/ADAMS
      7902 FORD               2   /JONES/FORD
      7369 SMITH              3     /JONES/FORD/SMITH
     EMPNO ENAME          LEVEL PATH
---------- ---------- ----------------------------------------
      7566 JONES              1  /JONES
      7902 FORD               2   /JONES/FORD
      7369 SMITH              3     /JONES/FORD/SMITH

 





层次查询知识补充：

gyj@MYDB> create table test(id number,name varchar2(10),fid number);  Table created.  gyj@MYDB> insert into test values(1,'A',2);  1 row created.  gyj@MYDB> insert into test values(2,'B',3);  1 row created.  gyj@MYDB> insert into test values(3,'C',4);  1 row created.  gyj@MYDB> insert into test values(4,'D',null);  1 row created.  gyj@MYDB> commit;  Commit complete.

正向查找，对于每个遍历，只查找第一行记录

gyj@MYDB> select distinct first_value(path) over(partition by id order by lev desc) from (   2  select connect_by_root id id,level lev, sys_connect_by_path(name,'  ') path   3   from test   4   start with id in (select id from test)   5   connect by id=prior fid);  FIRST_VALUE(PATH)OVER(PARTITIONBYIDORDERBYLEVDESC) ---------------------------------------------------------------------------------------   A  B  C  D   B  C  D   C  D   D

正向查找，用翻转函数

gyj@MYDB> select reverse(sys_connect_by_path(name,'  '))   2   from test   3   start with fid is null   4   connect by fid= prior id    5   order by level desc;  REVERSE(SYS_CONNECT_BY_PATH(NAME,'')) --------------------------------------------------------------------------------------- A  B  C  D B  C  D C  D D

反向查找，最后只找叶子节点

gyj@MYDB> SELECT SYS_CONNECT_BY_PATH(NAME,' ')   2    FROM TEST   3  WHERE CONNECT_BY_ISLEAF=1   4  START WITH ID IS NOT NULL --×