（遞歸查詢）關于oracle樹結構查詢展示分組，查找父節點，查找子節點問題

Oracle實在太強了，本篇文章詳細介紹了 Oracle 的遞歸查詢文法，利用此文法，可以友善地實作遞歸的雙向查詢：

-- Tirle : Recursion query for TREE with "connect by/start with"

-- Author : Rake Gao

-- Create Date : 2005-08-22

-- Version : 2.0

-- Last Modify : 2005-08-22

　　一、測試準備

　　二、實作各種查詢要求

　　三、要點總結

　　正文

　　一、測試準備

1、先假設有如下部門結構。

/ \

2 3

/\ /|\

4 5 6 7 8

2、然後建立測試表和資料。

drop table t_dept_temp;

create table t_dept_temp(

DEPT_ID NUMBER(2) NOT NULL,

PARENT_ID NUMBER(2) ,

DEPT_NAME VARCHAR2(10) ,

AMOUNT NUMBER(3) --人數

);

delete t_dept_temp;

insert into t_dept_temp (DEPT_ID,PARENT_ID,DEPT_NAME,AMOUNT) values (1,null,'1' ,2);

insert into t_dept_temp (DEPT_ID,PARENT_ID,DEPT_NAME,AMOUNT) values (2,1 ,'1-2' ,15);

insert into t_dept_temp (DEPT_ID,PARENT_ID,DEPT_NAME,AMOUNT) values (3,1 ,'1-3' ,8);

insert into t_dept_temp (DEPT_ID,PARENT_ID,DEPT_NAME,AMOUNT) values (4,2 ,'1-2-4',10);

insert into t_dept_temp (DEPT_ID,PARENT_ID,DEPT_NAME,AMOUNT) values (5,2 ,'1-2-5',9);

insert into t_dept_temp (DEPT_ID,PARENT_ID,DEPT_NAME,AMOUNT) values (6,3 ,'1-3-6',17);

insert into t_dept_temp (DEPT_ID,PARENT_ID,DEPT_NAME,AMOUNT) values (7,3 ,'1-3-7',5);

insert into t_dept_temp (DEPT_ID,PARENT_ID,DEPT_NAME,AMOUNT) values (8,3 ,'1-3-8',6);

commit;

SQL> select * from t_dept_temp;

DEPT_ID PARENT_ID DEPT_NAME AMOUNT

------- --------- ---------- ------

1 1 2

2 1 1-2 15

3 1 1-3 8

4 2 1-2-4 10

5 2 1-2-5 9

6 3 1-3-6 17

7 3 1-3-7 5

8 3 1-3-8 6

3、調整一下輸出格式

col DEPT_ID format A10;

　　二、接下來實作各種查詢要求

1、部門2及其所有下級部門。

SELECT LPAD(' ',2*(LEVEL - 1), ' ')||DEPT_ID AS DEPT_ID,

PARENT_ID,DEPT_NAME,AMOUNT

FROM t_dept_temp

CONNECT BY PARENT_ID = PRIOR DEPT_ID --找出所有PARENT_ID等于目前記錄DEPT_ID的記錄。

START WITH DEPT_ID = 2 --從部門2開始遞歸查詢。

;

DEPT_ID PARENT_ID DEPT_NAME AMOUNT

---------- --------- ---------- ------

2 1 1-2 15

4 2 1-2-4 10

5 2 1-2-5 9

2、部門4及其所有上級部門

SELECT LPAD(' ',2*(LEVEL - 1), ' ')||DEPT_ID AS DEPT_ID,

PARENT_ID,DEPT_NAME,AMOUNT

FROM T_DEPT_TEMP

CONNECT BY PRIOR PARENT_ID = DEPT_ID --找出所有DEPT_ID等于目前記錄PARENT_ID的記錄

START WITH DEPT_ID = 4 --從部門4開始遞歸查詢。

;

DEPT_ID PARENT_ID DEPT_NAME AMOUNT

---------- --------- ---------- ------

4 2 1-2-4 10

2 1 1-2 15

1 1 2

3、部門1的所有下級部門。

SELECT LPAD(' ',2*(LEVEL - 1), ' ')||DEPT_ID AS DEPT_ID,

PARENT_ID,DEPT_NAME,AMOUNT

FROM T_DEPT_TEMP

START WITH DEPT_ID = 1

CONNECT BY PARENT_ID = PRIOR DEPT_ID;

DEPT_ID PARENT_ID DEPT_NAME AMOUNT

---------- --------- ---------- ------

1 1 2

2 1 1-2 15

4 2 1-2-4 10

5 2 1-2-5 9

3 1 1-3 8

6 3 1-3-6 17

7 3 1-3-7 5

8 3 1-3-8 6

4、部門1及其所有下級部門，但是不包括部門3及其下級部門。（排除樹枝）

SELECT LPAD(' ',2*(LEVEL - 1), ' ')||DEPT_ID AS DEPT_ID,

PARENT_ID,DEPT_NAME,AMOUNT

FROM T_DEPT_TEMP

START WITH DEPT_ID = 1

CONNECT BY PARENT_ID = PRIOR DEPT_ID

AND DEPT_ID <> 3 -- 不包括部門3及其下屬部門(部門3和6、7、8都沒出現)

;

DEPT_ID PARENT_ID DEPT_NAME AMOUNT

---------- --------- ---------- ------

1 1 2

2 1 1-2 15

4 2 1-2-4 10

5 2 1-2-5 9

5、部門1及其所有下級部門，但是僅不包括部門3。（排除節點）

SELECT LPAD(' ',2*(LEVEL - 1), ' ')||DEPT_ID AS DEPT_ID,

PARENT_ID,DEPT_NAME,AMOUNT

FROM T_DEPT_TEMP

WHERE DEPT_ID <>3 -- 僅僅不包括部門3(輸出結果中，3的下級部門6、7、8還是出現了)

START WITH DEPT_ID = 1

CONNECT BY PARENT_ID = PRIOR DEPT_ID --執行順序where在connect by之後

;

DEPT_ID PARENT_ID DEPT_NAME AMOUNT

---------- --------- ---------- ------

1 1 2

2 1 1-2 15

4 2 1-2-4 10

5 2 1-2-5 9

6 3 1-3-6 17

7 3 1-3-7 5

8 3 1-3-8 6

6、部門1及其所有下級部門，且所有部門按照人數升序排列。

SELECT LPAD(' ',2*(LEVEL - 1), ' ')||DEPT_ID AS DEPT_ID,

PARENT_ID,DEPT_NAME,AMOUNT

FROM T_DEPT_TEMP

START WITH DEPT_ID = 1

CONNECT BY PARENT_ID = PRIOR DEPT_ID

ORDER BY AMOUNT ASC -- 排序在最後被執行，是以DEPT_ID完全被打亂了，而且層級關系也打亂了。

;

-- In a hierarchical query, do not specify either ORDER BY or GROUP BY,

-- as they will destroy the hierarchical order of the CONNECT BY results.

DEPT_ID PARENT_ID DEPT_NAME AMOUNT

---------- --------- ---------- ------

1 1 2

7 3 1-3-7 5

8 3 1-3-8 6

3 1 1-3 8

5 2 1-2-5 9

4 2 1-2-4 10

2 1 1-2 15

6 3 1-3-6 17

部門1及其所有下級部門，每個部門的下一級部門之間，按照人數降序排列。(有同一上級的那些部門？？？

-- If you want to order rows of siblings of the same parent,

-- then use the ORDER SIBLINGS BY clause.

SELECT LPAD(' ',2*(LEVEL - 1), ' ')||DEPT_ID AS DEPT_ID,

PARENT_ID,DEPT_NAME,AMOUNT

FROM T_DEPT_TEMP

START WITH DEPT_ID = 1

CONNECT BY PARENT_ID = PRIOR DEPT_ID

ORDER SIBLINGS BY AMOUNT ASC -- 同屬部門間排序

;

-- 輸出結果可見，部門3、2作為一組進行排序，部門7、8、6一組，5、4一組。

DEPT_ID PARENT_ID DEPT_NAME AMOUNT

---------- --------- ---------- ------

1 1 2

3 1 1-3 8

7 3 1-3-7 5

8 3 1-3-8 6

6 3 1-3-6 17

2 1 1-2 15

5 2 1-2-5 9

4 2 1-2-4 10

　　三、要點總結

1、子句的文法書寫順序。

select -> from -> where -> start with -> connect by -> order by

where寫在connect by後面就不行，報錯。

2、子句的執行順序

from -> start with -> connect by -> where -> select -> order by

　　執行順序where在connect by之後，可以從例5證明。

　　可是書寫SQL語句的時候，卻隻能寫前面，注意了解。

3、如何了解和記憶“CONNECT BY PRIOR PARENT_ID = DEPT_ID ”的含義呢？

　　現在看這個例子似乎很直覺，但是今後實際應用時，條件變化後，如何推斷查詢結果呢？

　　這裡我自己總結一種方法，前提是要了解SQL語句執行時，是一條一條記錄來處理的。

　　每條滿足START WITH語句條件的記錄被依次取出，暫且把每次被取出處理的記錄，稱為目前記錄。

“PRIOR PARENT_ID”表明從目前記錄得到PARENT_ID，

　　然後" = DEPT_ID"說明找到表中所有DEPT_ID等于目前記錄PARENT_ID的記錄，也就是找目前記錄PARENT_ID所指向的記錄。

　　因為PARENT_ID的取值含義是上級節點，是以說明是向樹的根節點方向的搜尋。（我的上級是誰？）

　　反之，如果是“CONNECT BY PARENT_ID = PRIOR DEPT_ID”，“PRIOR”在DEPT_ID一邊，就是找所有PARENT_ID等于目前記錄DEPT_ID的記錄，是向樹的葉子方向的搜尋。（誰的上級是我？）

　　找到結果記錄集以後，從第一條記錄開始遞歸處理，依此類推。

4、前序周遊

　　由于是遞歸處理，從例3可以看出，樹的根節點向葉子節點遞歸查詢時，查詢節點的順序是按照樹的前序周遊進行的。

5、排序

　　例6和例7說明了兩種排序的差別。

In a hierarchical query, do not specify either ORDER BY or GROUP BY, as they will destroy the hierarchical order of the CONNECT BY results. If you want to order rows of siblings of the same parent, then use the ORDER SIBLINGS BY clause. See order_by_clause.

6、僞列LEVEL

　　隻能随CONNECT BY子句一起使用，是一個整數，代表遞歸的層次深度。也就是節點在樹中所處深度。

　　根節點時等于1，根節點的葉子節點的深度等于2，依此類推。

LPAD(' ',2*(LEVEL - 1), ' ')||DEPT_ID正是利用了LEVEL來為每個層級的字段提供不同的縮進。

（遞歸查詢）關于oracle樹結構查詢展示分組，查找父節點，查找子節點問題

繼續閱讀

[引]在Oracle中如何利用Rowid查找和删除表中的重複記錄

oralce與mysql中，如何删除重複記錄

查詢表中重複記錄

ORACLE 表壓縮

oracle中的start with connect by用法

使用JDeveloper Remote debug PLSQL程式

LoadRunner 的多種Vuser類型

性能測試（并發負載壓力）測試分析－簡要篇

賦予使用者dba權限--解釋以上：

Oracle的基本操作

UVA 110 Meta-Loopless Sorts

為什麼要選擇UniDAC

UVA 620 Cellular Structure

windows不能在本地計算機上運作oracleDbConsoleorcl

SQL語言基礎：常用的資料查詢語句

Oracle 批量查詢傳入List 傳回List

（遞歸查詢）關于oracle樹結構查詢 展示 分組，查找父節點，查找子節點問題

繼續閱讀

（遞歸查詢）關于oracle樹結構查詢展示分組，查找父節點，查找子節點問題