oracle 9i判断是叶子或根节点,是比较麻烦的一件事情,SQL演示脚本如下:
- DROPTABLEidb_hierarchical;
- createTABLEidb_hierarchical
- (
- idnumber,
- parent_idnumber,
- strvarchar2(10)
- );
- insertintoidb_hierarchicalvalues(1,null,'A');
- insertintoidb_hierarchicalvalues(2,1,'B');
- insertintoidb_hierarchicalvalues(3,2,'C');
- insertintoidb_hierarchicalvalues(4,3,'D');
- insertintoidb_hierarchicalvalues(5,2,'E');
- insertintoidb_hierarchicalvalues(6,2,'F');
- insertintoidb_hierarchicalvalues(7,3,'G');
- insertintoidb_hierarchicalvalues(8,4,'H');
- insertintoidb_hierarchicalvalues(9,4,'I');
- insertintoidb_hierarchicalvalues(10,null,'J');
- insertintoidb_hierarchicalvalues(11,10,'K');
- insertintoidb_hierarchicalvalues(12,11,'L');
- insertintoidb_hierarchicalvalues(13,10,'M');
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');
示例数据清单如下:
- SELECTRPAD('+',LEVEL*2+1,'.')||STRSTR_LEVEL,ID,PARENT_ID,LEVELLVL
- FROMidb_hierarchical
- STARTWITHPARENT_IDISNULL
- CONNECTBYPARENT_ID=PRIORID;
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
- SELECTRPAD('+',LEVEL*2+1,'.')||STRSTR_LEVEL,ID,PARENT_ID,LEVELLVL
- FROMidb_hierarchicalI
- WHERENOTEXISTS(SELECT1
- FROMidb_hierarchicalB
- WHEREI.ID=B.PARENT_ID)
- STARTWITHPARENT_IDISNULL
- CONNECTBYPARENT_ID=PRIORID;
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
- SELECTRPAD('+',LEVEL*2+1,'.')||STRSTR_LEVEL,ID,PARENT_ID,LEVELLVL,
- NVL((SELECT'N'
- FROMidb_hierarchicalB
- WHEREI.ID=B.PARENT_ID
- ANDROWNUM<2),'Y')IS_LEAF
- FROMidb_hierarchicalI
- STARTWITHPARENT_IDISNULL
- CONNECTBYPARENT_ID=PRIORID;
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 查询根节点
- SELECTRPAD('+',LEVEL*2+1,'.')||STRSTR_LEVEL,ID,PARENT_ID,LEVELLVL
- FROMidb_hierarchicalI
- STARTWITHid=2
- CONNECTBYPARENT_ID=PRIORID;
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
- SELECTRPAD('+',LEVEL*2+1,'.')||STRSTR_LEVEL,
- ID,
- PARENT_ID,
- LEVELLVL,
- (selectb.str
- fromidb_hierarchicalb
- wherelevel=1
- startwithb.id=2
- connectbypriorb.id=b.parent_id
- )root_str
- FROMidb_hierarchicalI
- wherelevel=1
- STARTWITHid=2
- CONNECTBYPARENT_ID=PRIORID;
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
- SELECTRPAD('+',LEVEL*2+1,'.')||STRSTR_LEVEL,
- ID,
- PARENT_ID,
- DECODE(LEVEL,1,'Y','N')is_root,
- LEVELLVL,
- (selectb.str
- fromidb_hierarchicalb
- wherelevel=1
- startwithb.id=2
- connectbypriorb.id=b.parent_id)root_str
- FROMidb_hierarchicalI
- STARTWITHid=2
- CONNECTBYPARENT_ID=PRIORID;
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判断叶节点
- SELECTRPAD('+',LEVEL*2+1,'.')||STRSTR_LEVEL,ID,PARENT_ID,LEVELLVL
- FROMidb_hierarchicalI
- whereconnect_by_isleaf=1
- STARTWITHPARENT_IDISNULL
- CONNECTBYPARENT_ID=PRIORID;
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 |
- SELECTRPAD('+',LEVEL*2+1,'.')||STRSTR_LEVEL,ID,PARENT_ID,LEVELLVL,
- decode(connect_by_isleaf,1,'Y','N')IS_LEAF
- FROMidb_hierarchicalI
- STARTWITHPARENT_IDISNULL
- CONNECTBYPARENT_ID=PRIORID;
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判断根节点
- SELECTRPAD('+',LEVEL*2+1,'.')||STRSTR_LEVEL,
- ID,
- PARENT_ID,
- LEVELLVL,
- connect_by_rootSTRROOT_STR
- FROMidb_hierarchicalI
- STARTWITHid=2
- CONNECTBYPARENT_ID=PRIORID;
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 |
- SELECTRPAD('+',LEVEL*2+1,'.')||STRSTR_LEVEL,
- ID,
- PARENT_ID,
- DECODE(LEVEL,1,'Y','N')is_root,
- LEVELLVL,
- connect_by_rootSTRROOT_STR
- FROMidb_hierarchicalI
- STARTWITHid=3
- CONNECTBYPARENT_ID=PRIORID;
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 |
- SELECTRPAD('+',LEVEL*2+1,'.')||STRSTR_LEVEL,
- ID,
- PARENT_ID,
- DECODE(LEVEL,1,'Y','N')is_root,
- LEVELLVL,
- connect_by_rootSTRROOT_STR
- FROMidb_hierarchicalI
- STARTWITHPARENT_ID=2
- CONNECTBYPARENT_ID=PRIORID;
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 |