`

oracle 层次查询判断叶子和根节点

oracle 9i判断是叶子或根节点递归 
阅读更多

oracle 9i判断是叶子或根节点，是比较麻烦的一件事情，SQL演示脚本如下:
view plaincopy to clipboardprint?

    DROP TABLE idb_hierarchical; 
    create TABLE idb_hierarchical 
    ( 
    id number, 
    parent_id number, 
    str varchar2(10) 
    ); 
     
    insert into idb_hierarchical values(1,null,'A'); 
    insert into idb_hierarchical values(2,1,'B'); 
    insert into idb_hierarchical values(3,2,'C'); 
    insert into idb_hierarchical values(4,3,'D'); 
    insert into idb_hierarchical values(5,2,'E'); 
    insert into idb_hierarchical values(6,2,'F'); 
    insert into idb_hierarchical values(7,3,'G'); 
    insert into idb_hierarchical values(8,4,'H'); 
    insert into idb_hierarchical values(9,4,'I'); 
    insert into idb_hierarchical values(10,null,'J'); 
    insert into idb_hierarchical values(11,10,'K'); 
    insert into idb_hierarchical values(12,11,'L'); 
    insert into idb_hierarchical values(13,10,'M'); 

[sql] view plaincopy

    DROP TABLE idb_hierarchical; 
    create TABLE idb_hierarchical 
    ( 
    id number, 
    parent_id number, 
    str varchar2(10) 
    ); 
     
    insert into idb_hierarchical values(1,null,'A'); 
    insert into idb_hierarchical values(2,1,'B'); 
    insert into idb_hierarchical values(3,2,'C'); 
    insert into idb_hierarchical values(4,3,'D'); 
    insert into idb_hierarchical values(5,2,'E'); 
    insert into idb_hierarchical values(6,2,'F'); 
    insert into idb_hierarchical values(7,3,'G'); 
    insert into idb_hierarchical values(8,4,'H'); 
    insert into idb_hierarchical values(9,4,'I'); 
    insert into idb_hierarchical values(10,null,'J'); 
    insert into idb_hierarchical values(11,10,'K'); 
    insert into idb_hierarchical values(12,11,'L'); 
    insert into idb_hierarchical values(13,10,'M'); 

示例数据清单如下:
view plaincopy to clipboardprint?

    SELECT RPAD('+',LEVEL*2+1,'.')||STR STR_LEVEL,ID,PARENT_ID,LEVEL LVL 
      FROM idb_hierarchical 
     START WITH PARENT_ID IS NULL 
    CONNECT BY PARENT_ID = PRIOR ID; 

[sql] view plaincopy

    SELECT RPAD('+',LEVEL*2+1,'.')||STR STR_LEVEL,ID,PARENT_ID,LEVEL LVL 
      FROM idb_hierarchical 
     START WITH PARENT_ID IS NULL 
    CONNECT BY PARENT_ID = PRIOR ID; 

表1:数据清单 STR_LEVEL     ID     PARENT_ID     LVL
+..A     1           1
+….B     2     1     2
+……C     3     2     3
+……..D     4     3     4
+……….H     8     4     5
+……….I     9     4     5
+……..G     7     3     4
+……E     5     2     3
+……F     6     2     3
+..J     10           1
+….K     11     10     2
+……L     12     11     3
+….M     13     10     2

在表1中，ID为8、9、 7、5、6、12、13都没有子节点，因此称为叶节点。
1.oracle9i 查询叶节点

只显示叶子节点SQL
view plaincopy to clipboardprint?

    SELECT RPAD('+',LEVEL*2+1,'.')||STR STR_LEVEL,ID,PARENT_ID,LEVEL LVL 
      FROM idb_hierarchical I 
      --在oracle 9i中显示叶节点，需要判断是否有子节点即可 
      WHERE NOT EXISTS(SELECT 1 
      FROM idb_hierarchical B 
      WHERE I.ID=B.PARENT_ID) 
     START WITH PARENT_ID IS NULL 
    CONNECT BY PARENT_ID = PRIOR ID; 

[sql] view plaincopy

    SELECT RPAD('+',LEVEL*2+1,'.')||STR STR_LEVEL,ID,PARENT_ID,LEVEL LVL 
      FROM idb_hierarchical I 
      --在oracle 9i中显示叶节点，需要判断是否有子节点即可 
      WHERE NOT EXISTS(SELECT 1 
      FROM idb_hierarchical B 
      WHERE I.ID=B.PARENT_ID) 
     START WITH PARENT_ID IS NULL 
    CONNECT BY PARENT_ID = PRIOR ID; 

表2 STR_LEVEL     ID     PARENT_ID     LVL
+……….H     8     4     5
+……….I     9     4     5
+……..G     7     3     4
+……E     5     2     3
+……F     6     2     3
+……L     12     11     3
+….M     13     10     2

显示所有节点，标明该行是否为叶节点SQL
view plaincopy to clipboardprint?

    SELECT RPAD('+',LEVEL*2+1,'.')||STR STR_LEVEL,ID,PARENT_ID,LEVEL LVL, 
    NVL((SELECT 'N' 
      FROM idb_hierarchical B 
      WHERE I.ID=B.PARENT_ID 
      AND ROWNUM  < 2),'Y') IS_LEAF 
      FROM idb_hierarchical I 
     START WITH PARENT_ID IS NULL 
    CONNECT BY PARENT_ID = PRIOR ID; 

[sql] view plaincopy

    SELECT RPAD('+',LEVEL*2+1,'.')||STR STR_LEVEL,ID,PARENT_ID,LEVEL LVL, 
    NVL((SELECT 'N' 
      FROM idb_hierarchical B 
      WHERE I.ID=B.PARENT_ID 
      AND ROWNUM  < 2),'Y') IS_LEAF 
      FROM idb_hierarchical I 
     START WITH PARENT_ID IS NULL 
    CONNECT BY PARENT_ID = PRIOR ID; 

表3 STR_LEVEL     ID     PARENT_ID     LVL     IS_LEAF
+..A     1           1     N
+....B     2     1     2     N
+......C     3     2     3     N
+........D     4     3     4     N
+..........H     8     4     5     Y
+..........I     9     4     5     Y
+........G     7     3     4     Y
+......E     5     2     3     Y
+......F     6     2     3     Y
+..J     10           1     N
+....K     11     10     2     N
+......L     12     11     3     Y
+....M     13     10     2     Y
oracle 9i 查询根节点
view plaincopy to clipboardprint?

    SELECT RPAD('+',LEVEL*2+1,'.')||STR STR_LEVEL,ID,PARENT_ID,LEVEL LVL 
      FROM idb_hierarchical I 
     START WITH id =2 
    CONNECT BY PARENT_ID = PRIOR ID; 

[sql] view plaincopy

    SELECT RPAD('+',LEVEL*2+1,'.')||STR STR_LEVEL,ID,PARENT_ID,LEVEL LVL 
      FROM idb_hierarchical I 
     START WITH id =2 
    CONNECT BY PARENT_ID = PRIOR ID; 

表4 STR_LEVEL     ID     PARENT_ID     LVL
+..B     2     1     1
+....C     3     2     2
+......D     4     3     3
+........H     8     4     4
+........I     9     4     4
+......G     7     3     3
+....E     5     2     2
+....F     6     2     2

根节点ID应该为3、5、6，即lvl为1即可

查询根节点，只显示根节点SQL
view plaincopy to clipboardprint?

    SELECT RPAD('+', LEVEL * 2 + 1, '.') || STR STR_LEVEL, 
           ID, 
           PARENT_ID, 
           LEVEL LVL, 
           (select b.str 
              from idb_hierarchical b 
             where level = 1 
             start with b.id = 2 
            connect by prior b.id =  b.parent_id 
            ) root_str 
      FROM idb_hierarchical I 
     where level = 1 
     START WITH id = 2 
    CONNECT BY PARENT_ID = PRIOR ID; 

[sql] view plaincopy

    SELECT RPAD('+', LEVEL * 2 + 1, '.') || STR STR_LEVEL, 
           ID, 
           PARENT_ID, 
           LEVEL LVL, 
           (select b.str 
              from idb_hierarchical b 
             where level = 1 
             start with b.id = 2 
            connect by prior b.id =  b.parent_id 
            ) root_str 
      FROM idb_hierarchical I 
     where level = 1 
     START WITH id = 2 
    CONNECT BY PARENT_ID = PRIOR ID; 

表5 STR_LEVEL     ID     PARENT_ID     LVL     ROOT_STR
+..B     2     1     1     B

标明根节点SQL
view plaincopy to clipboardprint?

    SELECT RPAD('+', LEVEL * 2 + 1, '.') || STR STR_LEVEL, 
           ID, 
           PARENT_ID, 
           DECODE(LEVEL, 1, 'Y', 'N') is_root, 
           LEVEL LVL, 
           (select b.str 
              from idb_hierarchical b 
             where level = 1 
             start with b.id = 2 
            connect by prior b.id = b.parent_id) root_str 
      FROM idb_hierarchical I 
     START WITH id = 2 
    CONNECT BY PARENT_ID = PRIOR ID; 

[sql] view plaincopy

    SELECT RPAD('+', LEVEL * 2 + 1, '.') || STR STR_LEVEL, 
           ID, 
           PARENT_ID, 
           DECODE(LEVEL, 1, 'Y', 'N') is_root, 
           LEVEL LVL, 
           (select b.str 
              from idb_hierarchical b 
             where level = 1 
             start with b.id = 2 
            connect by prior b.id = b.parent_id) root_str 
      FROM idb_hierarchical I 
     START WITH id = 2 
    CONNECT BY PARENT_ID = PRIOR ID; 

表6 STR_LEVEL     ID     PARENT_ID     IS_ROOT     LVL     ROOT_STR
+..B     2     1     Y     1     B
+....C     3     2     N     2     B
+......D     4     3     N     3     B
+........H     8     4     N     4     B
+........I     9     4     N     4     B
+......G     7     3     N     3     B
+....E     5     2     N     2     B
+....F     6     2     N     2     B

在oracle 10g提供了connect_by_isleaf和connect_by_root
oracle 10g用connect_by_isleaf判断叶节点
view plaincopy to clipboardprint?

    SELECT RPAD('+',LEVEL*2+1,'.')||STR STR_LEVEL,ID,PARENT_ID,LEVEL LVL 
      FROM idb_hierarchical I 
    where connect_by_isleaf=1 
     START WITH PARENT_ID IS NULL 
    CONNECT BY PARENT_ID = PRIOR ID; 

[sql] view plaincopy

    SELECT RPAD('+',LEVEL*2+1,'.')||STR STR_LEVEL,ID,PARENT_ID,LEVEL LVL 
      FROM idb_hierarchical I 
    where connect_by_isleaf=1 
     START WITH PARENT_ID IS NULL 
    CONNECT BY PARENT_ID = PRIOR ID; 

表7 STR_LEVEL     ID     PARENT_ID     LVL
+..........H     8     4     5
+..........I     9     4     5
+........G     7     3     4
+......E     5     2     3
+......F     6     2     3
+......L     12     11     3
+....M     13     10     2
view plaincopy to clipboardprint?

    SELECT RPAD('+',LEVEL*2+1,'.')||STR STR_LEVEL,ID,PARENT_ID,LEVEL LVL, 
    decode(connect_by_isleaf,1,'Y','N') IS_LEAF 
      FROM idb_hierarchical I 
     START WITH PARENT_ID IS NULL 
    CONNECT BY PARENT_ID = PRIOR ID; 

[sql] view plaincopy

    SELECT RPAD('+',LEVEL*2+1,'.')||STR STR_LEVEL,ID,PARENT_ID,LEVEL LVL, 
    decode(connect_by_isleaf,1,'Y','N') IS_LEAF 
      FROM idb_hierarchical I 
     START WITH PARENT_ID IS NULL 
    CONNECT BY PARENT_ID = PRIOR ID; 

表8 STR_LEVEL     ID     PARENT_ID     LVL     IS_LEAF
+..A     1           1     N
+....B     2     1     2     N
+......C     3     2     3     N
+........D     4     3     4     N
+..........H     8     4     5     Y
+..........I     9     4     5     Y
+........G     7     3     4     Y
+......E     5     2     3     Y
+......F     6     2     3     Y
+..J     10           1     N
+....K     11     10     2     N
+......L     12     11     3     Y
+....M     13     10     2     Y
oracle 10g用connect_by_root判断根节点
view plaincopy to clipboardprint?

    SELECT RPAD('+', LEVEL * 2 + 1, '.') || STR STR_LEVEL, 
           ID, 
           PARENT_ID, 
           LEVEL LVL, 
           connect_by_root STR ROOT_STR 
      FROM idb_hierarchical I 
     START WITH id = 2 
    CONNECT BY PARENT_ID = PRIOR ID; 

[sql] view plaincopy

    SELECT RPAD('+', LEVEL * 2 + 1, '.') || STR STR_LEVEL, 
           ID, 
           PARENT_ID, 
           LEVEL LVL, 
           connect_by_root STR ROOT_STR 
      FROM idb_hierarchical I 
     START WITH id = 2 
    CONNECT BY PARENT_ID = PRIOR ID; 

表9 STR_LEVEL     ID     PARENT_ID     LVL     ROOT_STR
+..B     2     1     1     B
+....C     3     2     2     B
+......D     4     3     3     B
+........H     8     4     4     B
+........I     9     4     4     B
+......G     7     3     3     B
+....E     5     2     2     B
+....F     6     2     2     B
view plaincopy to clipboardprint?

    SELECT RPAD('+', LEVEL * 2 + 1, '.') || STR STR_LEVEL, 
           ID, 
           PARENT_ID, 
           DECODE(LEVEL, 1, 'Y', 'N') is_root, 
           LEVEL LVL, 
           connect_by_root STR ROOT_STR 
      FROM idb_hierarchical I 
     START WITH id = 3 
    CONNECT BY PARENT_ID = PRIOR ID; 

[sql] view plaincopy

    SELECT RPAD('+', LEVEL * 2 + 1, '.') || STR STR_LEVEL, 
           ID, 
           PARENT_ID, 
           DECODE(LEVEL, 1, 'Y', 'N') is_root, 
           LEVEL LVL, 
           connect_by_root STR ROOT_STR 
      FROM idb_hierarchical I 
     START WITH id = 3 
    CONNECT BY PARENT_ID = PRIOR ID; 

表10 STR_LEVEL     ID     PARENT_ID     IS_ROOT     LVL     ROOT_STR
+..C     3     2     Y     1     C
+....D     4     3     N     2     C
+......H     8     4     N     3     C
+......I     9     4     N     3     C
+....G     7     3     N     2     C
view plaincopy to clipboardprint?

    SELECT RPAD('+', LEVEL * 2 + 1, '.') || STR STR_LEVEL, 
           ID, 
           PARENT_ID, 
           DECODE(LEVEL, 1, 'Y', 'N') is_root, 
           LEVEL LVL, 
           connect_by_root STR ROOT_STR 
      FROM idb_hierarchical I 
     START WITH PARENT_ID = 2 
    CONNECT BY PARENT_ID = PRIOR ID; 

[sql] view plaincopy

    SELECT RPAD('+', LEVEL * 2 + 1, '.') || STR STR_LEVEL, 
           ID, 
           PARENT_ID, 
           DECODE(LEVEL, 1, 'Y', 'N') is_root, 
           LEVEL LVL, 
           connect_by_root STR ROOT_STR 
      FROM idb_hierarchical I 
     START WITH PARENT_ID = 2 
    CONNECT BY PARENT_ID = PRIOR ID; 

表11 STR_LEVEL     ID     PARENT_ID     IS_ROOT     LVL     ROOT_STR
+..C     3     2     Y     1     C
+....D     4     3     N     2     C
+......H     8     4     N     3     C
+......I     9     4     N     3     C
+....G     7     3     N     2     C
+..E     5     2     Y     1     E
+..F     6     2     Y     1     F

 

 

转载：

http://blog.csdn.net/zhangdaiscott/article/details/6721313

分享到：
| C3P0，Proxool，BoneCP，Druid等连接池的 ...
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    Oracle层次查询功能的剖析.pdf

    Oracle层次查询功能是Oracle数据库系统中的一个重要特性，它在关系型数据库管理中为处理具有层级关系的数据提供了便利。本文主要探讨了如何利用Oracle的层次查询功能处理和展示类似树形结构的数据。 首先，层次结构...

    Oracle实战层次查询

    层次查询的概念 层次查询的语法 基本层次查询 层次查询的增强特性 CONNECT BY特殊用法示例

    oracle层次查询

    ### Oracle层次查询详解 在Oracle数据库中，处理具有层次结构的数据是一项常见的需求，尤其是在企业管理、财务分析、组织架构展示等领域。Oracle提供了强大的`CONNECT BY`子句，它能够有效地进行层次查询，帮助用户...

    Oracle中层次查询的使用和探讨.pdf

    Oracle数据库中的层次查询（Hierarchical Queries）是一种强大的功能，它扩展了标准SQL语言，使得能够高效地处理具有层级关系的数据。这种查询方式尤其适用于处理树形结构的数据，例如组织架构、文件目录等。 首先...

    Oracle递归树形结构查询功能

    - `START WITH`：指定查询的起始节点，即根节点的条件。可以设置多个条件来获取多个根节点，形成多棵树。 - `CONNECT BY`：定义了父子节点之间的关系。`PRIOR`关键字用来引用当前行的上一行，即父节点。例如，`...

    Oracle 实战SQL层次查询

    ### Oracle 实战SQL层次查询详解 #### 一、层次查询概念 层次查询，也被称为树型结构查询，是在SQL中最常见的功能之一。这种查询方式主要用于处理具有层级结构的数据，例如组织架构、产品分类等场景。在Oracle...

    在Oracle层次查询中给SIBLINGS排序

    Oracle SELECT语句中的START WITH和CONNECT BY子句自动阻断一个层次。缺少这项特性，就需要一个复杂的自联接来确定行之间的逻辑联系。START WITH子句指定被...本文将为大家介绍如何在Oracle层次查询中给SIBLINGS排序。

    【原创】oracle树形结构查询，层次查询，hierarical retrival

    Oracle树形结构查询，层次查询，hierarchical retrieval Oracle中的树形结构查询，也被称为层次查询或hierarchical retrieval，是一种获取树形结构数据的方法。这种方法可以将数据组织成树形结构，具有层次关系的...

    【Oracle】树状结构查询

    在Oracle数据库中，进行树状结构查询是一种常见且强大的功能，尤其当数据具有层次或层级关系时，如组织架构、产品分类、文件系统等。本文将深入探讨如何在Oracle中执行树状结构查询，理解其背后的逻辑，以及如何利用...

    Oracle通过递归查询父子兄弟节点方法示例

    在Oracle数据库中，递归查询是一种强大的工具，用于处理层级数据结构，如组织结构、文件系统或树形关系。在本篇文章中，我们将探讨如何利用递归查询来查找父子兄弟节点，这对于理解和处理这类关系非常关键。 首先，...

    层次查询功能在Oracle数据库中的应用.pdf

    Oracle数据库是一种广泛使用的商业关系型数据库管理系统，其层次查询功能主要基于树形结构来处理层级数据，这种结构被广泛用于表示具有层级关系的数据，如人事组织结构、公司组织架构以及计算机目录结构等。层次查询...

    基于Oracle的层次树查询功能及实例分析.pdf

    《基于Oracle的层次树查询功能及实例分析》 在关系型数据库中，处理具有层次结构的数据通常需要复杂的迭代编程，而Oracle的层次树查询功能提供了一种高效且简洁的解决方案。本文将深入探讨Oracle的层次树查询技术，...

    oraclesql判断值为空-Oracle-sqlserver的空值（null）判断.pdf

    Oracle SQL 判断值为空OrNull 判断 Oracle SQL 中判断值为空或 Null 的方法有多种，在本文中，我们将介绍 Oracle 和 SQL Server 中的空值判断方法。 Oracle 中的空值判断 在 Oracle 中，可以使用 `NVL` 函数来...

    Oracle树查询总结

    `CONNECT_BY_ROOT` 函数用于标识根节点，结合 `START WITH parent_id=0` 和 `CONNECT BY` 语句，找到从根节点到指定节点的路径。 9. **列出当前节点是否为叶子** `CONNECT_BY_ISLEAF` 表达式返回一个布尔值，表示...

    oracle树查询

    无论是查询根节点还是特定节点的子节点，或者是查询特定节点的父节点，甚至是查询特定层级的所有节点，都可以通过简单的SQL语句来实现。这些查询方式不仅能够提高数据处理的效率，还能够简化复杂数据关系的分析过程...

    oracle判断日期是否合法

    oracle判断日期是否合法

    oracle 自定义方法 判断日期是不是为假期 如果是假期则取下一天 直到工作日爲止

    oracle 自定义方法 判断日期是不是为假期 如果是假期则取下一天 直到工作日爲止

    oracle递归查询的例子

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

    oracle解析XMLTYPE字段里面节点值，并用PL/SQL将查询结果导出为excel

    oracle解析XMLTYPE字段里面节点值，并用PL/SQL将查询结果导出为excel

    数据库开发 Oracle数据库 SQL开发教程 第10章 层次查询（共17页）.pdf

    在Oracle数据库中，SQL开发是核心技能之一，尤其在处理复杂的数据关系时，层次查询（Hierarchical Queries）显得尤为重要。本教程的第10章专门介绍了这一主题，旨在帮助学员理解并掌握如何在数据中构建树型结构的...

Magicbox
Global site tag (gtag.js) - Google Analytics