Oracle:递归查询详解

转载 2013年12月02日 17:08:27

本篇文章详细介绍了Oracle的递归查询语法,利用此语法,可以方便地实现递归的双向查询:

 

-- Tirle              : Recursion query for TREE with "connect by/start with"

-- Author          :

-- Create Date   :

-- Version         :

-- Last Modify    :

 

  目 

一、测试准备

二、实现各种查询要求

三、要点总结

 

 

  正 

一、测试准备

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

         

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

drop table t_dept_temp;

create table t_dept_temp(

  DEPT_ID       NUMBER(2)        NOT NULL,  --部门ID

  PARENT_ID   NUMBER(2)    ,                --上级部门ID

  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                     nbsp; -- 从部门4开始递归查询。

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及其所有下级部门,且所有部门按照人数升序排列。

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

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 递归查询详解

Oracle递归查询详解 , SYS_CONNECT_BY_PATH函数
  • xiaokui_wingfly
  • xiaokui_wingfly
  • 2015年01月22日 17:58
  • 4245

Oracle 树查询(递归查询) 与虚列level 结合

oracle中的select语句可以用START WITH...CONNECT BY PRIOR子句实现递归查询,connect by 是结构化查询中用到的,其基本语法是: select * from...
  • l271640625
  • l271640625
  • 2011年07月26日 17:18
  • 6015

在oracle中通过connect by prior来实现递归查询!

connect by 是结构化查询中用到的,其基本语法是: select ... from tablename start by cond1 connect by cond2 where cond3;...
  • apicescn
  • apicescn
  • 2007年02月16日 09:13
  • 15896

oracle 递归查询 + 条件 + 排序

from group where status=1 start with parentid=0 connect by prior groupid = parentid ORDER SIBLINGS ...
  • wichita
  • wichita
  • 2016年06月29日 16:58
  • 207

Oracle递归查询(目录结构查询)

参考:http://www.cnblogs.com/walk-the-Line/p/4882866.html使用Kettle资源库数据表操作: select ml from (select sys...
  • u010758605
  • u010758605
  • 2017年05月08日 14:22
  • 402

Oracle中的递归查询语法(start with...connect by prior)

select * from table1 start with id='0' connect by prior id = parentid     查询id等于0的节点下面的所有子节点     s...
  • u014227966
  • u014227966
  • 2014年04月16日 10:48
  • 1151

oracle中的层级递归查询操作

oracle中的层级操作非常方便,在使用之后爱不释手,以前要实现该种数据查询操作,需要非常复杂的实现过程。在oracle中通过connect by可以实现前面的目的,通常情况下层级查询基本都能实现递归...
  • zouqingfang
  • zouqingfang
  • 2013年10月24日 16:11
  • 2947

oracle递归查询实例

Oracle递归查询 创建表  建表语句: CREATE TABLE SC_DISTRICT ( ID NUMBER(10) ...
  • baidu_25310663
  • baidu_25310663
  • 2015年07月15日 10:49
  • 929

oracle 递归查询,向上和向下遍历

--查询全部资源信息  select * from urm_class_info  --向上遍历树,找到路径直到根节点,指定的是parentid  select distinct classid...
  • wangchuntao1
  • wangchuntao1
  • 2016年10月25日 09:40
  • 1943

Hibernate使用Oracle的递归查询。

表结构:Create TABLE { ID NUMBER NAME VARCHAR2 PARENT_ID NUMBER }
  • wwcwolf
  • wwcwolf
  • 2014年07月03日 11:08
  • 478
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Oracle:递归查询详解
举报原因:
原因补充:

(最多只允许输入30个字)