oracle 2010-09-21 11:32:18 閱讀132 評論0 字號：大中小 訂閱
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、先假設有如下部門結構。
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來為每個層級的字段提供不同的縮進。
在樹中對每一個級別用一個標識符進行標識，比如全國行政區域，省用1，市用2，縣用3等等，