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;