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