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。