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

view plaincopy to clipboardprint?

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;

[sql] view plaincopy

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

转载：

http://blog..net/zhangdaiscott/article/details/6721313