oracle 2010-09-21 11:32:18 閱讀132 評論0 字號：大中小 訂閱
Oracle 實在太強了，本篇文章詳細(xì)介紹了Oracle的遞歸查詢語法，利用此語法，可以方便地實現(xiàn)遞歸的雙向查詢：
--
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
目 錄
一、測試準(zhǔn)備
二、實現(xiàn)各種查詢要求
三、要點總結(jié)
正 文
一、測試準(zhǔn)備
1、先假設(shè)有如下部門結(jié)構(gòu)。
1
/ \
2 3
/\ /|\
4 5 6 7 8
2、然后建立測試表和數(shù)據(jù)。
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)
--人數(shù)
);
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、調(diào)整一下輸出格式
col DEPT_ID format A10;
二、接下來實現(xiàn)各種查詢要求
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等于當(dāng)前記錄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等于當(dāng)前記錄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都沒出現(xiàn))
;
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。（排除節(jié)點）
SELECT LPAD(' ',2*(LEVEL - 1), '
')||DEPT_ID AS DEPT_ID,
PARENT_ID,DEPT_NAME,AMOUNT
FROM T_DEPT_TEMP
WHERE DEPT_ID
<>3 -- 僅僅不包括部門3(輸出結(jié)果中，3的下級部門6、7、8還是出現(xiàn)了)
START WITH DEPT_ID = 1
CONNECT BY PARENT_ID = PRIOR DEPT_ID -- 執(zhí)行順序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及其所有下級部門，且所有部門按照人數(shù)升序排列。
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 -- 排序在最后被執(zhí)行，所以DEPT_ID完全被打亂了，而且層級關(guān)系也打亂了。
;
-- 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及其所有下級部門，每個部門的下一級部門之間，按照人數(shù)降序排列。(有同一上級的那些部門？？？
-- 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 -- 同屬部門間排序
;
-- 輸出結(jié)果可見，部門3、2作為一組進(jìn)行排序，部門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
三、要點總結(jié)
1、子句的語法書寫順序。
select -> from -> where ->
start with -> connect by -> order by
where寫在connect by后面就不行，報錯。
2、子句的執(zhí)行順序
from -> start with -> connect by
-> where -> select -> order by
執(zhí)行順序where在connect by之后，可以從例5證明。
可是書寫SQL語句的時候，卻只能寫前面，注意理解。
3、如何理解和記憶“CONNECT BY PRIOR PARENT_ID = DEPT_ID ”的含義呢？
現(xiàn)在看這個例子似乎很直觀，但是今后實際應(yīng)用時，條件變化后，如何推斷查詢結(jié)果呢？
這里我自己總結(jié)一種方法，前提是要理解SQL語句執(zhí)行時，是一條一條記錄來處理的。
每條滿足START WITH語句條件的記錄被依次取出，暫且把每次被取出處理的記錄，稱為當(dāng)前記錄。
“PRIOR PARENT_ID”表明從當(dāng)前記錄得到PARENT_ID，
然后" = DEPT_ID"說明找到表中所有DEPT_ID等于當(dāng)前記錄PARENT_ID的記錄，也就是找當(dāng)前記錄PARENT_ID所指向的記錄。
因為PARENT_ID的取值含義是上級節(jié)點，所以說明是向樹的根節(jié)點方向的搜索。（我的上級是誰？）
反之，如果是“CONNECT BY PARENT_ID = PRIOR DEPT_ID”，“PRIOR”在DEPT_ID一邊，就是找所有PARENT_ID等于當(dāng)前記錄DEPT_ID的記錄，是向樹的葉子方向的搜索。（誰的上級是我？）
找到結(jié)果記錄集以后，從第一條記錄開始遞歸處理，依此類推。
4、前序遍歷
由于是遞歸處理，從例3可以看出，樹的根節(jié)點向葉子節(jié)點遞歸查詢時，查詢節(jié)點的順序是按照樹的前序遍歷進(jìn)行的。
5、排序
例6和例7說明了兩種排序的區(qū)別。
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子句一起使用，是一個整數(shù)，代表遞歸的層次深度。也就是節(jié)點在樹中所處深度。
根節(jié)點時等于1，根節(jié)點的葉子節(jié)點的深度等于2，依此類推。
LPAD(' ',2*(LEVEL - 1), ' ')||DEPT_ID 正是利用了LEVEL來為每個層級的字段提供不同的縮進(jìn)。
在樹中對每一個級別用一個標(biāo)識符進(jìn)行標(biāo)識，比如全國行政區(qū)域，省用1，市用2，縣用3等等，