|
Oracle樹查詢的最重要的就是select...start with... connect by ...prior 語法了。依托于該語法，我們可以將一個(gè)表形結(jié)構(gòu)的中以樹的順序列出來。在下面列述了Oracle中樹型查詢的常用查詢方式以及經(jīng)常使用的與樹查詢相關(guān)的Oracle特性函數(shù)等，在這里只涉及到一張表中的樹查詢方式而不涉及多表中的關(guān)聯(lián)等。
以我做過的一個(gè)項(xiàng)目中的表為例，表結(jié)構(gòu)如下：
Sql代碼
CREATE TABLE FLFL ( ID NUMBER NOT NULL, MC NVARCHAR2(20), FLJB NUMBER, SJFLID NUMBER ) CREATE TABLE FLFL ( ID NUMBER NOT NULL, MC NVARCHAR2(20), FLJB NUMBER, SJFLID NUMBER ) FLJB是作為樹的級別，在很多查詢中可以加快SQL的查詢效率。在下面演示的功能基本上不使用這個(gè)關(guān)鍵字。 SJFLID存儲的是上級ID，如果是頂級父節(jié)點(diǎn)，該SJFLID為null（得補(bǔ)充一句，當(dāng)初的確是這樣設(shè)計(jì)的，不過現(xiàn)在知道，表中最好別有null記錄，這會引起全文掃描，建議改成0代替)。
我們從最基本的操作，逐步列出樹查詢中常見的操作，所以查詢出來的節(jié)點(diǎn)以家族中的輩份作比方。
1. 查找樹中的所有頂級父節(jié)點(diǎn)（輩份最長的人）。 假設(shè)這個(gè)樹是個(gè)目錄結(jié)構(gòu)，那么第一個(gè)操作總是找出所有的頂級節(jié)點(diǎn)，再根據(jù)該節(jié)點(diǎn)找到其下屬節(jié)點(diǎn)。
Sql代碼
SELECT * FROM flfl WHERE sjflid IS NULL; SELECT * FROM flfl WHERE sjflid IS NULL; 這是個(gè)引子，沒用到樹型查詢。 2.查找一個(gè)節(jié)點(diǎn)的直屬子節(jié)點(diǎn)（所有兒子）。 如果查找的是直屬子類節(jié)點(diǎn)，也是不用用到樹型查詢的。
Sql代碼
SELECT * FROM flfl WHERE sjflid = 819459; SELECT * FROM flfl WHERE sjflid = 819459; 這個(gè)可以找到ID為819459的直屬子類節(jié)點(diǎn)。 3.查找一個(gè)節(jié)點(diǎn)的所有 直屬子節(jié)點(diǎn)（所有后代）。
Sql代碼
SELECT * FROM flfl START WITH ID = 819459 CONNECT BY sjflid = PRIOR ID; SELECT * FROM flfl START WITH ID = 819459 CONNECT BY sjflid = PRIOR ID; 這個(gè)查找的是ID為819459的節(jié)點(diǎn)下的所有直屬子類節(jié)點(diǎn)，包括子輩的和孫子輩的所有直屬節(jié)點(diǎn)。 4.查找一個(gè)節(jié)點(diǎn)的直屬父節(jié)點(diǎn)（父親）。 如果查找的是節(jié)點(diǎn)的直屬父節(jié)點(diǎn)，也是不用用到樹型查詢的。
Sql代碼
SELECT b.* FROM flfl a JOIN flfl b ON a.sjflid = b.ID WHERE a.ID = 6758; SELECT b.* FROM flfl a JOIN flfl b ON a.sjflid = b.ID WHERE a.ID = 6758; 這個(gè)找到的是ID為6758的節(jié)點(diǎn)的直屬父節(jié)點(diǎn)，要用到同一張表的關(guān)聯(lián)了。 5.查找一個(gè)節(jié)點(diǎn)的所有直屬父節(jié)點(diǎn)（祖宗）。
Sql代碼
SELECT * FROM flfl START WITH ID = 6758 CONNECT BY PRIOR sjflid = ID; SELECT * FROM flfl START WITH ID = 6758 CONNECT BY PRIOR sjflid = ID; 這里查找的就是ID為6758的所有直屬父節(jié)點(diǎn)，打個(gè)比方就是找到一個(gè)人的父親、祖父等。但是值得注意的是這個(gè)查詢出來的結(jié)果的順序是先列出子類節(jié)點(diǎn)再列出父類節(jié)點(diǎn)，姑且認(rèn)為是個(gè)倒序吧。 上面列出兩個(gè)樹型查詢方式，第3條語句和第5條語句，這兩條語句之間的區(qū)別在于prior關(guān)鍵字的位置不同，所以決定了查詢的方式不同。 當(dāng)sjflid = PRIOR ID時(shí)，數(shù)據(jù)庫會根據(jù)當(dāng)前的ID迭代出sjflid與該ID相同的記錄，所以查詢的結(jié)果是迭代出了所有的子類記錄；而PRIOR ID = sjflid時(shí)，數(shù)據(jù)庫會跟據(jù)當(dāng)前的sjflid來迭代出與當(dāng)前的sjflid相同的id的記錄，所以查詢出來的結(jié)果就是所有的父類結(jié)果。
以下是一系列針對樹結(jié)構(gòu)的更深層次的查詢，這里的查詢不一定是最優(yōu)的查詢方式，或許只是其中的一種實(shí)現(xiàn)而已。
6.查詢一個(gè)節(jié)點(diǎn)的兄弟節(jié)點(diǎn)（親兄弟）。
Sql代碼
SELECT a.* FROM flfl a WHERE EXISTS (SELECT * FROM flfl b WHERE a.sjflid = b.sjflid AND b.ID = 6757); SELECT a.* FROM flfl a WHERE EXISTS (SELECT * FROM flfl b WHERE a.sjflid = b.sjflid AND b.ID = 6757); 這里查詢的就是與ID為6757的節(jié)點(diǎn)同屬一個(gè)父節(jié)點(diǎn)的節(jié)點(diǎn)了，就好比親兄弟了。 7.查詢與一個(gè)節(jié)點(diǎn)同級的節(jié)點(diǎn)（族兄弟）。 如果在表中設(shè)置了級別的字段，上表中的FLJB，那么在做這類查詢時(shí)會很輕松，同一級別的就是與那個(gè)節(jié)點(diǎn)同級的，在這里列出不使用該字段時(shí)的實(shí)現(xiàn)!
Sql代碼
WITH tmp AS (SELECT a.*, LEVEL lev FROM flfl a START WITH a.sjflid IS NULL CONNECT BY a.sjflid = PRIOR a.ID) SELECT * FROM tmp WHERE lev = (SELECT lev FROM tmp WHERE ID = 819394) WITH tmp AS (SELECT a.*, LEVEL lev FROM flfl a START WITH a.sjflid IS NULL CONNECT BY a.sjflid = PRIOR a.ID) SELECT * FROM tmp WHERE lev = (SELECT lev FROM tmp WHERE ID = 819394) 這里使用兩個(gè)技巧，一個(gè)是使用了LEVEL來標(biāo)識每個(gè)節(jié)點(diǎn)在表中的級別，還有就是使用with語法模擬出了一張帶有級別的臨時(shí)表。 8.查詢一個(gè)節(jié)點(diǎn)的父節(jié)點(diǎn)的的兄弟節(jié)點(diǎn)（伯父與叔父）。
Sql代碼
WITH tmp AS (SELECT flfl.*, LEVEL lev FROM flfl START WITH sjflid IS NULL CONNECT BY sjflid = PRIOR ID) SELECT b.* FROM tmp b, (SELECT * FROM tmp WHERE ID = 7004 AND lev = 2) a WHERE b.lev = 1 UNION ALL SELECT * FROM tmp WHERE sjflid = (SELECT DISTINCT x.ID FROM tmp x, tmp y, (SELECT * FROM tmp WHERE ID = 7004 AND lev > 2) z WHERE y.ID = z.sjflid AND x.ID = y.sjflid); WITH tmp AS (SELECT flfl.*, LEVEL lev FROM flfl START WITH sjflid IS NULL CONNECT BY sjflid = PRIOR ID) SELECT b.* FROM tmp b, (SELECT * FROM tmp WHERE ID = 7004 AND lev = 2) a WHERE b.lev = 1 UNION ALL SELECT * FROM tmp WHERE sjflid = (SELECT DISTINCT x.ID FROM tmp x, tmp y, (SELECT * FROM tmp WHERE ID = 7004 AND lev > 2) z WHERE y.ID = z.sjflid AND x.ID = y.sjflid); 這里查詢分成以下幾步。首先，將第7個(gè)一樣，將全表都使用臨時(shí)表加上級別；其次，根據(jù)級別來判斷有幾種類型，以上文中舉的例子來說，有三種情況：（1）當(dāng)前節(jié)點(diǎn)為頂級節(jié)點(diǎn)，即查詢出來的lev值為1，那么它沒有上級節(jié)點(diǎn)，不予考慮。（2）當(dāng)前節(jié)點(diǎn)為2級節(jié)點(diǎn)，查詢出來的lev值為2，那么就只要保證lev級別為1的就是其上級節(jié)點(diǎn)的兄弟節(jié)點(diǎn)。（3）其它情況就是3以及以上級別，那么就要選查詢出來其上級的上級節(jié)點(diǎn)（祖父），再來判斷祖父的下級節(jié)點(diǎn)都是屬于該節(jié)點(diǎn)的上級節(jié)點(diǎn)的兄弟節(jié)點(diǎn)。 最后，就是使用UNION將查詢出來的結(jié)果進(jìn)行結(jié)合起來，形成結(jié)果集。 9.查詢一個(gè)節(jié)點(diǎn)的父節(jié)點(diǎn)的同級節(jié)點(diǎn)（族叔）。
這個(gè)其實(shí)跟第7種情況是相同的。
Sql代碼
WITH tmp AS (SELECT a.*, LEVEL lev FROM flfl a START WITH a.sjflid IS NULL CONNECT BY a.sjflid = PRIOR a.ID) SELECT * FROM tmp WHERE lev = (SELECT lev FROM tmp WHERE ID = 819394) - 1 WITH tmp AS (SELECT a.*, LEVEL lev FROM flfl a START WITH a.sjflid IS NULL CONNECT BY a.sjflid = PRIOR a.ID) SELECT * FROM tmp WHERE lev = (SELECT lev FROM tmp WHERE ID = 819394) - 1 只需要做個(gè)級別判斷就成了。 基本上，常見的查詢在里面了，不常見的也有部分了。其中，查詢的內(nèi)容都是節(jié)點(diǎn)的基本信息，都是數(shù)據(jù)表中的基本字段，但是在樹查詢中還有些特殊需求，是對查詢數(shù)據(jù)進(jìn)行了處理的，常見的包括列出樹路徑等。
補(bǔ)充一個(gè)概念，對于數(shù)據(jù)庫來說，根節(jié)點(diǎn)并不一定是在數(shù)據(jù)庫中設(shè)計(jì)的頂級節(jié)點(diǎn)，對于數(shù)據(jù)庫來說，根節(jié)點(diǎn)就是start with開始的地方。
下面列出的是一些與樹相關(guān)的特殊需求。
10.名稱要列出名稱全部路徑。
這里常見的有兩種情況，一種是是從頂級列出，直到當(dāng)前節(jié)點(diǎn)的名稱（或者其它屬性）；一種是從當(dāng)前節(jié)點(diǎn)列出，直到頂級節(jié)點(diǎn)的名稱（或其它屬性）。舉地址為例：國內(nèi)的習(xí)慣是從省開始、到市、到縣、到居委會的，而國外的習(xí)慣正好相反（老師說的，還沒接過國外的郵件，誰能寄個(gè)瞅瞅 ）。
從頂部開始：
Sql代碼
SELECT SYS_CONNECT_BY_PATH (mc, '/') FROM flfl WHERE ID = 6498 START WITH sjflid IS NULL CONNECT BY sjflid = PRIOR ID; SELECT SYS_CONNECT_BY_PATH (mc, '/') FROM flfl WHERE ID = 6498 START WITH sjflid IS NULL CONNECT BY sjflid = PRIOR ID; 從當(dāng)前節(jié)點(diǎn)開始： Sql代碼
SELECT SYS_CONNECT_BY_PATH (mc, '/') FROM flfl START WITH ID = 6498 CONNECT BY PRIOR sjflid = ID; SELECT SYS_CONNECT_BY_PATH (mc, '/') FROM flfl START WITH ID = 6498 CONNECT BY PRIOR sjflid = ID; 在這里我又不得不放個(gè)牢騷了。oracle只提供了一個(gè)sys_connect_by_path函數(shù)，卻忘了字符串的連接的順序。在上面的例子中，第一個(gè)SQL是從根節(jié)點(diǎn)開始遍歷，而第二個(gè)SQL是直接找到當(dāng)前節(jié)點(diǎn)，從效率上來說已經(jīng)是千差萬別，更關(guān)鍵的是第一個(gè)SQL只能選擇一個(gè)節(jié)點(diǎn)，而第二個(gè)SQL卻是遍歷出了一顆樹來。再次PS一下。 sys_connect_by_path函數(shù)就是從start with開始的地方開始遍歷，并記下其遍歷到的節(jié)點(diǎn)，start with開始的地方被視為根節(jié)點(diǎn)，將遍歷到的路徑根據(jù)函數(shù)中的分隔符，組成一個(gè)新的字符串，這個(gè)功能還是很強(qiáng)大的。
11.列出當(dāng)前節(jié)點(diǎn)的根節(jié)點(diǎn)。
在前面說過，根節(jié)點(diǎn)就是start with開始的地方。
Sql代碼
SELECT CONNECT_BY_ROOT mc, flfl.* FROM flfl START WITH ID = 6498 CONNECT BY PRIOR sjflid = ID; SELECT CONNECT_BY_ROOT mc, flfl.* FROM flfl START WITH ID = 6498 CONNECT BY PRIOR sjflid = ID; connect_by_root函數(shù)用來列的前面，記錄的是當(dāng)前節(jié)點(diǎn)的根節(jié)點(diǎn)的內(nèi)容。 12.列出當(dāng)前節(jié)點(diǎn)是否為葉子。
這個(gè)比較常見，尤其在動態(tài)目錄中，在查出的內(nèi)容是否還有下級節(jié)點(diǎn)時(shí)，這個(gè)函數(shù)是很適用的。
Sql代碼
SELECT CONNECT_BY_ISLEAF, flfl.* FROM flfl START WITH sjflid IS NULL CONNECT BY sjflid = PRIOR ID; SELECT CONNECT_BY_ISLEAF, flfl.* FROM flfl START WITH sjflid IS NULL CONNECT BY sjflid = PRIOR ID; connect_by_isleaf函數(shù)用來判斷當(dāng)前節(jié)點(diǎn)是否包含下級節(jié)點(diǎn)，如果包含的話，說明不是葉子節(jié)點(diǎn)，這里返回0；反之，如果不包含下級節(jié)點(diǎn)，這里返回1。 至此，oracle樹型查詢基本上講完了，以上的例子中的數(shù)據(jù)是使用到做過的項(xiàng)目中的數(shù)據(jù)，因?yàn)槔锩娴膬?nèi)容可能不好理解，所以就全部用一些新的例子來進(jìn)行闡述。以上所有SQL都在本機(jī)上測試通過，也都能實(shí)現(xiàn)相應(yīng)的功能，但是并不能保證是解決這類問題的最優(yōu)方案（如第8條明顯寫成存儲過程會更好），如果誰有更好的解決方案、或者有關(guān)oracle樹查詢的任何問題，歡迎留言討論，以上的SQL有什么問題也歡迎大家留言批評。
|
|
|
