RobinHood

I want to be with you in my whole life!

Connect By –From Lily examples
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呢?

阅读更多
文章标签: c
个人分类: Oracle数据库学习
想对作者说点什么? 我来说一句

orcl 数据库代码

2013年09月16日 774B 下载

Arecont Vision 网络摄像头开发包

2010年05月03日 5.64MB 下载

VW Lily语音库去广告补丁

2012年07月27日 1KB 下载

Neospeech中文女声_Lily[1].part10

2012年02月22日 40.46MB 下载

没有更多推荐了,返回首页

不良信息举报

Connect By –From Lily examples

最多只允许输入30个字

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭