`

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来为每个层级的字段提供不同的缩进
分享到:
评论

相关推荐

    Oracle start with.connect by prior子句实现递归查询

    ### Oracle Start With.Connect By Prior 子句实现递归查询 #### 概述 在Oracle数据库中,`Start With.Connect By Prior`子句是执行递归查询的一种强大工具,主要用于处理层次结构数据。这类数据通常存在于组织...

    树状数据库表:Oracle中start with...connect by prior子句用法

    总结来说,`START WITH...CONNECT BY PRIOR`子句是Oracle SQL处理树形数据的关键工具,通过它可以有效地查询和展示层次关系的数据。理解并熟练运用这个子句,能够帮助你在处理具有层级结构的业务场景时更加游刃有余...

    ORACLE查询树型关系(connect_by_prior_start_with)

    Oracle 查询树型关系是指使用 START WITH 和 CONNECT BY 子句来实现 SQL 的层次查询。从 Oracle 9i 开始,可以通过 SYS_CONNECT_BY_PATH 函数实现将父节点到当前行内容以“path”或者层次元素列表的形式显示出来。 ...

    Oracle递归树形结构查询功能

    在进行递归查询优化时,要注意避免无限循环和性能问题,确保`CONNECT BY`条件正确无误,必要时还可以使用`CYCLE`子句来检测并处理循环引用。同时,合理利用索引可以显著提升查询效率。 总之,Oracle的递归树形结构...

    connect by的使用探索

    其中,`START WITH`子句用于指定查询的起始节点,`CONNECT BY`子句用于指定查询的递归条件。 Connect By的执行原理 Connect By语句的执行原理可以用以下程序来说明: ```sql for rec in (select * from some_table...

    Oracle_start_with_connect_by_prior_用法

    3. **过滤条件的影响**:`WHERE`子句是在已经通过`START WITH`和`CONNECT BY PRIOR`确定的记录集中进一步筛选,它不会影响到递归的路径。 #### 五、注意事项 - 在使用`START WITH`和`CONNECT BY PRIOR`时,需要确保...

    Oracle递归查询start with connect by prior的用法

    Oracle的递归查询功能强大,通过`START WITH`和`CONNECT BY`子句可以有效地处理层次结构数据。理解`PRIOR`关键字的使用至关重要,因为它决定了层次结构的导航方向。在实际业务中,合理利用这些特性可以帮助我们轻松...

    Oracle 递归函数介绍

    其中,`START WITH` 子句指定了递归查询的起始点,`CONNECT BY` 子句指定了递归查询的连接条件。 在上面的示例代码中,我们创建了一个名为 `T_DEPT_HP` 的表,并插入了一些示例数据。然后,我们可以使用递归函数来...

    10.2.0.3版本 with改造递归查询

    Oracle数据库支持通过`CONNECT BY`和`START WITH`子句实现递归查询。递归查询通常用于处理层次结构数据,如组织结构、文件系统等。 ##### 2. WITH子句 WITH子句是Oracle SQL中的一个特性,它允许用户定义一个临时...

    oracle递归、迭代

    在Oracle中实现递归查询需要用到`START WITH... CONNECT BY PRIOR`语法。 #### 三、递归查询的基本语法 递归查询的基本语法如下: ```sql SELECT column_name(s) FROM table_name START WITH start_condition ...

    oracle树结构查询.DOC

    Oracle数据库系统支持对树状结构数据的查询,这种查询方式主要通过`START WITH`和`CONNECT BY`子句实现。这两个子句是Oracle特有的,用于处理层级关系的数据,例如组织结构、产品分类或者树形菜单等。理解并掌握这两...

    connect_by_prior_递归算法

    ### Oracle中的Connect By Prior递归算法详解 ...通过正确地设置`START WITH`、`CONNECT BY`和`WHERE`子句,可以有效地查询和分析复杂的数据结构。理解其工作原理有助于更好地利用Oracle的功能来满足各种业务需求。

    在ORACLE、MSSQL、MYSQL中树结构表递归查询的实现.pdf

    CONNECT BY子句通常配合START WITH子句使用,其中START WITH指定了递归的起点,而CONNECT BY用于定义父子关系。CONNECT BY子句中的PRIOR关键字用于表示层级之间的连接,它指向前一个步骤得到的列值。例如,如果当前...

    oracle递归查询的例子

    - **START WITH 子句**:这里指定了递归查询的起点,即当 `relation` 字段值为 `1` 的记录作为根节点开始查询。 - **CONNECT BY PRIOR 子句**:定义了递归关系。在这里,`PRIOR id` 表示当前记录的父节点(上一级)...

    Oracle查询树形结构

    在 Oracle 中,查询树形结构可以使用 START WITH...CONNECT BY PRIOR 子句实现递归查询。其基本语法是: SELECT * FROM tablename START WITH cond1 CONNECT BY cond2 WHERE cond3; 其中,COND1 是根结点的限定...

    Oracle中的树状查询(递归查询)

    递归查询基于`CONNECT BY`子句,它允许我们定义一个起始点并根据特定条件进行递归。`PRIOR`关键字用于指定当前行与父行的关系。通过这些工具,Oracle可以构建出一个层次结构,展示出数据的树状关系。 2. **基本...

    Oracle递归SQL学习

    通过理解`start with`、`connect by prior`和`order siblings by`的用法,我们可以有效地展示和操作层次数据。不过,实际应用时要注意性能优化,避免因递归深度过大而导致的问题,并考虑在模板渲染时的替代方案。

    oracle菜单树查询

    oracle菜单树查询 使用实例 使用START WITH CONNECT BY PRIOR子句实现递归查询

    在db2和oracle中的对树的递归查询语句

    同时,为了防止无限递归,通常会设置一个最大递归深度,比如Oracle的`CONNECT_BY_ISCYCLE`和DB2的`WITH RECURSIVE`中的循环检测。 在实际应用中,了解并熟练掌握这些递归查询技巧对于处理树形结构的数据至关重要。...

    oracle-tree-sql.rar_oracle

    Oracle提供了一种称为“连接查询”(Connect By)的特有语法,使得我们可以方便地对具有层级关系的数据进行检索。本资料"oracle-tree-sql.rar_oracle"主要探讨的就是如何在Oracle中使用SQL查询树形结构数据。 一、...

Global site tag (gtag.js) - Google Analytics