Sample1:
with temp as
(
select 'Z001' userno, 'Jeff' username,'X059' usermgrno,1 useractive from dual
union
select 'X059' userno, 'Kevin' username,'X043' usermgrno,1 useractive from dual
union
select 'X040' userno, 'Judy' username,'B001' usermgrno,1 useractive from dual
union
select 'X043' userno, 'David' username,'X040' usermgrno,0 useractive from dual
union
select 'B001' userno, 'Andy' username,'A001' usermgrno,1 useractive from dual
union
select 'A001' userno, 'Frank' username,'' usermgrno,1 useractive from dual
union
select 'C001' userno, 'Cindy' username,'B001' usermgrno,1 useractive from dual
)
--select * from temp;
--to check all his supervisor
select userno,lpad(username,(7-level)* 5,'-'),useractive from temp t
where t.useractive=1
start with userno='Z001'
connect by userno = prior usermgrno;
Sample2
--to check all his descendents
with temp as
(
select 'Z001' userno, 'Jeff' username,'X059' usermgrno,1 useractive from dual
union
select 'X059' userno, 'Kevin' username,'X043' usermgrno,1 useractive from dual
union
select 'X040' userno, 'Judy' username,'B001' usermgrno,1 useractive from dual
union
select 'X043' userno, 'David' username,'X040' usermgrno,0 useractive from dual
union
select 'B001' userno, 'Andy' username,'A001' usermgrno,1 useractive from dual
union
select 'A001' userno, 'Frank' username,'' usermgrno,1 useractive from dual
union
select 'C001' userno, 'Cindy' username,'B001' usermgrno,1 useractive from dual
)
select userno,lpad(username,(level)* 4,'-'),useractive from temp t
where t.useractive=1
start with userno='A001'
connect by usermgrno = prior userno;
Sample3
with temp2 as
(
select 'a' name, '-1' p_name, 3 num from dual
union
select 'b' name, '-1' p_name, 4 num from dual
union
select 'c' name, 'a' p_name, 5 num from dual
union
select 'd' name, 'b' p_name, 6 num from dual
union
select 'e' name, 'c' p_name, 7 num from dual
union
select 'f' name, 'd' p_name, 8 num from dual
union
select 'g' name, 'e' p_name, 9 num from dual
)
--select * from temp2;
--all the descendents
select name,p_name,(select sum(num) from temp2 start with name=t.name connect by p_name =prior name) num
from temp2 t
where t.p_name='-1';
综上所述， 如果一个表有层次关系可以向两个方向递归：向上、向下， 拿第一张表做列子：
- 向上：connect by UserNo = prior UserMgrNo. 既然是向上， 那么起点就是层次关系中的子孙。 ‘Z001’满足这一条件。 如果你start with UserNo=’A001’,恐怕找不出结果。 因为他已经是表中级别最高的了。 他没有上级。
- 向下：connect by UserMgrNo = prior UserNo, 既然是向下， 那么起点应该是层次关系中的父辈。 ‘A001’满足这一条件。
如此你看出什么没有？
可以看去我向那个方向搜索， 我就把该字段放在prior这头。 如果我要搜他的经理，我就用prior UserMgrNo. 如果我搜它的员工， 我就用prior UserNo.
那么试着想想第三个列子：
我想在要搜所有孩子的NUM和， 那么是不是该用prior name呢？