« June 2009 | Main | November 2009 »
July 2, 2009
Tree structure
I found some this sql queries very interesting:
And I tried to used on my project (because the division and department sctructure is kind like the tree structure)
create table tree (
id number(10) not null primary key,
name varchar2(100) not null,
super number(10) not null // 0 is root
);
-- from leaf to root
#select * from tree start with id = ? connect by id = prior super
# -- from root to leaf
# select * from tree start with id = ? connect by prior id = suepr
# -- the whole tree
# select * from tree start with super = 0 connect by prior id = suepr
--------------------------------------------------------------------------------------------------
Example:
create table test_connect_by (
parent number,
child number,
constraint uq_tcb unique (child)
);
insert into test_connect_by values ( 5, 2);
insert into test_connect_by values ( 5, 3);
insert into test_connect_by values (18,11);
insert into test_connect_by values (18, 7);
insert into test_connect_by values (17, 9);
insert into test_connect_by values (17, 8);
insert into test_connect_by values (26,13);
insert into test_connect_by values (26, 1);
insert into test_connect_by values (26,12);
insert into test_connect_by values (15,10);
insert into test_connect_by values (15, 5);
insert into test_connect_by values (38,15);
insert into test_connect_by values (38,17);
insert into test_connect_by values (null, 38);
insert into test_connect_by values (null, 26);
insert into test_connect_by values (null, 18);
-------------------------------------------------------------------------
select lpad(' ',2*(level-1)) || to_char(child) s
from test_connect_by
start with parent is null
connect by prior child = parent;
--------------------------------------------------------------------------------
Then it shows :
-----------------------------------------------------
(Reporting Bugs btw, i cannot type more "space" before string in the entry, so i use '-' instead)
38
--15
----10
----5
------2
------3
----17
----9
----8
--6
26
--13
--1
--12
18
--11
--7
Posted by chenz at 11:29 AM | Comments (0)