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

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