[转]Oracle查询树形数据的叶节点和子节点

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'); [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; 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

 

转载于:https://www.cnblogs.com/siyunianhua/p/3454431.html

相关资源：【原创】oracle树形结构查询，层次查询，hierarical retrival