oracle 9i判断是叶子或根节点，是比较麻烦的一件事情，SQL演示脚本如下:
view plaincopy to clipboardprint?
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');
示例数据清单如下:
view plaincopy to clipboardprint?
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
view plaincopy to clipboardprint?
SELECTRPAD('+',LEVEL*2+1,'.')||STRSTR_LEVEL,ID,PARENT_ID,LEVELLVL
FROMidb_hierarchicalI
--在oracle9i中显示叶节点，需要判断是否有子节点即可
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
view plaincopy to clipboardprint?
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 查询根节点
view plaincopy to clipboardprint?
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
view plaincopy to clipboardprint?
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
view plaincopy to clipboardprint?
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判断叶节点
view plaincopy to clipboardprint?
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
view plaincopy to clipboardprint?
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判断根节点
view plaincopy to clipboardprint?
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
view plaincopy to clipboardprint?
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
view plaincopy to clipboardprint?
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
