Oracle 树形结构遍历代码讲解

-- 遍历SQL

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
start with id=7
connect by id = prior pid;

　　
从根结点开始找子节点
　　

select * from test
start with id=2
connect by prior id = pid;

选择某层的下级节点

　　level 是oracle 保留的级数字段
　　

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

-- 查出 7839 总裁下面各级 员工的工资和

 select level , sum(sal)
 from emp
 group by level
 start with empno = 7839
 connect by prior empno = mgr