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

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

view plain copy to clipboard print ?

1. DROP TABLE idb_hierarchical;  
2. create TABLE idb_hierarchical  
3. (  
4. id number,  
5. parent_id number,  
6. str varchar2(10)  
7. );  
8.   
9. insert into idb_hierarchical values(1,null,'A');  
10. insert into idb_hierarchical values(2,1,'B');  
11. insert into idb_hierarchical values(3,2,'C');  
12. insert into idb_hierarchical values(4,3,'D');  
13. insert into idb_hierarchical values(5,2,'E');  
14. insert into idb_hierarchical values(6,2,'F');  
15. insert into idb_hierarchical values(7,3,'G');  
16. insert into idb_hierarchical values(8,4,'H');  
17. insert into idb_hierarchical values(9,4,'I');  
18. insert into idb_hierarchical values(10,null,'J');  
19. insert into idb_hierarchical values(11,10,'K');  
20. insert into idb_hierarchical values(12,11,'L');  
21. insert into idb_hierarchical values(13,10,'M');  

[sql] view plain copy

1. DROP TABLE idb_hierarchical;  
2. create TABLE idb_hierarchical  
3. (  
4. id number,  
5. parent_id number,  
6. str varchar2(10)  
7. );  
8.   
9. insert into idb_hierarchical values(1,null,'A');  
10. insert into idb_hierarchical values(2,1,'B');  
11. insert into idb_hierarchical values(3,2,'C');  
12. insert into idb_hierarchical values(4,3,'D');  
13. insert into idb_hierarchical values(5,2,'E');  
14. insert into idb_hierarchical values(6,2,'F');  
15. insert into idb_hierarchical values(7,3,'G');  
16. insert into idb_hierarchical values(8,4,'H');  
17. insert into idb_hierarchical values(9,4,'I');  
18. insert into idb_hierarchical values(10,null,'J');  
19. insert into idb_hierarchical values(11,10,'K');  
20. insert into idb_hierarchical values(12,11,'L');  
21. insert into idb_hierarchical values(13,10,'M');  

示例数据清单如下:

view plain copy to clipboard print ?

1. SELECT RPAD('+',LEVEL*2+1,'.')||STR STR_LEVEL,ID,PARENT_ID,LEVEL LVL  
2.   FROM idb_hierarchical  
3.  START WITH PARENT_ID IS NULL  
4. CONNECT BY PARENT_ID = PRIOR ID;  

[sql] view plain copy

1. SELECT RPAD('+',LEVEL*2+1,'.')||STR STR_LEVEL,ID,PARENT_ID,LEVEL LVL  
2.   FROM idb_hierarchical  
3.  START WITH PARENT_ID IS NULL  
4. 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 plain copy to clipboard print ?

1. SELECT RPAD('+',LEVEL*2+1,'.')||STR STR_LEVEL,ID,PARENT_ID,LEVEL LVL  
2.   FROM idb_hierarchical I  
3.   --在oracle 9i中显示叶节点，需要判断是否有子节点即可  
4.   WHERE NOT EXISTS(SELECT 1  
5.   FROM idb_hierarchical B  
6.   WHERE I.ID=B.PARENT_ID)  
7.  START WITH PARENT_ID IS NULL  
8. CONNECT BY PARENT_ID = PRIOR ID;  

[sql] view plain copy

1. SELECT RPAD('+',LEVEL*2+1,'.')||STR STR_LEVEL,ID,PARENT_ID,LEVEL LVL  
2.   FROM idb_hierarchical I  
3.   --在oracle 9i中显示叶节点，需要判断是否有子节点即可  
4.   WHERE NOT EXISTS(SELECT 1  
5.   FROM idb_hierarchical B  
6.   WHERE I.ID=B.PARENT_ID)  
7.  START WITH PARENT_ID IS NULL  
8. 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 plain copy to clipboard print ?

1. SELECT RPAD('+',LEVEL*2+1,'.')||STR STR_LEVEL,ID,PARENT_ID,LEVEL LVL,  
2. NVL((SELECT 'N'  
3.   FROM idb_hierarchical B  
4.   WHERE I.ID=B.PARENT_ID  
5.   AND ROWNUM  < 2),'Y') IS_LEAF  
6.   FROM idb_hierarchical I  
7.  START WITH PARENT_ID IS NULL  
8. CONNECT BY PARENT_ID = PRIOR ID;  

[sql] view plain copy

1. SELECT RPAD('+',LEVEL*2+1,'.')||STR STR_LEVEL,ID,PARENT_ID,LEVEL LVL,  
2. NVL((SELECT 'N'  
3.   FROM idb_hierarchical B  
4.   WHERE I.ID=B.PARENT_ID  
5.   AND ROWNUM  < 2),'Y') IS_LEAF  
6.   FROM idb_hierarchical I  
7.  START WITH PARENT_ID IS NULL  
8. 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 plain copy to clipboard print ?

1. SELECT RPAD('+',LEVEL*2+1,'.')||STR STR_LEVEL,ID,PARENT_ID,LEVEL LVL  
2.   FROM idb_hierarchical I  
3.  START WITH id =2  
4. CONNECT BY PARENT_ID = PRIOR ID;  

[sql] view plain copy

1. SELECT RPAD('+',LEVEL*2+1,'.')||STR STR_LEVEL,ID,PARENT_ID,LEVEL LVL  
2.   FROM idb_hierarchical I  
3.  START WITH id =2  
4. 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 plain copy to clipboard print ?

1. SELECT RPAD('+', LEVEL * 2 + 1, '.') || STR STR_LEVEL,  
2.        ID,  
3.        PARENT_ID,  
4.        LEVEL LVL,  
5.        (select b.str  
6.           from idb_hierarchical b  
7.          where level = 1  
8.          start with b.id = 2  
9.         connect by prior b.id =  b.parent_id  
10.         ) root_str  
11.   FROM idb_hierarchical I  
12.  where level = 1  
13.  START WITH id = 2  
14. CONNECT BY PARENT_ID = PRIOR ID;  

[sql] view plain copy

1. SELECT RPAD('+', LEVEL * 2 + 1, '.') || STR STR_LEVEL,  
2.        ID,  
3.        PARENT_ID,  
4.        LEVEL LVL,  
5.        (select b.str  
6.           from idb_hierarchical b  
7.          where level = 1  
8.          start with b.id = 2  
9.         connect by prior b.id =  b.parent_id  
10.         ) root_str  
11.   FROM idb_hierarchical I  
12.  where level = 1  
13.  START WITH id = 2  
14. CONNECT BY PARENT_ID = PRIOR ID;  

表5

STR_LEVEL	ID	PARENT_ID	LVL	ROOT_STR
+..B	2	1	1	B

标明根节点SQL

view plain copy to clipboard print ?

1. SELECT RPAD('+', LEVEL * 2 + 1, '.') || STR STR_LEVEL,  
2.        ID,  
3.        PARENT_ID,  
4.        DECODE(LEVEL, 1, 'Y', 'N') is_root,  
5.        LEVEL LVL,  
6.        (select b.str  
7.           from idb_hierarchical b  
8.          where level = 1  
9.          start with b.id = 2  
10.         connect by prior b.id = b.parent_id) root_str  
11.   FROM idb_hierarchical I  
12.  START WITH id = 2  
13. CONNECT BY PARENT_ID = PRIOR ID;  

[sql] view plain copy

1. SELECT RPAD('+', LEVEL * 2 + 1, '.') || STR STR_LEVEL,  
2.        ID,  
3.        PARENT_ID,  
4.        DECODE(LEVEL, 1, 'Y', 'N') is_root,  
5.        LEVEL LVL,  
6.        (select b.str  
7.           from idb_hierarchical b  
8.          where level = 1  
9.          start with b.id = 2  
10.         connect by prior b.id = b.parent_id) root_str  
11.   FROM idb_hierarchical I  
12.  START WITH id = 2  
13. 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 plain copy to clipboard print ?

1. SELECT RPAD('+',LEVEL*2+1,'.')||STR STR_LEVEL,ID,PARENT_ID,LEVEL LVL  
2.   FROM idb_hierarchical I  
3. where connect_by_isleaf=1  
4.  START WITH PARENT_ID IS NULL  
5. CONNECT BY PARENT_ID = PRIOR ID;  

[sql] view plain copy

1. SELECT RPAD('+',LEVEL*2+1,'.')||STR STR_LEVEL,ID,PARENT_ID,LEVEL LVL  
2.   FROM idb_hierarchical I  
3. where connect_by_isleaf=1  
4.  START WITH PARENT_ID IS NULL  
5. 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 plain copy to clipboard print ?

1. SELECT RPAD('+',LEVEL*2+1,'.')||STR STR_LEVEL,ID,PARENT_ID,LEVEL LVL,  
2. decode(connect_by_isleaf,1,'Y','N') IS_LEAF  
3.   FROM idb_hierarchical I  
4.  START WITH PARENT_ID IS NULL  
5. CONNECT BY PARENT_ID = PRIOR ID;  

[sql] view plain copy

1. SELECT RPAD('+',LEVEL*2+1,'.')||STR STR_LEVEL,ID,PARENT_ID,LEVEL LVL,  
2. decode(connect_by_isleaf,1,'Y','N') IS_LEAF  
3.   FROM idb_hierarchical I  
4.  START WITH PARENT_ID IS NULL  
5. 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 plain copy to clipboard print ?

1. SELECT RPAD('+', LEVEL * 2 + 1, '.') || STR STR_LEVEL,  
2.        ID,  
3.        PARENT_ID,  
4.        LEVEL LVL,  
5.        connect_by_root STR ROOT_STR  
6.   FROM idb_hierarchical I  
7.  START WITH id = 2  
8. CONNECT BY PARENT_ID = PRIOR ID;  

[sql] view plain copy

1. SELECT RPAD('+', LEVEL * 2 + 1, '.') || STR STR_LEVEL,  
2.        ID,  
3.        PARENT_ID,  
4.        LEVEL LVL,  
5.        connect_by_root STR ROOT_STR  
6.   FROM idb_hierarchical I  
7.  START WITH id = 2  
8. 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 plain copy to clipboard print ?

1. SELECT RPAD('+', LEVEL * 2 + 1, '.') || STR STR_LEVEL,  
2.        ID,  
3.        PARENT_ID,  
4.        DECODE(LEVEL, 1, 'Y', 'N') is_root,  
5.        LEVEL LVL,  
6.        connect_by_root STR ROOT_STR  
7.   FROM idb_hierarchical I  
8.  START WITH id = 3  
9. CONNECT BY PARENT_ID = PRIOR ID;  

[sql] view plain copy

1. SELECT RPAD('+', LEVEL * 2 + 1, '.') || STR STR_LEVEL,  
2.        ID,  
3.        PARENT_ID,  
4.        DECODE(LEVEL, 1, 'Y', 'N') is_root,  
5.        LEVEL LVL,  
6.        connect_by_root STR ROOT_STR  
7.   FROM idb_hierarchical I  
8.  START WITH id = 3  
9. 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 plain copy to clipboard print ?

1. SELECT RPAD('+', LEVEL * 2 + 1, '.') || STR STR_LEVEL,  
2.        ID,  
3.        PARENT_ID,  
4.        DECODE(LEVEL, 1, 'Y', 'N') is_root,  
5.        LEVEL LVL,  
6.        connect_by_root STR ROOT_STR  
7.   FROM idb_hierarchical I  
8.  START WITH PARENT_ID = 2  
9. CONNECT BY PARENT_ID = PRIOR ID;  

[sql] view plain copy

1. SELECT RPAD('+', LEVEL * 2 + 1, '.') || STR STR_LEVEL,  
2.        ID,  
3.        PARENT_ID,  
4.        DECODE(LEVEL, 1, 'Y', 'N') is_root,  
5.        LEVEL LVL,  
6.        connect_by_root STR ROOT_STR  
7.   FROM idb_hierarchical I  
8.  START WITH PARENT_ID = 2  
9. 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