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