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 m3 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.name2 from henan m left join henan n on m.parent_id=n.parent_id3 where n.id=23;ID PARENT_ID NAME---------- ---------- ---------------21 17 丁柿园村23 17 龚庄村22 17 关庄村

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

SQL> with tmp as(2 select a.*, level leaf3 from henan a4 start with a.parent_id is null5 connect by a.parent_id = prior a.id)6 select *7 from tmp8 where leaf = (select leaf from tmp where id =17 );ID PARENT_ID NAME LEAF---------- ---------- --------------- ----------17 13 水池铺乡 418 13 观堂乡 419 14 路河乡 420 14 阎集乡 4

(7)查询递归路径

SQL> select id, name, parent_id,2 substr(sys_connect_by_path(name,'->'),3) path3 from henan4 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 leaf2 from henan m3 start with parent_id is null4 connect by parent_id = prior id;ID PARENT_ID NAME LEAF---------- ---------- --------------- ----------1 河南省 02 1 郑州市 07 2 中原区 18 2 金水区 13 1 开封市 09 3 龙亭区 110 3 金明区 14 1 洛阳市 011 4 老城区 112 4 西工区 15 1 商丘市 013 5 梁园区 017 13 水池铺乡 021 17 丁柿园村 122 17 关庄村 123 17 龚庄村 118 13 观堂乡 114 5 睢阳区 019 14 路河乡 120 14 阎集乡 16 1 信阳市 015 6 浉河区 116 6 平桥区 123 rows selected.

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

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