数据构造布局以下图：

|----a-----a-a
|           |--a-b
|           |--a-c----a-c-a
|
|
|----b-----b-a
|           |--b-b    

表布局以下：

CREATE TABLE TREE
(ID       NUMBER,
ROOT_ID  NUMBER,
NAME     VARCHAR2(5 BYTE)
)

 

ID	ROOT_ID	NAME
1	0	a
2	1	a-a
3	1	a-b
4	0	b
5	4	b-a
6	4	b-b
7	1	a-c
8	7	a-c-a

 

获得无缺树(从Root往树末梢递归)：

select * from TREE
start with root_id = 0
connect by prior id = root_id;

 

ROOT_ID	ID	NAME
0	1	a
1	2	a-a
1	3	a-b
1	7	a-c
7	8	a-c-a
0	4	b
4	5	b-a
4	6	b-b

 

 

|----a-----a-a
|           |--a-b
|           |--a-c----a-c-a
|
|
|----b-----b-a
|           |--b-b    

获得特定子树：

select * from TREE
start with id = 1
connect by prior id = root_id;

 

ROOT_ID	ID	NAME
0	1	a
1	2	a-a
1	3	a-b
1	7	a-c
7	8	a-c-a

 

|----a-----a-a
|           |--a-b
|           |--a-c----a-c-a

select * from TREE
start with id = 4
connect by prior id = root_id;

 

ROOT_ID	ID	NAME
0	4	b
4	5	b-a
4	6	b-b

 

|----a-----a-a
|           |--a-b
|           |--a-c----a-c-a

假设connect by prior中的prior被省略，则查询将没有进止深层递归。

select * from TREE 
start with root_id = 0
connect by id = root_id;

 

ROOT_ID	ID	NAME
0	1	a
0	4	b

 

|----a

|----b 

select * from TREE 
start with root_id = 1
connect by id = root_id;

 

ROOT_ID	ID	NAME
1	7	a-c
1	2	a-a
1	3	a-b

 

|----a-a
|----a-b
|----a-c

从末梢往树ROOT递归

select * from TREE
start with id=8
connect by prior root_id= id

 

ID	ROOT_ID	NAME
8	7	a-c-a
7	1	a-c
1	0	a

 

a-c-a--------a-c--------a

参考：

Oracle Connect By用法>

Oracle用Start with...Connect By子句递归查询>