oracle connect by用法

 

oracle中的select語句可以用START WITH...CONNECT BY PRIOR子句實現遞迴查詢，connect by 是結構化查詢中用到的，其基本語法是： select ... from where start with connect by ; ：過濾條件，用於對返回的所有記錄進行過濾。 ：查詢結果重起始根結點的限定條件。 ：連線條件

舉例

資料組織結構如下圖：

|----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子句遞迴查詢