oracle start with connect by prior递归详述

1.描述：start with...connect by prior用于实现递归查询树形结构。

2.语法：

select *

from t_name

{where 条件一}

start with 条件二

connect by {prior parent=id|parent=prior id} 

3.语法详述：

(1)CONNECT BY子句说明每行数据将是按层次顺序检索，并规定将表中的数据连入树型结构的关系中。

(2)prior关键字的位置不同，所以决定了查询的方式不同。 prior修饰哪个字段，就是哪个字段为基准，然后迭代出另一个字段与之相对应的值的记录。

(3)在连接关系中，除了可以使用列名外，还允许使用列表达式。

(4)START WITH 子句为可选项，用来标识哪个节点作为查找树型结构的根节点。若该子句被省略，则表示所有满足查询条件的行作为根节点。start with不但可以指定一个根节点，还可以指定多个根节点。 

4.树结构详述：

(1)自顶向下查询时，既可以从根节点开始，又可以从任何其它节点开始，当以非根节点开始时，查询的是以此节点为根节点的子树。

(2)在树结构中每一个一层均有相对于根节点的层号level，并且根节点的level为1。不论以何节点开始，其根节点的层号level均为一，其余次之。

(3)在对树进行查询时，可以去掉表中的某些行，也可以减树中的某一分支进行查询，使用where子句可以限定要查询的单个节点，但不影响其他后辈节点(自顶向下检索)或前辈(自底向上检索)节点。

(4)遍历的结果和其它普通查询一样可以使用order by进行排序，同时不影响其对树的遍历方式。

5.遍历详述：

(1)先查看根节点。

(2)访问下一层的左边节点。

(3)继续访问下一层的左节点，直到节点没有左节点(即为叶子节点)。

(4)访问节点的靠右边的兄弟节点，若兄弟有子节点返回(2),若兄弟节点无子节点，继续访问靠右边的兄弟节点，知道没有可访问的兄弟节点。

(5)返回上一级，返回(4)。

(7)直至访问完整颗树。

注：次访问顺序可以牵强的理解为前序遍历，或者深度遍历，详情可以结合6.1截图以及7.7递归查询路径

6.树形结构实例

（1）以河南省行政区划分为例

（2）表结构构建

CREATE TABLE HeNan
(
ID NUMBER(10) NOT NULL,
PARENT_ID NUMBER(10),
NAME VARCHAR2(255 BYTE) NOT NULL
);
ALTER TABLE ADD
(
CONSTRAINT HeNan_PK PRIMARY KEY(ID)
);
ALTER TABLE HeNan ADD
(
CONSTRAINT HeNan_R01 FOREIGN KEY (PARENT_ID)
REFERENCES HeNan (ID)
);

（3）插入数据

INSERT INTO HeNan(ID,NAME) VALUES(1,'河南省');
INSERT INTO HeNan(ID,PARENT_ID,NAME) VALUES(2,1,'郑州市');
INSERT INTO HeNan(ID,PARENT_ID,NAME) VALUES(3,1,'开封市');
INSERT INTO HeNan(ID,PARENT_ID,NAME) VALUES(4,1,'洛阳市');
INSERT INTO HeNan(ID,PARENT_ID,NAME) VALUES(5,1,'商丘市');
INSERT INTO HeNan(ID,PARENT_ID,NAME) VALUES(6,1,'信阳市');
INSERT INTO HeNan(ID,PARENT_ID,NAME) VALUES(7,2,'中原区');
INSERT INTO HeNan(ID,PARENT_ID,NAME) VALUES(8,2,'金水区');
INSERT INTO HeNan(ID,PARENT_ID,NAME) VALUES(9,3,'龙亭区');
INSERT INTO HeNan(ID,PARENT_ID,NAME) VALUES(10,3,'金明区');
INSERT INTO HeNan(ID,PARENT_ID,NAME) VALUES(11,4,'老城区');
INSERT INTO HeNan(ID,PARENT_ID,NAME) VALUES(12,4,'西工区');
INSERT INTO HeNan(ID,PARENT_ID,NAME) VALUES(13,5,'梁园区');
INSERT INTO HeNan(ID,PARENT_ID,NAME) VALUES(14,5,'睢阳区');
INSERT INTO HeNan(ID,PARENT_ID,NAME) VALUES(15,6,'浉河区');
INSERT INTO HeNan(ID,PARENT_ID,NAME) VALUES(16,6,'平桥区');
INSERT INTO HeNan(ID,PARENT_ID,NAME) VALUES(17,13,'水池铺乡');
INSERT INTO HeNan(ID,PARENT_ID,NAME) VALUES(18,13,'观堂乡');
INSERT INTO HeNan(ID,PARENT_ID,NAME) VALUES(19,14,'路河乡');
INSERT INTO HeNan(ID,PARENT_ID,NAME) VALUES(20,14,'阎集乡');
INSERT INTO HeNan(ID,PARENT_ID,NAME) VALUES(21,17,'丁柿园村');
INSERT INTO HeNan(ID,PARENT_ID,NAME) VALUES(22,17,'关庄村');
INSERT INTO HeNan(ID,PARENT_ID,NAME) VALUES(23,17,'龚庄村');

7.查询操作

(1)查询一棵树的root节点

SQL> select * from henan where parent_id is null;

ID PARENT_ID NAME
---------- ---------- ---------------
1 河南省

(2)查询一个节点所有儿子节点

SQL> select * from henan where parent_id=5;

ID PARENT_ID NAME
---------- ---------- ---------------
13 5 梁园区
14 5 睢阳区

(3)查询一个节点所有子节点

方法一

SQL> select * from henan start with name='商丘市' connect by parent_id=prior id;

ID PARENT_ID NAME
---------- ---------- ---------------
5 1 商丘市
13 5 梁园区
17 13 水池铺乡
21 17 丁柿园村
22 17 关庄村
23 17 龚庄村
18 13 观堂乡
14 5 睢阳区
19 14 路河乡
20 14 阎集乡

10 rows selected.

方法二

SQL> SELECT * from henan start with name='商丘市' connect by prior id=parent_id;

ID PARENT_ID NAME
---------- ---------- ---------------
5 1 商丘市
13 5 梁园区
17 13 水池铺乡
21 17 丁柿园村
22 17 关庄村
23 17 龚庄村
18 13 观堂乡
14 5 睢阳区
19 14 路河乡
20 14 阎集乡

10 rows selected.

(4)查找一个节点所有上级节点

方法一

SQL> SELECT * from henan start with name='商丘市' connect by prior parent_id=id;

ID PARENT_ID NAME
---------- ---------- ---------------
5 1 商丘市
1 河南省

方法二

SQL> SELECT * from henan start with name='商丘市' connect by id=prior parent_id;

ID PARENT_ID NAME
---------- ---------- ---------------
5 1 商丘市
1 河南省

(5)查询一个节点亲兄弟节点

方法一

SQL> select *
2 from henan m
3 where exists (select * from henan n where m.parent_id=n.parent_id and id=23);

ID PARENT_ID NAME
---------- ---------- ---------------
21 17 丁柿园村
23 17 龚庄村
22 17 关庄村

方法二

SQL> select m.id ,m.parent_id, m.name
2 from henan m left join henan n on m.parent_id=n.parent_id
3 where n.id=23;

ID PARENT_ID NAME
---------- ---------- ---------------
21 17 丁柿园村
23 17 龚庄村
22 17 关庄村

(6)查询一个节点的簇兄弟(在同一层)

SQL> with tmp as(
2 select a.*, level leaf
3 from henan a
4 start with a.parent_id is null
5 connect by a.parent_id = prior a.id)
6 select *
7 from tmp
8 where leaf = (select leaf from tmp where id =17 );

ID PARENT_ID NAME LEAF
---------- ---------- --------------- ----------
17 13 水池铺乡 4
18 13 观堂乡 4
19 14 路河乡 4
20 14 阎集乡 4

(7)查询递归路径

SQL> select id, name, parent_id,
2 substr(sys_connect_by_path(name,'->'),3) path
3 from henan
4 start with name='商丘市'
5 connect by prior id=parent_id;

ID NAME PARENT_ID PATH
---------- --------------- ---------- ----------------------------------------
5 商丘市 1 商丘市
13 梁园区 5 商丘市->梁园区
17 水池铺乡 13 商丘市->梁园区->水池铺乡
21 丁柿园村 17 商丘市->梁园区->水池铺乡->丁柿园村
22 关庄村 17 商丘市->梁园区->水池铺乡->关庄村
23 龚庄村 17 商丘市->梁园区->水池铺乡->龚庄村
18 观堂乡 13 商丘市->梁园区->观堂乡
14 睢阳区 5 商丘市->睢阳区
19 路河乡 14 商丘市->睢阳区->路河乡
20 阎集乡 14 商丘市->睢阳区->阎集乡

10 rows selected.

(8)查询节点是否为叶节点(叶节点为1，非叶节点为0)

SQL> select m.*, connect_by_isleaf leaf
2 from henan m
3 start with parent_id is null
4 connect by parent_id = prior id;

ID PARENT_ID NAME LEAF
---------- ---------- --------------- ----------
1 河南省 0
2 1 郑州市 0
7 2 中原区 1
8 2 金水区 1
3 1 开封市 0
9 3 龙亭区 1
10 3 金明区 1
4 1 洛阳市 0
11 4 老城区 1
12 4 西工区 1
5 1 商丘市 0
13 5 梁园区 0
17 13 水池铺乡 0
21 17 丁柿园村 1
22 17 关庄村 1
23 17 龚庄村 1
18 13 观堂乡 1
14 5 睢阳区 0
19 14 路河乡 1
20 14 阎集乡 1
6 1 信阳市 0
15 6 浉河区 1
16 6 平桥区 1

23 rows selected.

参考博文:http://www.cnblogs.com/wanghonghu/archive/2012/08/31/2665945.html 

     :http://www.cnblogs.com/walk-the-Line/p/4882866.html