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

时间：2023-12-14 19:26:14

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

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

示例数据清单如下:

  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. 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

  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;
  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

  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;
  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 查询根节点

  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;
  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

  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;
  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

  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;
  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判断叶节点

  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;
  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
  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;
  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判断根节点

  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;
  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
  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;
  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
  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;
  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