Oracle的start with ... connect by

数据准备：
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 ;

all

对应B树的结构为：

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’（包括自己）及其子节点进行递归查询，结果如下：

1

查询结果自己所有的后代节点（包括自己）

第二种：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’（包括自己）及其父节点进行递归查询，结果如下：

2

查询结果自己所有的前代节点（包括自己）

第三种：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’（不包括自己）子节点进行递归查询，结果如下：

3

查询结果自己所有的后代节点（不包括自己）。
分析：
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’（包括自己）的第一代孩子们及其父节点进行递归查询，结果如下：

4

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

如果有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。