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

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