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
