Oracle层级查询语句（hierarchical query）connect by 用法详解

Oracle Connect By Prior用法

 

摘要：

1、connect by中的条件就表示了父子之间的连接关系，比如 connect by id=prior  pid。

2、prior，表示在一表上，prior所在列pid的某一值A的记录行的父亲是列id上值等于列pid上值A的记录行。

3、LEVEL伪列表示树的深度（或叫高度）。

oracle中的select语句可以用START WITH...CONNECT BYPRIOR子句实现递归查询（或叫树状查询），connect by 是结构化查询中用到的，

其基本语法是：

　　select ... from <TableName>

　　where <Conditional-1>

　　start with <Conditional-2>

　　connect by <Conditional-3>

　　;

　　<Conditional-1>：过滤条件，用于对返回的所有记录进行过滤。

　　<Conditional-2>：该限定条件，表示查询结果以谁作为起始根结点的。当然可以放宽该限定条件，以取得多个根结点，实际就是多棵树。

　　<Conditional-3>：连接条件，即表示不同节点间以该连接条件构成一个父子关系

或说是

1 2	[ START WITH condition ] CONNECT BY [ NOCYCLE  ] condition

或说

其中 connect by 与 start with 语句摆放的先后顺序不影响查询的结果，[where 条件1]可以不需要，若是出现则要放在 connect by 与 start with 语句之前，否则出错。

 [where 条件1]、[条件2]、[条件3]各自作用的范围都不相同：     

 [where 条件1]

是在根据“connect by [条件2] start with [条件3]”选择出来的记录中进行过滤，是针对单条记录的过滤， 不会考虑树的结构（最后的过滤）；

 [ connect by 条件2]

指定构造树的条件，以及对树分支的过滤条件，在这里执行的过滤会把符合条件的记录及其下的所有子节点都过滤掉；   

 [ start with 条件3]

限定作为搜索起始点的条件，如果是自上而下的搜索则是限定作为根节点的条件，如果是自下而上的搜索则是限定作为叶子节点的条件； 

要根据connect by 从上到下还是从下到上，来确定起始节点，可能是叶节点，也可能是父节点，这些开始节点可以是多个，并且包含这些节点。

oracle 11g r2貌似不支持 从下到上的遍历？？

oracle 10g支持从下到上的遍历？？

【oracle 的 start with ... connect by ...】

注释：

0、

层级查询语句（hierarchical query）中，where子句先被执行，再执行CONNECT BY子句以及其附属子句。

LEVEL 伪列表示树的深度（或叫高度）。

使用LEVEL伪列：

在具有树结构的表中，每一行数据都是树结构中的一个节点，由于节点所处的层次位置不同，所以每行记录都可以有一个层号。层号根据节点与根节点的距离确定。不论从哪个节点开始，该起始根节点的层号始终为1，根节点的子节点为2， 依此类推。

1、这里说的节点指的是层级查询语句（hierarchical query）中from子句里的表的每一数据行。

2、层级查询语句（hierarchical query）中，CONNECT BY子句是必选的，而START WITH子句是可选的，START WITH子句是用来修饰CONNECT BY子句的。

3、prior关键字放在CONNECT BY子句中。其中用PRIOR表示上一条记录，比如 CONNECT BY PRIOR id = parent_id就是说上一条记录的id 是本条记录的parent_id，即本记录的父亲是上一条记录。

例如，

connect by   parent_id =prior id

表示的是一数据行中列名为parent_id的列上的值为该行的父节点的编号值，而

父节点的编号值都是来源于表中列名为id的列上的各个值。

总之，prior放在connect by子句连接条件里的哪一边，哪一边就是父节点的编号值的来源，而connect by子句连接条件里等号另一边就是记录一数据行其对应的父节点的编号值。

4、

START WITH 子句为可选项，用来标识哪个节点作为查找树型结构的根节点。若该子句被省略，则表示所有满足查询条件（即where子句里的条件）的行作为根节点。具体例子见下文二大点里的注释。

START WITH： 不但可以指定一个根节点，还可以指定多个根节点。

“START WITH 子句被省略，则表示所有满足查询条件（即where子句里的条件）的行作为根节点”这个特性

可以用来证明

”层级查询语句（hierarchical query）中，where子句先被执行，再执行CONNECT BY子句以及其附属子句“。

5、

START WITH 子句和CONNECT BY子句是两个相互独立的子句，即并没有规定START WITH 子句出现的列就是要为CONNECT BY子句里那个带有关键字prior的列，START WITH 子句出现的列可以来自表里的任何列，也就是说START WITH 子句出现的列可以没有在START WITH 子句里出现，因为START WITH 子句的作用就是根据START WITH 子句的限定条件来筛选出哪些数据行作为根节点而已。例子，

select * from t2 start with id = 1 connect by prior id= root_id;

select *