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

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

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

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

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

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

[[START WITH condition1] CONNECT BY condition2]

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

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

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

三、實(shí)例比較：

下面我們通過(guò)2條SQL語(yǔ)句來(lái)演示如何進(jìn)行級(jí)聯(lián)查詢，以及PRIOR在不同位置時(shí)帶來(lái)的不同結(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

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

我們已經(jīng)知道PRIOR放在那一側(cè)，那一側(cè)的字段就會(huì)被當(dāng)成父記錄的字段而被用于和當(dāng)前記錄的字段(另一側(cè)的表達(dá)式)進(jìn)行比較，那么假如我們把PRIOR放在manager_id一側(cè)，結(jié)果會(huì)有什么不同嗎？請(qǐng)看下面的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é)果完全不同呢？說(shuō)到這里我們還要再回到SQL語(yǔ)言本身，我用一種比較直白的方式來(lái)講解不同位置的PRIROR所帶來(lái)的不同意義。

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

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

總結(jié)：Prior放在那里，那一側(cè)就是被比較的一方(父方)，另一側(cè)就是發(fā)起比較的一方(子方)。語(yǔ)義上可以這樣翻譯：xxx字段的值必須等于當(dāng)前記錄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 閱讀(4349) 評(píng)論(6) 編輯收藏所屬分類: Oracle 開(kāi)發(fā)

FeedBack:

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

2008-06-15 14:43 | 藍(lán)劍

明白了，謝謝！回復(fù) 更多評(píng)論