Oracle用Start with...Connect By子句递归查询(转)

一、语法
大致写法：select * from some_table [where 条件1] connect by [条件2]startwith[条件3];
其 中 connect by 与startwith语句摆放的先后顺序不影响查询的 结果，[where 条件1]可以不需要。
[where 条件1]、[条件2]、[条件3]各自作用的范围都不相同：

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

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

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

示 例：
假如有如下结构的表：some_table(id,p_id,name)，其中p_id保存父记录的id。
select * from some_table t where t.id!=123 connect by prior t.p_id=t.id and t.p_id!=321startwitht.p_id=33 or t.p_id=66;

对prior的说明：
    prior存在于[条件2]中，可以不要，不要的时候只能查找到符合“startwith[条件3]”的记录，不会在寻找 这些记录的子节点。要的时候有两种写法：connect by prior t.p_id=t.id 或 connect by t.p_id=prior t.id，前一种写法表示采用自上而下的搜索方式（先找父节点然后找子节点），后一种写法表示采用自下而上的搜索方式（先找叶子节点然后找父节点）。

二、 执行原理
connect by...startwith...的执行原理可以用以下一段 程序的执行以及对存储过程RECURSE()的调用来说明：

/* 遍历表中的每条记录，对比是否满足startwith后的条件，如果不满足则继续下一 条，
如果满足则以该记录为根节点，然后调用RECURSE()递归寻找该节点下的子节点，
如 此循环直到遍历完整个表的所有记录 。*/
for rec in (select * from some_table) loop
if FULLFILLS_START_WITH_CONDITION(rec) then
    RECURSE(rec, rec.child);
end if;
end loop;

/* 寻找子节点的存储过程*/
procedure RECURSE (rec in MATCHES_SELECT_STMT, new_parent IN field_type) is
begin
APPEND_RESULT_LIST(rec); /*把记录加入结果集合中*/
/*再次遍历表中的所有记录，对比是否满足connect by后的条件，如果不满足则继续下一条，
如果满足则再以该记录为根节点，然后调用RECURSE()继续递归寻找该节点下的子节点，
如此循环直到找至叶子节点。*/
for rec_recurse in (select * from some_table) loop
    if FULLFILLS_CONNECT_BY_CONDITION(rec_recurse.child, new_parent) then
      RECURSE(rec_recurse,rec_recurse.child);
    end if;
end loop;
end procedure RECURSE;

三、使用探讨
    从上面的执行原理可以看到“connect by...startwith...”构造树的方式是：（1） 如果是自上而下方式，则把表中的每一条记录都作为根节点来生成树，所以表中有多少条记录就会构造出多少棵树。（2）如果是自下而上的搜索方式，则把表中的 每一条记录都作为叶子节点来生成分支，所以表中有多少条记录就会生成多少条分支。
    因此如果表中的记录不是严格遵照每条记录都只能有一个父记录的原则，那么就可能有部分记录会存在于多棵树中，那么在查找记录的时候就可能会出现找到多条重 复记录的异常情况。[align=left][/align]
Start with...Connect By子句递归查询一般用于一个表维护树形结构的应用。
创建示例表：
CREATE TABLE TBL_TEST
(
  ID    NUMBER,
  NAME  VARCHAR2(100 BYTE),
  PID   NUMBER                                  DEFAULT 0
);

插入测试数 据：
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('1','10','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('2','11','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('3','20','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('4','12','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('5','121','2');

从Root往树末梢递归
select * from TBL_TEST
start with id=1
connect by prior id = pid

从末梢往树ROOT递归
select * from TBL_TEST
start with id=5
connect by prior pid = id
=====
对于oracle进行简单树查询(递归查询)
DEPTID PAREDEPTID NAME
NUMBER NUMBER CHAR (40 Byte)
部门id 父部门id(所属部门id) 部门名称
通过子节点向根节点追朔.


select * from persons.dept start with deptid=76 connect by prior paredeptid=deptid
通 过根节点遍历子节点.


select * from persons.dept start with paredeptid=0 connect by prior deptid=paredeptid

可通过level 关键字查询所在层次.

select a.*,level from persons.dept a start with paredeptid=0 connect by prior deptid=paredeptid

再次复习一下:start with ...connect by 的用法，start with后面所跟的 就是就是递归的种子。

递归的种子也就是递归开始的地方connect by 后面的"prior" 如果缺省：则只能查询到符合条件的起始行，并不进行递归查询；
connect by prior 后面所放的字段是有关系的，它指明了查询的方向。

练习： 通过子节点获得顶节点


select FIRST_VALUE(deptid) OVER (ORDER BY LEVEL DESC ROWS UNBOUNDED PRECEDING) AS firstdeptid from persons.dept start with deptid=76 connect by prior paredeptid=deptid 

====这 种方法只是当表里就有一颗树,多棵树怎么办？



*************************************************************

Oracle实现多级分组管理的一次性递归查询
Oracle的递归用法：

从本级往上递归，如果想将结果反序显示，可以使用LEVEL伪例或rownum，并降序排列即可。
select *    
    from "MONITOR"."GROUP_INFO"
    start with group_id=72                       --从哪个起始id开始递归
    connect by group_id = prior parent_group_id  --从本级往上级递归，找出所有group_id等于当前parent_group_id的行

从本级往下递归
select *    
    from "MONITOR"."GROUP_INFO"
    start with group_id=72                       --从哪个起始id开始递归
    connect by parent_group_id = prior group_id  --从本级往下级递归，找出所有parent_group_id等于当前group_id的行

递归并排除指定分支
select *    
    from "MONITOR"."GROUP_INFO"
    start with group_id=72                       --从哪个起始id开始递归
    connect by parent_group_id = prior group_id  --从本级往下级递归，找出所有parent_group_id等于当前group_id的行
    and group_id<>73                             --排除group_id等于73的分支，即不包括部门73及下级部门

递归并排除指定的行
select *    
    from "MONITOR"."GROUP_INFO"
    where group_id<>73                           --仅不包括部门73，但包括其下级部门
    start with group_id=72                       --从哪个起始id开始递归
    connect by parent_group_id = prior group_id  --从本级往下级递归，找出所有parent_group_id等于当前group_id的行

递归并将结果排序
select *    
    from "MONITOR"."GROUP_INFO"
    start with group_id=72                       --从哪个起始id开始递归
    connect by parent_group_id = prior group_id  --从本级往下级递归，找出所有parent_group_id等于当前group_id的行
    order by group_id asc                        --将查询结果按group_id升序

递归并将同一层级的结果排序，该方法可以将所有结果按层级顺序排序，并将相关的层级放在一起
select *    
    from "MONITOR"."GROUP_INFO"
    start with group_id=72                       --从哪个起始id开始递归
    connect by parent_group_id = prior group_id  --从本级往下级递归，找出所有parent_group_id等于当前group_id的行
    order SIBLINGS by group_id asc               --将同一层级的查询结果按group_id升序





参考网上的例子：
　　　Oracle 实在太强了，本篇文章详细介绍了Oracle的递归查询语法，利用此语法，可以方便地实现递归的双向查询：
　　-- Tirle        : Recursion query for TREE with "connect by/start with"

　　-- Author       : Rake Gao

　　-- Create Date : 2005-08-22

　　-- Version      : 2.0

　　-- Last Modify : 2005-08-22

　　目 录

　　一、测试准备

　　二、实现各种查询要求

　　三、要点总结

　　正 文

　　一、测试准备

　　1、先假设有如下部门结构。

　　1

　　/ \

　　2    3

　　/\    /|\

　　4 5 6 7 8

　　2、然后建立测试表和数据。

　　drop table t_dept_temp;

　　create table t_dept_temp(

　　DEPT_ID    NUMBER(2)    NOT NULL,

　　PARENT_ID NUMBER(2)    ,

　　DEPT_NAME VARCHAR2(10) ,

　　AMOUNT     NUMBER(3)           --人数

　　);

　　delete t_dept_temp;

　　insert into t_dept_temp (DEPT_ID,PARENT_ID,DEPT_NAME,AMOUNT) values (1,null,'1'    ,2);

　　insert into t_dept_temp (DEPT_ID,PARENT_ID,DEPT_NAME,AMOUNT) values (2,1   ,'1-2' ,15);

　　insert into t_dept_temp (DEPT_ID,PARENT_ID,DEPT_NAME,AMOUNT) values (3,1   ,'1-3' ,8);

　　insert into t_dept_temp (DEPT_ID,PARENT_ID,DEPT_NAME,AMOUNT) values (4,2   ,'1-2-4',10);

　　insert into t_dept_temp (DEPT_ID,PARENT_ID,DEPT_NAME,AMOUNT) values (5,2   ,'1-2-5',9);

　　insert into t_dept_temp (DEPT_ID,PARENT_ID,DEPT_NAME,AMOUNT) values (6,3   ,'1-3-6',17);

　　insert into t_dept_temp (DEPT_ID,PARENT_ID,DEPT_NAME,AMOUNT) values (7,3   ,'1-3-7',5);

　　insert into t_dept_temp (DEPT_ID,PARENT_ID,DEPT_NAME,AMOUNT) values (8,3   ,'1-3-8',6);

　　commit;

　　SQL> select * from t_dept_temp;

　　DEPT_ID PARENT_ID DEPT_NAME AMOUNT

　　------- --------- ---------- ------

　　1           1               2

　　2         1 1-2            15

　　3         1 1-3             8

　　4         2 1-2-4          10

　　5         2 1-2-5           9

　　6         3 1-3-6          17

　　7         3 1-3-7           5

　　8         3 1-3-8           6

　　3、调整一下输出格式

　　col DEPT_ID format A10;

　　二、接下来实现各种查询要求

　　1、部门2及其所有下级部门。

　　SELECT LPAD(' ',2*(LEVEL - 1), ' ')||DEPT_ID AS DEPT_ID,

　　PARENT_ID,DEPT_NAME,AMOUNT

　　FROM t_dept_temp

　　CONNECT BY PARENT_ID = PRIOR DEPT_ID -- 找出所有PARENT_ID等于当前记录DEPT_ID的记录。

　　START WITH DEPT_ID = 2                -- 从部门2开始递归查询。

　　;

　　DEPT_ID    PARENT_ID DEPT_NAME AMOUNT

　　---------- --------- ---------- ------

　　2                  1 1-2            15

　　4                2 1-2-4          10

　　5                2 1-2-5           9

　　2、部门4及其所有上级部门

　　SELECT LPAD(' ',2*(LEVEL - 1), ' ')||DEPT_ID AS DEPT_ID,

　　PARENT_ID,DEPT_NAME,AMOUNT

　　FROM T_DEPT_TEMP

　　CONNECT BY PRIOR PARENT_ID = DEPT_ID -- 找出所有DEPT_ID等于当前记录PARENT_ID的记录

　　START WITH DEPT_ID = 4               -- 从部门4开始递归查询。

　　;

　　DEPT_ID    PARENT_ID DEPT_NAME AMOUNT

　　---------- --------- ---------- ------

　　4                  2 1-2-4          10

　　2                1 1-2            15

　　1                1               2
　　3、部门1的所有下级部门。

　　SELECT LPAD(' ',2*(LEVEL - 1), ' ')||DEPT_ID AS DEPT_ID,

　　PARENT_ID,DEPT_NAME,AMOUNT

　　FROM T_DEPT_TEMP

　　START WITH DEPT_ID = 1

　　CONNECT BY PARENT_ID = PRIOR DEPT_ID;

　　DEPT_ID    PARENT_ID DEPT_NAME AMOUNT

　　---------- --------- ---------- ------

　　1                    1               2

　　2                1 1-2            15

　　4              2 1-2-4          10

　　5              2 1-2-5           9

　　3                1 1-3             8

　　6              3 1-3-6          17

　　7              3 1-3-7           5

　　8              3 1-3-8           6

　　4、部门1及其所有下级部门，但是不包括部门3及其下级部门。（排除树枝）

　　SELECT LPAD(' ',2*(LEVEL - 1), ' ')||DEPT_ID AS DEPT_ID,

　　PARENT_ID,DEPT_NAME,AMOUNT

　　FROM T_DEPT_TEMP

　　START WITH DEPT_ID = 1

　　CONNECT BY PARENT_ID = PRIOR DEPT_ID

　　AND DEPT_ID <> 3    -- 不包括部门3及其下属部门(部门3和6、7、8都没出现)

　　;

　　DEPT_ID    PARENT_ID DEPT_NAME AMOUNT

　　---------- --------- ---------- ------

　　1                    1               2

　　2                1 1-2            15

　　4              2 1-2-4          10

　　5              2 1-2-5           9

　　5、部门1及其所有下级部门，但是仅不包括部门3。（排除节点）

　　SELECT LPAD(' ',2*(LEVEL - 1), ' ')||DEPT_ID AS DEPT_ID,

　　PARENT_ID,DEPT_NAME,AMOUNT

　　FROM T_DEPT_TEMP

　　WHERE DEPT_ID <>3          -- 仅仅不包括部门3(输出结果中，3的下级部门6、7、8还是出现了)

　　START WITH DEPT_ID = 1

　　CONNECT BY PARENT_ID = PRIOR DEPT_ID -- 执行顺序where在connect by之后

　　;

　　DEPT_ID    PARENT_ID DEPT_NAME AMOUNT

　　---------- --------- ---------- ------

　　1                    1               2

　　2                1 1-2            15

　　4              2 1-2-4          10

　　5              2 1-2-5           9

　　6              3 1-3-6          17

　　7              3 1-3-7           5

　　8              3 1-3-8           6

　　6、部门1及其所有下级部门，且所有部门按照人数升序排列。

　　SELECT LPAD(' ',2*(LEVEL - 1), ' ')||DEPT_ID AS DEPT_ID,

　　PARENT_ID,DEPT_NAME,AMOUNT

　　FROM T_DEPT_TEMP

　　START WITH DEPT_ID = 1

　　CONNECT BY PARENT_ID = PRIOR DEPT_ID

　　ORDER BY AMOUNT ASC -- 排序在最后被执行，所以DEPT_ID完全被打乱了，而且层级关系也打乱了。

　　;

　　-- In a hierarchical query, do not specify either ORDER BY or GROUP BY,

　　-- as they will destroy the hierarchical order of the CONNECT BY results.

　　DEPT_ID    PARENT_ID DEPT_NAME AMOUNT

　　---------- --------- ---------- ------

　　1                    1               2

　　7              3 1-3-7           5

　　8              3 1-3-8           6

　　3                1 1-3             8

　　5              2 1-2-5           9

　　4              2 1-2-4          10

　　2                1 1-2            15

　　6              3 1-3-6          17
　　　7、部门1及其所有下级部门，每个部门的下一级部门之间，按照人数降序排列。(有同一上级的那些部门？？？

　　-- If you want to order rows of siblings of the same parent,

　　-- then use the ORDER SIBLINGS BY clause.

　　SELECT LPAD(' ',2*(LEVEL - 1), ' ')||DEPT_ID AS DEPT_ID,

　　PARENT_ID,DEPT_NAME,AMOUNT

　　FROM T_DEPT_TEMP

　　START WITH DEPT_ID = 1

　　CONNECT BY PARENT_ID = PRIOR DEPT_ID

　　ORDER SIBLINGS BY AMOUNT ASC -- 同属部门间排序

　　;

　　-- 输出结果可见，部门3、2作为一组进行排序，部门7、8、6一组，5、4一组。

　　DEPT_ID    PARENT_ID DEPT_NAME AMOUNT

　　---------- --------- ---------- ------

　　1                    1               2

　　3                1 1-3             8

　　7              3 1-3-7           5

　　8              3 1-3-8           6

　　6              3 1-3-6          17

　　2                1 1-2            15

　　5              2 1-2-5           9

　　4              2 1-2-4          10

　　三、要点总结

　　1、子句的语法书写顺序。

　　select -> from -> where -> start with -> connect by -> order by

　　where写在connect by后面就不行，报错。

　　2、子句的执行顺序

　　from -> start with -> connect by -> where -> select -> order by

　　执行顺序where在connect by之后，可以从例5证明。

　　可是书写SQL语句的时候，却只能写前面，注意理解。

　　3、如何理解和记忆“CONNECT BY PRIOR PARENT_ID = DEPT_ID ”的含义呢？

　　现在看这个例子似乎很直观，但是今后实际应用时，条件变化后，如何推断查询结果呢？

　　这里我自己总结一种方法，前提是要理解SQL语句执行时，是一条一条记录来处理的。

　　每条满足START WITH语句条件的记录被依次取出，暂且把每次被取出处理的记录，称为当前记录。

　　“PRIOR PARENT_ID”表明从当前记录得到PARENT_ID，

　　然后" = DEPT_ID"说明找到表中所有DEPT_ID等于当前记录PARENT_ID的记录，也就是找当前记录PARENT_ID所指向的记录。

　　因为PARENT_ID的取值含义是上级节点，所以说明是向树的根节点方向的搜索。（我的上级是谁？）

　　反之，如果是“CONNECT BY PARENT_ID = PRIOR DEPT_ID”，“PRIOR”在DEPT_ID一边，就是找所有PARENT_ID等于当前记录DEPT_ID

的记录，是向树的叶子方向的搜索。（谁的上级是我？）

　　找到结果记录集以后，从第一条记录开始递归处理，依此类推。

　　4、前序遍历

　　由于是递归处理，从例3可以看出，树的根节点向叶子节点递归查询时，查询节点的顺序是按照树的前序遍历进行的。

　　5、排序

　　例6和例7说明了两种排序的区别。

　　In a hierarchical query, do not specify either ORDER BY or GROUP BY, as they will destroy the hierarchical order of

the CONNECT BY results. If you want to order rows of siblings of the same parent, then use the ORDER SIBLINGS BY clause.

See order_by_clause.

　　6、伪列LEVEL

　　只能随CONNECT BY子句一起使用，是一个整数，代表递归的层次深度。也就是节点在树中所处深度。

　　根节点时等于1，根节点的叶子节点的深度等于2，依此类推。

　　LPAD(' ',2*(LEVEL - 1), ' ')||DEPT_ID 正是利用了LEVEL来为每个层级的字段提供不同的缩进