`

Oracle递归树select...start with... connect by ...prior 理解

阅读更多

Oracle树查询的最重要的就是select...start with... connect by ...prior 语法了。依托于该语法,我们可以将一个表形结构的中以树的顺序列出来。在下面列述了Oracle中树型查询的常用查询方式以及经常使用的与树查询相关的Oracle特性函数等,在这里只涉及到一张表中的树查询方式而不涉及多表中的关联等。

      以我做过的一个项目中的表为例,表结构如下:

Sql代码
1.CREATE TABLE FLFL  
2.(  
3.  ID      NUMBER                                NOT NULL,  
4.  MC      NVARCHAR2(20),  
5.  FLJB    NUMBER,  
6.  SJFLID  NUMBER  
7.) 
CREATE TABLE FLFL
(
  ID      NUMBER                                NOT NULL,
  MC      NVARCHAR2(20),
  FLJB    NUMBER,
  SJFLID  NUMBER
)      FLJB是作为树的级别,在很多查询中可以加快SQL的查询效率。在下面演示的功能基本上不使用这个关键字。

      SJFLID存储的是上级ID,如果是顶级父节点,该SJFLID为null(得补充一句,当初的确是这样设计的,不过现在知道,表中最好别有null记录,这会引起全文扫描,建议改成0代替)。

      我们从最基本的操作,逐步列出树查询中常见的操作,所以查询出来的节点以家族中的辈份作比方。

      1. 查找树中的所有顶级父节点(辈份最长的人)。 假设这个树是个目录结构,那么第一个操作总是找出所有的顶级节点,再根据该节点找到其下属节点。

Sql代码
1.SELECT * FROM flfl WHERE sjflid IS NULL; 
SELECT * FROM flfl WHERE sjflid IS NULL;      这是个引子,没用到树型查询。

      2.查找一个节点的直属子节点(所有儿子)。 如果查找的是直属子类节点,也是不用用到树型查询的。

Sql代码
1.SELECT * FROM flfl WHERE sjflid = 819459; 
SELECT * FROM flfl WHERE sjflid = 819459;      这个可以找到ID为819459的直属子类节点。

      3.查找一个节点的所有 直属子节点(所有后代)。

Sql代码
1.SELECT * FROM flfl START WITH ID = 819459 CONNECT BY sjflid = PRIOR ID; 
SELECT * FROM flfl START WITH ID = 819459 CONNECT BY sjflid = PRIOR ID;      这个查找的是ID为819459的节点下的所有直属子类节点,包括子辈的和孙子辈的所有直属节点。

      4.查找一个节点的直属父节点(父亲)。 如果查找的是节点的直属父节点,也是不用用到树型查询的。

Sql代码
1.SELECT b.* FROM flfl a JOIN flfl b ON a.sjflid = b.ID WHERE a.ID = 6758; 
SELECT b.* FROM flfl a JOIN flfl b ON a.sjflid = b.ID WHERE a.ID = 6758;      这个找到的是ID为6758的节点的直属父节点,要用到同一张表的关联了。

      5.查找一个节点的所有直属父节点(祖宗)。

Sql代码
1.SELECT * FROM flfl START WITH ID = 6758 CONNECT BY PRIOR sjflid = ID; 
SELECT * FROM flfl START WITH ID = 6758 CONNECT BY PRIOR sjflid = ID;      这里查找的就是ID为6758的所有直属父节点,打个比方就是找到一个人的父亲、祖父等。但是值得注意的是这个查询出来的结果的顺序是先列出子类节点再列出父类节点,姑且认为是个倒序吧。

      上面列出两个树型查询方式,第3条语句和第5条语句,这两条语句之间的区别在于prior关键字的位置不同,所以决定了查询的方式不同。 当sjflid = PRIOR ID时,数据库会根据当前的ID迭代出sjflid与该ID相同的记录,所以查询的结果是迭代出了所有的子类记录;而PRIOR ID = sjflid时,数据库会跟据当前的sjflid来迭代出与当前的sjflid相同的id的记录,所以查询出来的结果就是所有的父类结果。

      以下是一系列针对树结构的更深层次的查询,这里的查询不一定是最优的查询方式,或许只是其中的一种实现而已。

      6.查询一个节点的兄弟节点(亲兄弟)。

Sql代码
1.SELECT a.*  
2.  FROM flfl a  
3. WHERE EXISTS (SELECT *  
4.                 FROM flfl b  
5.                WHERE a.sjflid = b.sjflid AND b.ID = 6757); 
SELECT a.*
  FROM flfl a
 WHERE EXISTS (SELECT *
                 FROM flfl b
                WHERE a.sjflid = b.sjflid AND b.ID = 6757);      这里查询的就是与ID为6757的节点同属一个父节点的节点了,就好比亲兄弟了。

      7.查询与一个节点同级的节点(族兄弟)。 如果在表中设置了级别的字段,上表中的FLJB,那么在做这类查询时会很轻松,同一级别的就是与那个节点同级的,在这里列出不使用该字段时的实现!

Sql代码
1.WITH tmp AS 
2.     (SELECT     a.*, LEVEL lev  
3.            FROM flfl a  
4.      START WITH a.sjflid IS NULL 
5.      CONNECT BY a.sjflid = PRIOR a.ID)  
6.SELECT *  
7.  FROM tmp  
8. WHERE lev = (SELECT lev  
9.                FROM tmp  
10.               WHERE ID = 819394) 
WITH tmp AS
     (SELECT     a.*, LEVEL lev
            FROM flfl a
      START WITH a.sjflid IS NULL
      CONNECT BY a.sjflid = PRIOR a.ID)
SELECT *
  FROM tmp
 WHERE lev = (SELECT lev
                FROM tmp
               WHERE ID = 819394)       这里使用两个技巧,一个是使用了LEVEL来标识每个节点在表中的级别,还有就是使用with语法模拟出了一张带有级别的临时表。

      8.查询一个节点的父节点的的兄弟节点(伯父与叔父)。

Sql代码
1.WITH tmp AS 
2.     (SELECT     flfl.*, LEVEL lev  
3.            FROM flfl  
4.      START WITH sjflid IS NULL 
5.      CONNECT BY sjflid = PRIOR ID)  
6.SELECT b.*  
7.  FROM tmp b,  
8.       (SELECT *  
9.          FROM tmp  
10.         WHERE ID = 7004 AND lev = 2) a  
11. WHERE b.lev = 1  
12.UNION ALL 
13.SELECT *  
14.  FROM tmp  
15. WHERE sjflid = (SELECT DISTINCT x.ID  
16.                            FROM tmp x,  
17.                                 tmp y,  
18.                                 (SELECT *  
19.                                    FROM tmp  
20.                                   WHERE ID = 7004 AND lev > 2) z  
21.                           WHERE y.ID = z.sjflid AND x.ID = y.sjflid); 
WITH tmp AS
     (SELECT     flfl.*, LEVEL lev
            FROM flfl
      START WITH sjflid IS NULL
      CONNECT BY sjflid = PRIOR ID)
SELECT b.*
  FROM tmp b,
       (SELECT *
          FROM tmp
         WHERE ID = 7004 AND lev = 2) a
 WHERE b.lev = 1
UNION ALL
SELECT *
  FROM tmp
 WHERE sjflid = (SELECT DISTINCT x.ID
                            FROM tmp x,
                                 tmp y,
                                 (SELECT *
                                    FROM tmp
                                   WHERE ID = 7004 AND lev > 2) z
                           WHERE y.ID = z.sjflid AND x.ID = y.sjflid);       这里查询分成以下几步。首先,将第7个一样,将全表都使用临时表加上级别;其次,根据级别来判断有几种类型,以上文中举的例子来说,有三种情况:(1)当前节点为顶级节点,即查询出来的lev值为1,那么它没有上级节点,不予考虑。(2)当前节点为2级节点,查询出来的lev值为2,那么就只要保证 lev级别为1的就是其上级节点的兄弟节点。(3)其它情况就是3以及以上级别,那么就要选查询出来其上级的上级节点(祖父),再来判断祖父的下级节点都是属于该节点的上级节点的兄弟节点。 最后,就是使用UNION将查询出来的结果进行结合起来,形成结果集。

      9.查询一个节点的父节点的同级节点(族叔)。

      这个其实跟第7种情况是相同的。

Sql代码
1.WITH tmp AS 
2.     (SELECT     a.*, LEVEL lev  
3.            FROM flfl a  
4.      START WITH a.sjflid IS NULL 
5.      CONNECT BY a.sjflid = PRIOR a.ID)  
6.SELECT *  
7.  FROM tmp  
8. WHERE lev = (SELECT lev  
9.                FROM tmp  
10.               WHERE ID = 819394) - 1 
WITH tmp AS
     (SELECT     a.*, LEVEL lev
            FROM flfl a
      START WITH a.sjflid IS NULL
      CONNECT BY a.sjflid = PRIOR a.ID)
SELECT *
  FROM tmp
 WHERE lev = (SELECT lev
                FROM tmp
               WHERE ID = 819394) - 1      只需要做个级别判断就成了。

      基本上,常见的查询在里面了,不常见的也有部分了。其中,查询的内容都是节点的基本信息,都是数据表中的基本字段,但是在树查询中还有些特殊需求,是对查询数据进行了处理的,常见的包括列出树路径等。

      补充一个概念,对于数据库来说,根节点并不一定是在数据库中设计的顶级节点,对于数据库来说,根节点就是start with开始的地方。

      下面列出的是一些与树相关的特殊需求。

      10.名称要列出名称全部路径。

      这里常见的有两种情况,一种是是从顶级列出,直到当前节点的名称(或者其它属性);一种是从当前节点列出,直到顶级节点的名称(或其它属性)。举地址为例:国内的习惯是从省开始、到市、到县、到居委会的,而国外的习惯正好相反(老师说的,还没接过国外的邮件,谁能寄个瞅瞅 )。

      从顶部开始:

Sql代码
1.SELECT     SYS_CONNECT_BY_PATH (mc, '/')  
2.      FROM flfl  
3.     WHERE ID = 6498  
4.START WITH sjflid IS NULL 
5.CONNECT BY sjflid = PRIOR ID; 
SELECT     SYS_CONNECT_BY_PATH (mc, '/')
      FROM flfl
     WHERE ID = 6498
START WITH sjflid IS NULL
CONNECT BY sjflid = PRIOR ID;      从当前节点开始:

Sql代码
1.SELECT     SYS_CONNECT_BY_PATH (mc, '/')  
2.      FROM flfl  
3.START WITH ID = 6498  
4.CONNECT BY PRIOR sjflid = ID; 
SELECT     SYS_CONNECT_BY_PATH (mc, '/')
      FROM flfl
START WITH ID = 6498
CONNECT BY PRIOR sjflid = ID;      在这里我又不得不放个牢骚了。oracle只提供了一个sys_connect_by_path函数,却忘了字符串的连接的顺序。在上面的例子中,第一个 SQL是从根节点开始遍历,而第二个SQL是直接找到当前节点,从效率上来说已经是千差万别,更关键的是第一个SQL只能选择一个节点,而第二个SQL却是遍历出了一颗树来。再次PS一下。

      sys_connect_by_path函数就是从start with开始的地方开始遍历,并记下其遍历到的节点,start with开始的地方被视为根节点,将遍历到的路径根据函数中的分隔符,组成一个新的字符串,这个功能还是很强大的。

      11.列出当前节点的根节点。

      在前面说过,根节点就是start with开始的地方。

Sql代码
1.SELECT     CONNECT_BY_ROOT mc, flfl.*  
2.      FROM flfl  
3.START WITH ID = 6498  
4.CONNECT BY PRIOR sjflid = ID; 
SELECT     CONNECT_BY_ROOT mc, flfl.*
      FROM flfl
START WITH ID = 6498
CONNECT BY PRIOR sjflid = ID;      connect_by_root函数用来列的前面,记录的是当前节点的根节点的内容。
      12.列出当前节点是否为叶子。

      这个比较常见,尤其在动态目录中,在查出的内容是否还有下级节点时,这个函数是很适用的。

Sql代码
1.SELECT     CONNECT_BY_ISLEAF, flfl.*  
2.      FROM flfl  
3.START WITH sjflid IS NULL 
4.CONNECT BY sjflid = PRIOR ID; 
SELECT     CONNECT_BY_ISLEAF, flfl.*
      FROM flfl
START WITH sjflid IS NULL
CONNECT BY sjflid = PRIOR ID;      connect_by_isleaf函数用来判断当前节点是否包含下级节点,如果包含的话,说明不是叶子节点,这里返回0;反之,如果不包含下级节点,这里返回1。

      至此,oracle树型查询基本上讲完了,以上的例子中的数据是使用到做过的项目中的数据,因为里面的内容可能不好理解,所以就全部用一些新的例子来进行阐述。以上所有SQL都在本机上测试通过,也都能实现相应的功能,但是并不能保证是解决这类问题的最优方案(如第8条明显写成存储过程会更好),如果谁有更好的解决方案、或者有关oracle树查询的任何问题,欢迎留言讨论,以上的SQL有什么问题也欢迎大家留言批评。

 

分享到:
评论

相关推荐

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

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

    ORACLE查询树型关系(connect_by_prior_start_with)

    ORACLE 查询树型关系(connect_by_prior_start_with) Oracle 查询树型关系是指使用 START WITH 和 CONNECT BY 子句来实现 SQL 的层次查询。从 Oracle 9i 开始,可以通过 SYS_CONNECT_BY_PATH 函数实现将父节点到...

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

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

    Oracle递归树形结构查询功能

    递归树形结构查询主要依赖于`CONNECT BY`和`PRIOR`关键字,它们允许我们构建复杂的层级查询,以展示数据的层次关系。 在Oracle中,树形结构查询的基本语法如下: ```sql SELECT [LEVEL], * FROM table_name START ...

    MySQL多种递归查询方法.docx

    在Oracle数据库中,递归查询可以通过`START WITH CONNECT BY PRIOR`语句实现。此语句允许用户按照树状结构来检索数据。 ##### 1. `START WITH CONNECT BY PRIOR`用法详解 **基本语法**: ```sql SELECT * FROM ...

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

    select 1 from 表格 start with … connect by prior id = pId start with:表示以什么为根节点,不加限制可以写1=1,要以id为123的节点为根节点,就写为start with id =123 connect by:connect by是必须的,...

    connect_by_prior_递归算法

    ### Oracle中的Connect By Prior递归算法详解 #### 一、Connect By Prior 子句概述 在Oracle数据库中,`Connect By Prior`子句是一种非常有用的工具,尤其在处理具有层次结构的数据时。它允许用户以一种简洁的方式...

    Oracle_start_with_connect_by_prior_用法

    ### Oracle中的START WITH CONNECT BY PRIOR 用法详解 #### 一、概念介绍 在Oracle数据库中,`START WITH` 和 `CONNECT BY PRIOR` 是两个非常强大的特性,主要用于处理具有层级结构的数据。这两个特性可以帮助我们...

    Oracle_start_with_connect_by_prior_用法[文].pdf

    Oracle 连接查询是指使用 START WITH 和 CONNECT BY 语句来实现递归查询的方法,这种方法可以生成树形结构的数据。在 Oracle 中,START WITH 语句用于指定递归查询的开始记录,而 CONNECT BY 语句用于指定递归查询的...

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

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

    oracle递归、迭代

    通过合理地定义`START WITH`和`CONNECT BY PRIOR`子句,可以方便地检索出任何复杂的层级结构数据。此外,结合`ORDER SIBLINGS BY`子句,还可以对查询结果进行排序,以满足不同场景的需求。希望本文能帮助读者更好地...

    Oracle递归查询

    在Oracle数据库中,递归查询主要依赖于`START WITH`和`CONNECT BY PRIOR`两个关键语法。本文将详细介绍这些概念以及具体的使用方法。 #### 二、创建示例表 为了更好地理解Oracle中的递归查询,我们首先需要创建...

    oracle树结构查询.DOC

    总的来说,Oracle的`START WITH...CONNECT BY`语法提供了一种强大而灵活的方式来处理树形数据,使得我们能够轻松地查询和展示具有层级关系的数据。理解和熟练掌握这些概念,对于处理复杂的组织结构或者其他层次数据...

    Oracle递归SQL学习

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

    递归查询菜单树,支持mysql,oracle

    `START WITH`定义了开始节点(顶级菜单),`CONNECT BY`指定了递归连接条件(当前行的父菜单ID等于上一行的ID),而`level`表示菜单的深度。 在实际应用中,通常还需要创建相应的表结构来存储这些菜单数据。例如,`...

    oracle中connect-by-prior用法,实战解决日期分解问题.docx

    Oracle数据库中的`CONNECT BY PRIOR`是一个强大的查询构造器,用于处理树形数据结构,尤其在组织层级、部门结构或者员工管理系统中非常常见。这个功能允许我们从一个或多个根节点开始,按照指定的规则遍历整个树结构...

    数据库设计之递归树查询

    虽然Oracle数据库不直接支持`WITH RECURSIVE`,但它提供了`CONNECT BY`关键字来处理树形结构。以下是在Oracle中进行递归查询的例子: ```sql SELECT id, name, CONNECT_BY_ROOT id AS root_id FROM employees START...

    oracle递归查询的例子

    2. **CONNECT BY PRIOR 子句**:用于定义递归关系,即如何从上一级记录推导出下一级记录。 3. **LEVEL 关键字**:用于获取当前记录的层级编号,这对于控制递归深度非常有用。 #### 三、示例表的创建与数据插入 首先...

    Oracle的Connect By使用示例

    Oracle数据库通过提供`START WITH...CONNECT BY`语句来简化这类查询操作。此功能最早出现在Oracle 8.1.6版本,并一直沿用至今,成为处理递归查询的强大工具之一。 #### 二、Connect By与Start With详解 1. **...

Global site tag (gtag.js) - Google Analytics