【原】Oracle開發(fā)專題之：級聯(lián)查詢(Hierarchical Queries)

一、級聯(lián)數(shù)據(jù)的表示：

首先我們來看一張關(guān)于組織架構(gòu)的結(jié)構(gòu)圖

這張圖是一個典型的“樹型結(jié)構(gòu)圖”，只有一個根節(jié)點(King)，其下有若干個分支節(jié)點，每個分支節(jié)點下又有若干個子節(jié)點或樹葉節(jié)點。假如我們要把這些關(guān)系信息映射到數(shù)據(jù)庫中(此處以O(shè)racle9i數(shù)據(jù)庫為例)，表結(jié)構(gòu)應(yīng)當如何表示呢？

CREATE TABLE EMPLOYEE (

EMP_ID NUMBER (4) CONSTRAINT EMP_PK PRIMARY KEY,

FNAME VARCHAR2 (15)NOT NULL,

LNAME VARCHAR2 (15)NOT NULL,

DEPT_ID NUMBER (2)NOT NULL,

MANAGER_EMP_ID NUMBER (4) CONSTRAINT EMP_FK REFERENCES EMPLOYEE(EMP_ID),

SALARY NUMBER (7,2)NOT NULL,

HIRE_DATE DATENOT NULL,

JOB_ID NUMBER (3));

請注意這里紅色粗體部分，字段MANAGER_EMP_ID的值引用了字段EMP_ID的值，我們稱這種引用為“自引用”。它規(guī)定了經(jīng)理人員的ID必須是來自表中存在的員工ID。

二、Oracle 9i中的start with...connect by:

[[START WITH condition1] CONNECT BY condition2]

START WITH condition1
指定級聯(lián)數(shù)據(jù)的根記錄(一條或多條)，所有滿足條件1的記錄都將被當成是根紀錄，假如我們不給定START WITH子句，所有的紀錄都會被當成是根紀錄，通常這不是我們想要的結(jié)果。condition1可以是一個子查詢。

CONNECT BY condition2
指定級聯(lián)數(shù)據(jù)中父紀錄和子紀錄之間的關(guān)系，這里的關(guān)系被表示成一個表達式，當前紀錄的字段會和對應(yīng)的父紀錄的某個字段進行比較。condition2必須跟著一個PRIOR操作符，該操作符用于標明父紀錄的字段。condtion2不能包含子查詢

PRIOR是Oracle的一個內(nèi)建操作符，僅用于級聯(lián)查詢。當我們在級聯(lián)查詢的CONNECT BY條件中使用了PRIOR操作符時，位于其后的表達式被當成是當前紀錄的父紀錄進行比較。

三、實例比較：

下面我們通過2條SQL語句來演示如何進行級聯(lián)查詢，以及PRIOR在不同位置時帶來的不同結(jié)果。

SQL> select * from employee;

ID EMP_NAME MANAGER_ID

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

1 king

2 mark 1

3 bob 1

4 tom 2

5 paul 2

6 jack 3

7 ben 4

7 rows selected.

需求：我們要找出員工ID為2的人及其所有下屬(包括直接和間接下屬)

SQL> select * from employee start with id = 2 connect by prior id = manager_id order by id;

ID EMP_NAME MANAGER_ID

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

2 mark 1

4 tom 2

5 paul 2

7 ben 4

請注意PRIOR操作符被放置在字段ID前面。查詢結(jié)果中ID為7的員工ben，雖然其對應(yīng)的經(jīng)理ID為4，但是因為員工號為4的tom，其對應(yīng)的經(jīng)理ID為2，所以ben是屬于mark的間接下屬而符合查詢條件。

我們已經(jīng)知道PRIOR放在那一側(cè)，那一側(cè)的字段就會被當成父記錄的字段而被用于和當前記錄的字段(另一側(cè)的表達式)進行比較，那么假如我們把PRIOR放在manager_id一側(cè)，結(jié)果會有什么不同嗎？請看下面的SQL執(zhí)行結(jié)果。

SQL> select * from employee start with id = 2 connect by id = prior manager_id order by id;

ID EMP_NAME MANAGER_ID

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

1 king

2 mark 1

很明顯結(jié)果完全不同，那么是什么造成了兩次查詢的結(jié)果完全不同呢？說到這里我們還要再回到SQL語言本身，我用一種比較直白的方式來講解不同位置的PRIROR所帶來的不同意義。

【1】第一個查詢：connect by prior id = manager_id，意思是從當前根記錄開始，查找所有符合條件的記錄：他們的manager_id必須等于當前記錄的id。也就是說查找所有manager_id=2的記錄及其子記錄，很明顯manager_id=2的記錄只有tom和paul，但是由于ben的直接領(lǐng)導tom是mark的下屬，所以ben也是mark的下屬，只不過是間接關(guān)系而已。

【2】第二個查詢：connect by id = prior manager_id，意思是從當前根記錄開始，查找所有符合條件的記錄：他們的id必須等于當前記錄的manager_id。也就是說查找所以id=1的記錄，那么很明顯id=1的記錄只有king。

總結(jié)：Prior放在那里，那一側(cè)就是被比較的一方(父方)，另一側(cè)就是發(fā)起比較的一方(子方)。語義上可以這樣翻譯：xxx字段的值必須等于當前記錄XXX字段的值(prior一方)

參考資料：《Mastering Oracle SQL》(By Alan Beaulieu, Sanjay Mishra O'Reilly June 2004 0-596-00632-2)

-------------------------------------------------------------
生活就像打牌，不是要抓一手好牌，而是要盡力打好一手爛牌。

posted on 2008-06-11 17:55 Paul Lin 閱讀(4356) 評論(6) 編輯收藏所屬分類: Oracle 開發(fā)

FeedBack:

# re: Oracle開發(fā)專題之：級聯(lián)查詢(Hierarchical Queries)

2008-06-15 14:43 | 藍劍

明白了，謝謝！回復更多評論