当前位置:操作系统 > Unix/Linux >>

[每日一题] OCP1z0-047 :2013-08-11描述层次查询(hierarchical query)

[每日一题] OCP1z0-047 :2013-08-11描述层次查询(hierarchical query)
 
 
正确答案:BD
 
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

 

 
层次查询知识补充:
[html] 
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.  

 

 
正向查找,对于每个遍历,只查找第一行记录
[html] 
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  

 

 
正向查找,用翻转函数
[html] 
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  

 

 
反向查找,最后只找叶子节点
[html] 
gyj@MYDB> SELECT SYS_CONNECT_BY_PATH(NAME,' ')  
  2    FROM TEST  
  3  WHERE CONNECT_BY_ISLEAF=1  
  4  START WITH ID IS NOT NULL --×¢ÒâÊÇid is not null,ÿÐж¼×÷Ϊ¸ù£¬•´Ïò²éÕÒ  
  5  CONNECT BY ID=PRIOR FID;  
  
SYS_CONNECT_BY_PATH(NAME,'')  
---------------------------------------------------------------------------------------  
 A B C D  
 B C D  
 C D  
 D  

 

 
使用10g reverse函数
[html] 
gyj@MYDB>  WITH TEMP AS  
  2  (  
  3    SELECT 1 ID,'A' NAME,2 PARENT FROM DUAL  
  4    UNION  
  5    SELECT 2 ID,'B' NAME,3 PARENT FROM DUAL  
  6    UNION  
  7    SELECT 3 ID,'C' NAME,4 PARENT FROM DUAL  
  8    UNION  
  9    SELECT 4 ID,'D' NAME,NULL PARENT FROM DUAL  
 10  )  
 11  SELECT REVERSE(NAME) FROM  
 12  (  
 13  SELECT SYS_CONNECT_BY_PATH(NAME,' ') NAME,LENGTH(SYS_CONNECT_BY_PATH(NAME,' ')) RN FROM TEMP CONNECT BY PARENT = PRIOR ID START WITH PARENT IS NULL  
 14  ) ORDER BY RN DESC;  
  
REVERSE(NAME)  
---------------------------------------------------------------------------------------  
A B C D  
B C D  
C D  
D  

 

 
connect by和where,where是对最后的结果的过滤,不影响connect by出来的层次关系:也就是节点的level,所属的父节点,根等不变,不影响最后的结果。
[html] 
gyj@MYDB> select  a.t,b.t  
  2     from (select rownum n, substr('abc', rownum, 1) t  
  3             from dual  
  4           connect by rownum <= length('abc')) a,         
  5          (select rownum m, substr('eabvc', rownum, 1) t  
  6             from dual  
  7           connect by rownum <= length('eabvc')) b  
  8  where a.t = b.t  
  9  connect by a.n = prior a.n + 1  
 10          and b.m = prior b.m + 1;  
  
T  T  
-- --  
a  a  
b  b  
b  b  
c  c  

 

 
创建一棵树
[html] 
gyj@MYDB> create table TREETEST  
  2  (  
  3    CLASS1 VARCHAR2(40) not null,  
  4    CLASS2 VARCHAR2(40),  
  5    CLASS3 VARCHAR2(40),  
  6    NAME   VARCHAR2(40)  
  7  );  
  
gyj@MYDB> insert into treetest (CLASS1, CLASS2, CLASS3, NAME)  
  2  values ('A1', '', '', 'D1');  
  
1 row created.  
  
gyj@MYDB> insert into treetest (CLASS1, CLASS2, CLASS3, NAME)  
  2  values ('A2', '', '', 'D2');  
  
1 row created.  
  
gyj@MYDB> insert into treetest (CLASS1, CLASS2, CLASS3, NAME)  
  2  values ('A1', '', '', 'D3');  
  
1 row created.  
  
gyj@MYDB> insert into treetest (CLASS1, CLASS2, CLASS3, NAME)  
  2  values ('A1', 'B1', '', 'D4');  
  
1 row created.  
  
gyj@MYDB> insert into treetest (CLASS1, CLASS2, CLASS3, NAME)  
  2  values ('A2', 'B2', '', 'D5');  
  
1 row created.  
  
gyj@MYDB> insert into treetest (CLASS1, CLASS2, CLASS3, NAME)  
  2  values ('A1', 'B1', 'C1', 'D6');  
  
1 row created.  
  
gyj@MYDB> insert into treetest (CLASS1, CLASS2, CLASS3, NAME)  
  2  values ('A2', 'B2', 'C2', 'D7');  
  
1 row created.  
  
gyj@MYDB> insert into treetest (CLASS1, CLASS2, CLASS3, NAME)  
  2  values ('A1', 'B2', 'C3', 'D8');  
  
1 row created.  
  
gyj@MYDB> commit;  
  
Commit complete.  
  
  
  
  
gyj@MYDB> with temp as  
  2  (  
  3    select decode(name,'0','NULL',class1) class1,class2,class3,decode(name,'0',class1,name) name,rownum rn from  
  4    (  
  5      select t.* from   
  6      (  
  7       select * from treetest t   
  8       union   
  9       select distinct(class1),null,null,'0' from treetest group by class1  
 10      ) t order by class1,name  
 11    ) t  
 12  )  
 13  select replace(replace(lpad(' ',(level - 1)*4,' ') || '|-----' || name,  
 14                         key,  
 15                         ''  
 16                        ),  
 17                 '    ','|    '  
 18                ) result  
 19  from   
 20  (  
 21    select name,key,min(rn) rn from  
 22    (  
 23      select name,class1 || class2 || class3 key,rn from temp  
 24      union  
 25      select * from (select class1
CopyRight © 2012 站长网 编程知识问答 www.zzzyk.com All Rights Reserved
部份技术文章来自网络,