[每日一题] OCP1z0-047 :2013-08-11描述层次查询（hierarchical query）(二)
tr('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 || 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