oracle 判断 叶子,oracle 层次查询判断叶子和根节点

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_LEVELIDPARENT_IDLVL

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

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  

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_LEVELIDPARENT_IDLVLROOT_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_LEVELIDPARENT_IDIS_ROOTLVLROOT_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_LEVELIDPARENT_IDIS_ROOTLVLROOT_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