`

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递归查询的例子

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

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

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

    oracle ebs系统多应用节点安装手册

    "Oracle EBS 多应用节点安装手册" Oracle EBS 系统多应用节点安装手册是指在单个数据库节点和多个应用节点上安装 Oracle E-Business Suite（EBS）Release 12.1 的详细指南。本手册旨在指导用户完成 EBS 系统的安装...

    oracle层次汇总存储过程

    Oracle提供了几种处理层级数据的方法，包括自连接、递归子查询、CONNECT BY语句以及层次查询。本资料重点介绍的是使用存储过程来实现层次数据的汇总。 在Oracle中，存储过程是一种预编译的SQL和PL/SQL代码集合，...

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