Oracle的start with connect by prior 递归查询

这个子句主要是用于B树结构类型的数据递归查询，给出B树结构类型中的任意一个结点，遍历其最终父结点或者子结点。

创建测试表：

create table a_test
( parentid varchar2(10),
  subid    varchar2(10));

insert into a_test values ( '1', '2' );
insert into a_test values ( '1', '3' );
insert into a_test values ( '2', '4' );
insert into a_test values ( '2', '5' );
insert into a_test values ( '3', '6' );
insert into a_test values ( '3', '7' );
insert into a_test values ( '5', '8' );
insert into a_test values ( '5', '9' );
insert into a_test values ( '7', '10' );
insert into a_test values ( '7', '11' );
insert into a_test values ( '10', '12' );
insert into a_test values ( '10', '13' );

commit;

select * from a_test;

结果如下：

对应B树结构为：

先介绍相关命令含义：

start with 子句：遍历起始条件，有个小技巧，如果要查父结点，这里可以用子结点的列，反之亦然。

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

order by 子句：排序。

举例1：

--父节点遍历
select t.parentid, t.subid, level
  from a_test t
 start with t.subid = '7'
connect by subid = prior parentid
 order by level desc;

结果如下:

举例2：

--父节点遍历
select t.parentid,t.subid,level
from a_test t
start with t.parentid = '7'
connect by prior subid = parentid
order by level desc;

查询结果如下：

关于start with子句中选择不同的列的区别：

原意是要以7为父结点，遍历其子结点，左图取的是父结点列的值，结果符合原意；右图取的是子结点列的值，结果多余的显示了7 的父结点3。

结果可看出多出了2条冗余数据，均为长辈节点：

分析如下:(建议: 需要查询父节点行或祖先记录行时，可以用向上查询且后接子节点列)

注意事项：

1. start with 仅仅只是指定从那个节点开始，并不是上下查询的判断

2. start with 在向下查询时，仅有一点不同，后接子节点列则查询会比后接父节点列多一条记录，多出来的一条记录就是起始节点列，即，后接子节点列会查出起始行，后接父节点列则不会查询出起始行

3. 只有connect by语句中的prior关键字的位置决定了上下查询的方向，补充一下，如何判断上下查询

　　　(a). 死记 prior 子节点列 = 父节点列 [向下查询]，prior 父节点列 = 子节点列 [向上查询]

　　　(b). 另一种更方便一点，“prior 子节点列 = 父节点列 [向下查询]”  就把proir 翻译成"以...为查询条件",那么就是这样翻译的"以子节点列为查询条件，查询父节点列"，举例:"prior emp_id = lead_id" 翻译之后就是"以emp_id为查询条件查询lead_id"，即向下查询，这样理解起来是不是感觉很简单了;

4. 向上查询的两种特殊情况，(1). 后接子节点列   (2). 后接父节点列

5.多出的冗余数据会在如下情况中出现

　(a). 向上查询且start with 后接父节点列时　　　　　(b). sql语句中不包含start with 关键字时

参考文章：https://www.cnblogs.com/benbenduo/p/4588612.html

                  https://www.cnblogs.com/king-xg/p/6927541.html