oracle层次查询，oracle 9i利用SQL演示脚本判断是叶子或根节点

17-10-18 来源：[db:作者]

收藏我要投稿

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

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');

示例数据清单如下:

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;  

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
  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;  

  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

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;

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 查询根节点

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;  

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

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;  

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

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;  

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判断叶节点 #
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;  

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

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;  

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判断根节点

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;  

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

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;

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

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;

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

点击复制链接与好友分享!回本站首页