Oracle資料庫遍曆樹形結構表

1、從根結點開始找子節點

/*
create table test (id  int ,pid int ,name varchar(10));
insert into test values ( 1, 0 ,'n1' );
insert into test values(  2, 1 ,'n12');
insert into test values(  3, 1 ,'n13');
insert into test values(  4, 2 ,'n21');
insert into test values(  5, 2 ,'n22');
insert into test values(  6, 3 ,'n31');
insert into test values(  7, 3 ,'n32');
insert into test values(  8, 4 ,'n211');
insert into test values(  9, 4 ,'n212');
*/
/*
從根結點開始找子節點(可以指定節點層級)
*/

select id,pid,name ,level from test
--where level =3
start with id =2
connect by prior id =  pid;

 

2、從葉結點開始找根節點

/*
create table test (id  int ,pid int ,name varchar(10));
insert into test values ( 1, 0 ,'n1' );
insert into test values(  2, 1 ,'n12');
insert into test values(  3, 1 ,'n13');
insert into test values(  4, 2 ,'n21');
insert into test values(  5, 2 ,'n22');
insert into test values(  6, 3 ,'n31');
insert into test values(  7, 3 ,'n32');
insert into test values(  8, 4 ,'n211');
insert into test values(  9, 4 ,'n212');
*/
/*
從葉結點開始找根節點
prior 關鍵字 與誰放在一起，就是找誰
根節點的父節點不能是他本身，那會造成死迴圈
*/

select *  from test /*where conditions*/
start with id=7
connect by id = prior pid;