Oracle connet by prior 关键字的简单介绍和用法

        简单来讲,   connect by piror 这个关键字是用来保存树结构的关系表的.

   

一, 树结构简单介绍

        这里都简单解释一下树结构,   所谓树就是里面的成员除了最上级的成员外,  有且只有一个上级成员,  而且可以n个下级成员(n>=0). 最上级的成员没有上级成员.

        在现实中, 一间公司的人事结构可以用树来表示啦, 前提是1个员工不能有两个直属上司, 不然都不知道听哪个啊是不是.

        当然, 上面的解释是不严谨的, 严谨的解析就要去读一读数据结构啦.

二, 双亲表示法保存树结构.

2.1 存储原理

          例如我要保存1个简单的人事结构如下图:

          

                  1. Nedved

                      /           \   

         2. Jason       3.Hebe

            /            \

  4.David        5.Peter

                         /         \

                 6. Jacky    7.Bill

        怎么把上面的结构保存入数据库的一张表?  通常的做法是为表里的成员设置1个唯一的key (id), 而且设置1个parent_id, 存放的是这个成员的父成员的id.

        这种利用id 和 parent_id 来表示树结构的方法实际上就是 "双亲表示法" 详细可以参考下面链接:

        http://blog.csdn.net/nvd11/article/details/8872842

2.2 建表

         了解原理后, 建表就很简单了, 总之这张表必须包含3个列,  分别是 id, name , p_id

create table tree_1(
	id numeric(6),
	name varchar(20),
       	p_id numeric(6)
	); 

2.3 插入数据

         插入数据也不难,  只需要知道每个数据列的父级就ok.   但是我们上面提过了, 最上级的成员(Nedved) 是没有父级的, 一般会将Nedved行的p_id 设成NULL,  但是这种情况有时会影响检索(索引失效),  所以我们尽量避免NULL值出现在表中, 所以最好把Nedved的p_id 设成0.

          插入数据的语句如下:

delete from tree_1;

insert into tree_1 values(1, 'Nedved' , 0);
insert into tree_1 values(2, 'Jason'  , 1);
insert into tree_1 values(3, 'Hebe'   , 1);
insert into tree_1 values(4, 'David'  , 2);
insert into tree_1 values(5, 'Peter'  , 2);
insert into tree_1 values(6, 'Jacky'  , 5);
insert into tree_1 values(7, 'Bill'   , 5);
commit;

好了, 到这步为止我们已经把1个简单的树结构保存如一张关系数据表啦.

三, 检索数据

3.1 查找某个节点的父节点

       这个很简单了,  例如我想查找Jason上司是谁? (已知Jason的id是2)

       直接根据pid查找就ok啦.

select a.* from tree_1 a, tree_1 b
where a.id = b.p_id
and b.id = 2

输出:

	ID NAME 		      P_ID
---------- -------------------- ----------
	 1 Nedved			 0

3.2 查找某个节点的所有子节点.

这里的子节点是直某个节点的下一级的节点. 例如我想查找Jason的直接下属. 那么就应该找出David 和 Peter,  而Peter的下属Jacky和Bill就不属于Jason的子节点了.

方法都很简单,  查找p_id的值是Jason的id就ok啦.

SQL> select * from tree_1 where p_id = 2 /*Jason's id*/;

	ID NAME 		      P_ID
---------- -------------------- ----------
	 4 David			 2
	 5 Peter			 2

SQL>

3.3 查找某个节点的所有子孙节点.

这里的子孙就庞大了,  实际就是找1个节点的所有后代节点, 

例如我想找Jason的所有后代节点:

首先 当然包括David 和 Peter啦, 而Peter的后代也是Jason的后代啊, 所以要把Jacky 和 Bill也找出来.

这里看来并不复杂啊, 是因为我这个树结构太浅了, 实际上常规的sql语句写法是很难实现的,  假如Bill也有后代....  也就是说很难根据1个节点判断他的最深的后代去哪一层啊.

当然利用循环和临时表是能解决问题的, 但是sql语句就相当复杂了.

Oracle中有个关键字" connect by "  就是在这种情况下发挥作用的, 通常和"start with"字句一同使用.

例如这个小节, 我要找出Jason的所有直接和非直接的下属.

就可以利用如下语句:

select * from tree_1
start with id = 2  --Jason's id
connect by prior id = p_id;

输出:

	ID NAME 		      P_ID
---------- -------------------- ----------
	 2 Jason			 1
	 4 David			 2
	 5 Peter			 2
	 6 Jacky			 5
	 7 Bill 			 5

我们来分析下这条语句:

1)  首先  connect by prior id = p_id   也可写成 connect by p_id = prior id, 关键是prior的位置是在 id 的前面还是 parent id 的前面.

2) 如果prior 关键字在id前面, 就相当对1个树从根节点往下面遍历所有节点了!

3) 例如上面例子,  start with字句就是用来决定跟节点的位置的,  实际上就是遍历以Jason 为跟节点的字树啊. 看输出结果, 明显是1个先序遍历行为.  至于树的遍历知识可以参考我的博文中的数据结构部分.

4) 如果只想得出Jason的所有子孙节点, 不需要把Jason本身也select出来, 可以在 start with字句上面加上 where id <>2 子句.

3.4 查找某个节点的所有祖辈

例如我想找出Bill的所有直接和间接的上司(上司的上司).

Oracle 中 connect by字句同样可以方便的实现这个功能,

关键就是prior 的位置要在parent id前面

语句如下:

select * from tree_1
start with id = 7 --Bill's id
connect by id = prior p_id;

留意 prior 关键字的位置啊.

输出:

	ID NAME 		      P_ID
---------- -------------------- ----------
	 7 Bill 			 5
	 5 Peter			 2
	 2 Jason			 1
	 1 Nedved			 0