[每日一题] 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
层次查询知识补充：
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 --×¢ÒâÊÇ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函数
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,所属的父节点，根等不变,不影响最后的结果。
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
创建一棵树
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 || class2 || class3 name,class1 || class2 key,rn from temp) where name != key
26 union
27 select * from (select class1 || class2 name,class1 key,rn from temp) where name != key
28 ) group by name,key
29 ) connect by key = prior name start with key = 'NULL' ORDER SIBLINGS BY rn
30 ;
RESULT
---------------------------------------------------------------------------------------
|-----A1
| |-----D1
| |-----D3
| |-----B1
| | |-----D4
| | |-----C1
| | | |-----D6
| |-----B2
| | |-----C3
| | | |-----D8
|-----A2
| |-----D2
| |-----B2
| | |-----D5
| | |-----C2
| | | |-----D7
