OCP1z0-047 :2013-08-11 描述层次查询(hierarchical query)

 

 

 

 

正确答案: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

 

 

 

 

 

层次查询知识补充:

[html][/html] view plaincopy

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

 

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

 

[html][/html] view plaincopy

  1. gyj@MYDB> select distinct first_value(path) over(partition by id order by lev desc) from (
  2.   2  select connect_by_root id id,level lev, sys_connect_by_path(name,’  ‘) path
  3.   3   from test
  4.   4   start with id in (select id from test)
  5.   5   connect by id=prior fid);
  6. FIRST_VALUE(PATH)OVER(PARTITIONBYIDORDERBYLEVDESC)
  7. —————————————————————————————
  8.   A  B  C  D
  9.   B  C  D
  10.   C  D
  11.   D

 

 

正向查找,用翻转函数

[html][/html] view plaincopy

  1. gyj@MYDB> select reverse(sys_connect_by_path(name,’  ‘))
  2.   2   from test
  3.   3   start with fid is null
  4.   4   connect by fid= prior id
  5.   5   order by level desc;
  6. REVERSE(SYS_CONNECT_BY_PATH(NAME,”))
  7. —————————————————————————————
  8. A  B  C  D
  9. B  C  D
  10. C  D
  11. D

 

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

[html][/html] view plaincopy

  1. gyj@MYDB> SELECT SYS_CONNECT_BY_PATH(NAME,’ ‘)
  2.   2    FROM TEST
  3.   3  WHERE CONNECT_BY_ISLEAF=1
  4.   4  START WITH ID IS NOT NULL –×¢ÒâÊÇid is not null,ÿÐж¼×÷Ϊ¸ù£¬•´Ïò²éÕÒ
  5.   5  CONNECT BY ID=PRIOR FID;
  6. SYS_CONNECT_BY_PATH(NAME,”)
  7. —————————————————————————————
  8.  A B C D
  9.  B C D
  10.  C D
  11.  D

 

使用10g reverse函数

[html][/html] view plaincopy

  1. gyj@MYDB>  WITH TEMP AS
  2.   2  (
  3.   3    SELECT 1 ID,’A’ NAME,2 PARENT FROM DUAL
  4.   4    UNION
  5.   5    SELECT 2 ID,’B’ NAME,3 PARENT FROM DUAL
  6.   6    UNION
  7.   7    SELECT 3 ID,’C’ NAME,4 PARENT FROM DUAL
  8.   8    UNION
  9.   9    SELECT 4 ID,’D’ NAME,NULL PARENT FROM DUAL
  10.  10  )
  11.  11  SELECT REVERSE(NAME) FROM
  12.  12  (
  13.  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.  14  ) ORDER BY RN DESC;
  15. REVERSE(NAME)
  16. —————————————————————————————
  17. A B C D
  18. B C D
  19. C D
  20. D

 

connect by和where,where是对最后的结果的过滤,不影响connect by出来的层次关系:也就是节点的level,所属的父节点,根等不变,不影响最后的结果。

[html][/html] view plaincopy

  1. gyj@MYDB> select  a.t,b.t
  2.   2     from (select rownum n, substr(‘abc’, rownum, 1) t
  3.   3             from dual
  4.   4           connect by rownum <= length(‘abc’)) a,
  5.   5          (select rownum m, substr(‘eabvc’, rownum, 1) t
  6.   6             from dual
  7.   7           connect by rownum <= length(‘eabvc’)) b
  8.   8  where a.t = b.t
  9.   9  connect by a.n = prior a.n + 1
  10.  10          and b.m = prior b.m + 1;
  11. T  T
  12. — —
  13. a  a
  14. b  b
  15. b  b
  16. c  c

 

创建一棵树

[html][/html] view plaincopy

  1. gyj@MYDB> create table TREETEST
  2.   2  (
  3.   3    CLASS1 VARCHAR2(40) not null,
  4.   4    CLASS2 VARCHAR2(40),
  5.   5    CLASS3 VARCHAR2(40),
  6.   6    NAME   VARCHAR2(40)
  7.   7  );
  8. gyj@MYDB> insert into treetest (CLASS1, CLASS2, CLASS3, NAME)
  9.   2  values (‘A1’, ”, ”, ‘D1’);
  10. 1 row created.
  11. gyj@MYDB> insert into treetest (CLASS1, CLASS2, CLASS3, NAME)
  12.   2  values (‘A2’, ”, ”, ‘D2’);
  13. 1 row created.
  14. gyj@MYDB> insert into treetest (CLASS1, CLASS2, CLASS3, NAME)
  15.   2  values (‘A1’, ”, ”, ‘D3’);
  16. 1 row created.
  17. gyj@MYDB> insert into treetest (CLASS1, CLASS2, CLASS3, NAME)
  18.   2  values (‘A1’, ‘B1’, ”, ‘D4’);
  19. 1 row created.
  20. gyj@MYDB> insert into treetest (CLASS1, CLASS2, CLASS3, NAME)
  21.   2  values (‘A2’, ‘B2’, ”, ‘D5’);
  22. 1 row created.
  23. gyj@MYDB> insert into treetest (CLASS1, CLASS2, CLASS3, NAME)
  24.   2  values (‘A1’, ‘B1’, ‘C1’, ‘D6’);
  25. 1 row created.
  26. gyj@MYDB> insert into treetest (CLASS1, CLASS2, CLASS3, NAME)
  27.   2  values (‘A2’, ‘B2’, ‘C2’, ‘D7’);
  28. 1 row created.
  29. gyj@MYDB> insert into treetest (CLASS1, CLASS2, CLASS3, NAME)
  30.   2  values (‘A1’, ‘B2’, ‘C3’, ‘D8’);
  31. 1 row created.
  32. gyj@MYDB> commit;
  33. Commit complete.
  34. gyj@MYDB> with temp as
  35.   2  (
  36.   3    select decode(name,’0′,’NULL’,class1) class1,class2,class3,decode(name,’0′,class1,name) name,rownum rn from
  37.   4    (
  38.   5      select t.* from
  39.   6      (
  40.   7       select * from treetest t
  41.   8       union
  42.   9       select distinct(class1),null,null,’0′ from treetest group by class1
  43.  10      ) t order by class1,name
  44.  11    ) t
  45.  12  )
  46.  13  select replace(replace(lpad(‘ ‘,(level – 1)*4,’ ‘) || ‘|—–‘ || name,
  47.  14                         key,
  48.  15                         ”
  49.  16                        ),
  50.  17                 ‘    ‘,’|    ‘
  51.  18                ) result
  52.  19  from
  53.  20  (
  54.  21    select name,key,min(rn) rn from
  55.  22    (
  56.  23      select name,class1 || class2 || class3 key,rn from temp
  57.  24      union
  58.  25      select * from (select class1 || class2 || class3 name,class1 || class2 key,rn from temp) where name != key
  59.  26      union
  60.  27      select * from (select class1 || class2 name,class1 key,rn from temp) where name != key
  61.  28    ) group by name,key
  62.  29  ) connect by key = prior name start with key = ‘NULL’ ORDER SIBLINGS BY rn
  63.  30  ;
  64. RESULT
  65. —————————————————————————————
  66. |—–A1
  67. |    |—–D1
  68. |    |—–D3
  69. |    |—–B1
  70. |    |    |—–D4
  71. |    |    |—–C1
  72. |    |    |    |—–D6
  73. |    |—–B2
  74. |    |    |—–C3
  75. |    |    |    |—–D8
  76. |—–A2
  77. |    |—–D2
  78. |    |—–B2
  79. |    |    |—–D5
  80. |    |    |—–C2
  81. |    |    |    |—–D7

标签