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

末蓝、 2022-02-03 14:45 588阅读 0赞

##     ##

简单来讲,   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 关键字的位置啊.