oracle中 start with,Oracle的start with ... connect by prior ...

数据准备：

create table zxtable

( parentid varchar2(10),

subid varchar2(10)

);

insert into zxtable values ( ‘1’,‘2’);

insert into zxtable values ( ‘1’,‘3’);

insert into zxtable values ( ‘2’,‘4’);

insert into zxtable values ( ‘2’,‘5’);

insert into zxtable values ( ‘3’,‘6’);

insert into zxtable values ( ‘3’,‘7’);

insert into zxtable values ( ‘5’,‘8’);

insert into zxtable values ( ‘5’,‘9’);

insert into zxtable values ( ‘7’,‘10’);

insert into zxtable values ( ‘7’,‘11’);

insert into zxtable values ( ‘10’,‘12’);

insert into zxtable values ( ‘10’,‘13’);

commit;

select * from zxtable ;

对应B树的结构为：

start with 子句：遍历起始条件。如果要查父结点，这里用子结点的列，反之。

connect by 子句：连接条件。关键词prior，prior跟父节点列parentid放在一起，就是往父结点方向遍历；prior跟子结点列subid放在一起，则往叶子结点方向遍历。parentid、subid两列谁放在“=”前都无所谓，关键是prior跟谁在一起。

order by 子句：排序(desc降序、asc升序)。

Oracle的start with connect by prior主要是用于B树结构类型的数据递归查询，给出B树结构类型中的任意一个结点，遍历其最终父结点或者子结点。分为四种使用情况：

第一种：start with 子节点ID=’…’ connect by prior 子节点ID = 父节点ID

select parentid,subid,level from zxtable

start with subid='7'

connect by prior subid=parentid

order by level desc;

按照条件subid=‘7’，对’7’(包括自己)及其子节点进行递归查询，结果如下：

查询结果自己所有的后代节点(包括自己)

第二种：start with 子节点ID=’…’ connect by 子节点ID = prior 父节点ID

select parentid,subid,level from zxtable

start with subid='7'

connect by subid=prior parentid

order by level desc;

按照条件subid=‘7’，对’7’(包括自己)及其父节点进行递归查询，结果如下：

查询结果自己所有的前代节点(包括自己)

第三种：start with 父节点ID=’…’ connect by prior 子节点ID = 父节点ID

select parentid,subid,level from zxtable

start with parentid='7'

connect by prior subid=parentid

order by level desc;

按照条件parentid=‘7’，对’7’(不包括自己)子节点进行递归查询，结果如下：

查询结果自己所有的后代节点(不包括自己)。

分析：

connect by 子句中，prior跟subid在同一边，就是往叶子结点方向遍历去了。因为7有两个子结点，所以第一级中有两个结果(10和11)。10有两个子结点(12和13)，11无，所以第二级也有两个结果(12，13)。即12,13就是叶子结点。

第四种：start with 父节点ID=’…’ connect by 子节点ID = prior 父节点ID

select parentid,subid,level from zxtable

start with parentid='7'

connect by subid = prior parentid

order by level desc;

按照条件parentid=‘7’，对’7’(包括自己)的第一代孩子们及其父节点进行递归查询，结果如下：

查询结果自己的第一代后节点和所有的前代节点(包括自己)

如果有where 条件，如下：

select parentid,subid,level from zxtable

where 条件

start with parentid='7'

connect by subid = prior parentid

执行顺序为先执行start with … connect by prior，然后再按照where条件进行过滤。

注意：

以查询叶子结点(往下遍历)为例，仔细分析第一种和第三种查询结果，可明确start with子句中选择不同的列的区别：

结果很明显，原意是要以7为父结点，遍历其子结点，第三种取的是父结点列的值，结果符合原意；第一种取的是子结点列的值，结果多余的显示了7 的父结点3。