Oracle樹查詢及函數(shù)總結(jié)

準(zhǔn)備測試表和測試數(shù)據(jù)

-- 菜單目錄結(jié)構(gòu)表
CREATE TABLE TB_MENU (
   ID NUMBER(10) NOT NULL, -- 主鍵ID
   TITLE VARCHAR2(50),     -- 標(biāo)題
   PARENT NUMBER(10)       -- PARENT ID
)

-- 父菜單
INSERT INTO TB_MENU(ID, TITLE, PARENT) VALUES(1, '父菜單1',NULL);
INSERT INTO TB_MENU(ID, TITLE, PARENT) VALUES(2, '父菜單2',NULL);
INSERT INTO TB_MENU(ID, TITLE, PARENT) VALUES(3, '父菜單3',NULL);
INSERT INTO TB_MENU(ID, TITLE, PARENT) VALUES(4, '父菜單4',NULL);
INSERT INTO TB_MENU(ID, TITLE, PARENT) VALUES(5, '父菜單5',NULL);

--一級菜單
INSERT INTO TB_MENU(ID, TITLE, PARENT) VALUES(6, '一級菜單6',1);
INSERT INTO TB_MENU(ID, TITLE, PARENT) VALUES(7, '一級菜單7',1);
INSERT INTO TB_MENU(ID, TITLE, PARENT) VALUES(8, '一級菜單8',1);
INSERT INTO TB_MENU(ID, TITLE, PARENT) VALUES(9, '一級菜單9',2);
INSERT INTO TB_MENU(ID, TITLE, PARENT) VALUES(10, '一級菜單10',2);
INSERT INTO TB_MENU(ID, TITLE, PARENT) VALUES(11, '一級菜單11',2);
INSERT INTO TB_MENU(ID, TITLE, PARENT) VALUES(12, '一級菜單12',3);
INSERT INTO TB_MENU(ID, TITLE, PARENT) VALUES(13, '一級菜單13',3);
INSERT INTO TB_MENU(ID, TITLE, PARENT) VALUES(14, '一級菜單14',3);
INSERT INTO TB_MENU(ID, TITLE, PARENT) VALUES(15, '一級菜單15',4);
INSERT INTO TB_MENU(ID, TITLE, PARENT) VALUES(16, '一級菜單16',4);
INSERT INTO TB_MENU(ID, TITLE, PARENT) VALUES(17, '一級菜單17',4);
INSERT INTO TB_MENU(ID, TITLE, PARENT) VALUES(18, '一級菜單18',5);
INSERT INTO TB_MENU(ID, TITLE, PARENT) VALUES(19, '一級菜單19',5);
INSERT INTO TB_MENU(ID, TITLE, PARENT) VALUES(20, '一級菜單20',5);


--二級菜單
INSERT INTO TB_MENU(ID, TITLE, PARENT) VALUES(21, '二級菜單21',6);
INSERT INTO TB_MENU(ID, TITLE, PARENT) VALUES(22, '二級菜單22',6);
INSERT INTO TB_MENU(ID, TITLE, PARENT) VALUES(23, '二級菜單23',7);
INSERT INTO TB_MENU(ID, TITLE, PARENT) VALUES(24, '二級菜單24',7);
INSERT INTO TB_MENU(ID, TITLE, PARENT) VALUES(25, '二級菜單25',8);
INSERT INTO TB_MENU(ID, TITLE, PARENT) VALUES(26, '二級菜單26',9);
INSERT INTO TB_MENU(ID, TITLE, PARENT) VALUES(27, '二級菜單27',10);
INSERT INTO TB_MENU(ID, TITLE, PARENT) VALUES(28, '二級菜單28',11);
INSERT INTO TB_MENU(ID, TITLE, PARENT) VALUES(29, '二級菜單29',12);
INSERT INTO TB_MENU(ID, TITLE, PARENT) VALUES(30, '二級菜單30',13);
INSERT INTO TB_MENU(ID, TITLE, PARENT) VALUES(31, '二級菜單31',14);
INSERT INTO TB_MENU(ID, TITLE, PARENT) VALUES(32, '二級菜單32',15);
INSERT INTO TB_MENU(ID, TITLE, PARENT) VALUES(33, '二級菜單33',16);
INSERT INTO TB_MENU(ID, TITLE, PARENT) VALUES(34, '二級菜單34',17);
INSERT INTO TB_MENU(ID, TITLE, PARENT) VALUES(35, '二級菜單35',18);
INSERT INTO TB_MENU(ID, TITLE, PARENT) VALUES(36, '二級菜單36',19);
INSERT INTO TB_MENU(ID, TITLE, PARENT) VALUES(37, '二級菜單37',20);

--三級菜單
INSERT INTO TB_MENU(ID, TITLE, PARENT) VALUES(38, '三級菜單38',21);
INSERT INTO TB_MENU(ID, TITLE, PARENT) VALUES(39, '三級菜單39',22);
INSERT INTO TB_MENU(ID, TITLE, PARENT) VALUES(40, '三級菜單40',23);
INSERT INTO TB_MENU(ID, TITLE, PARENT) VALUES(41, '三級菜單41',24);
INSERT INTO TB_MENU(ID, TITLE, PARENT) VALUES(42, '三級菜單42',25);
INSERT INTO TB_MENU(ID, TITLE, PARENT) VALUES(43, '三級菜單43',26);
INSERT INTO TB_MENU(ID, TITLE, PARENT) VALUES(44, '三級菜單44',27);
INSERT INTO TB_MENU(ID, TITLE, PARENT) VALUES(45, '三級菜單45',28);
INSERT INTO TB_MENU(ID, TITLE, PARENT) VALUES(46, '三級菜單46',28);
INSERT INTO TB_MENU(ID, TITLE, PARENT) VALUES(47, '三級菜單47',29);
INSERT INTO TB_MENU(ID, TITLE, PARENT) VALUES(48, '三級菜單48',30);
INSERT INTO TB_MENU(ID, TITLE, PARENT) VALUES(49, '三級菜單49',31);
INSERT INTO TB_MENU(ID, TITLE, PARENT) VALUES(50, '三級菜單50',31);
COMMIT;
 
SELECT * FROM TB_MENU;

PARENT字段存儲的是上級ID，如果是頂級父節(jié)點，該P(yáng)ARENT為NULL(得補(bǔ)充一句，當(dāng)初的確是這樣設(shè)計的，不過現(xiàn)在知道，表中最好別有NULL記錄，這會引起全文掃描，建議改成0代替)。

樹操作

我們從最基本的操作，逐步列出樹查詢中常見的操作，所有查詢出來的節(jié)點以家族中的輩份作比方。

-- 1.查找樹中的所有頂級父節(jié)點（輩份最長的人）。 
-- 假設(shè)這個樹是個目錄結(jié)構(gòu)，那么第一個操作總是找出所有的頂級節(jié)點，再根據(jù)該節(jié)點找到其下屬節(jié)點。
SELECT * FROM TB_MENU M WHERE M.PARENT IS NULL;


-- 2.查找一個節(jié)點的直屬子節(jié)點（所有兒子）。 如果查找的是直屬子類節(jié)點，也是不用用到樹型查詢的。
SELECT * FROM TB_MENU M WHERE M.PARENT=1;

-- 3.查找一個節(jié)點的所有直屬子節(jié)點（所有后代）。
-- 這個查找的是ID為1的節(jié)點下的所有直屬子類節(jié)點，包括子輩的和孫子輩的所有直屬節(jié)點。
SELECT * FROM TB_MENU M START WITH M.ID=1 CONNECT BY M.PARENT=PRIOR M.ID;


-- 4.查找一個節(jié)點的直屬父節(jié)點（父親）。 如果查找的是節(jié)點的直屬父節(jié)點，也是不用用到樹型查詢的。
-- C-->CHILD, P->PARENT

SELECT C.ID
      ,C.TITLE
      ,P.ID PARENT_ID
      ,P.TITLE PARENT_TITLE
FROM TB_MENU C, TB_MENU P
WHERE C.PARENT=P.ID AND C.ID=6

-- 5.查找一個節(jié)點的所有直屬父節(jié)點（祖宗）。
-- M.PARENT=M2.PARENT-->同一個父親
SELECT * FROM TB_MENU M
WHERE EXISTS (SELECT * FROM TB_MENU M2 WHERE M.PARENT=M2.PARENT AND M2.ID=6)

-- 6.查詢與一個節(jié)點同級的節(jié)點（族兄弟）。 
-- 如果在表中設(shè)置了級別的字段，那么在做這類查詢時會很輕松，
-- 同一級別的就是與那個節(jié)點同級的，在這里列出不使用該字段時的實現(xiàn)!
-- 這里使用兩個技巧，一個是使用了level來標(biāo)識每個節(jié)點在表中的級別，還有就是使用with語法模擬出了一張帶有級別的臨時表。

WITH TMP AS(
      SELECT A.*, LEVEL LEAF        
      FROM TB_MENU A                
      START WITH A.PARENT IS NULL     
      CONNECT BY A.PARENT = PRIOR A.ID)
SELECT *                               
FROM TMP                             
WHERE LEAF = (SELECT LEAF FROM TMP WHERE ID = 50);

-- 7.查詢一個節(jié)點的父節(jié)點的的兄弟節(jié)點（伯父與叔父）。

WITH TMP AS(
    SELECT TB_MENU.*, LEVEL LEV
    FROM TB_MENU
    START WITH PARENT IS NULL
    CONNECT BY PARENT = PRIOR ID)
    
SELECT B.*
FROM TMP B,(SELECT *
            FROM TMP
            WHERE ID = 21 AND LEV = 2) A
WHERE B.LEV = 1
 
UNION ALL
 
SELECT *
FROM TMP
WHERE PARENT = (SELECT DISTINCT X.ID
                FROM TMP X, -- 祖父
                     TMP Y, -- 父親
                     (SELECT *
                      FROM TMP
                      WHERE ID = 21 AND LEV > 2) Z --兒子
                WHERE Y.ID = Z.PARENT AND X.ID = Y.PARENT);

這里查詢分成以下幾步。
首先，將第6個一樣，將全表都使用臨時表加上級別；
其次，根據(jù)級別來判斷有幾種類型，以上文中舉的例子來說，有三種情況：

  1. 當(dāng)前節(jié)點為頂級節(jié)點，即查詢出來的lev值為1，那么它沒有上級節(jié)點，不予考慮。
  2. 當(dāng)前節(jié)點為2級節(jié)點，查詢出來的lev值為2，那么就只要保證lev級別為1的就是其上級節(jié)點的兄弟節(jié)點。
  3. 其它情況就是3以及以上級別，那么就要選查詢出來其上級的上級節(jié)點（祖父），再來判斷祖父的下級節(jié)點都是屬于該節(jié)點的上級節(jié)點的兄弟節(jié)點。

最后，就是使用union將查詢出來的結(jié)果進(jìn)行結(jié)合起來，形成結(jié)果集。

-- 8.查詢一個節(jié)點的父節(jié)點的同級節(jié)點（族叔）。
-- 這個其實跟第7種情況是相同的。

WITH TMP AS(
      SELECT A.*, LEVEL LEAF        
      FROM TB_MENU A                
      START WITH A.PARENT IS NULL     
      CONNECT BY A.PARENT = PRIOR A.ID)
SELECT *                               
FROM TMP                             
WHERE LEAF = (SELECT LEAF FROM TMP WHERE ID = 6) - 1;

基本上，常見的查詢在里面了，不常見的也有部分了。其中，查詢的內(nèi)容都是節(jié)點的基本信息，都是數(shù)據(jù)表中的基本字段，但是在樹查詢中還有些特殊需求，是對查詢數(shù)據(jù)進(jìn)行了處理的，常見的包括列出樹路徑等。

補(bǔ)充一個概念，對于數(shù)據(jù)庫來說，根節(jié)點并不一定是在數(shù)據(jù)庫中設(shè)計的頂級節(jié)點，對于數(shù)據(jù)庫來說，根節(jié)點就是start with開始的地方。

-- 名稱要列出名稱全部路徑。從頂部開始：
SELECT SYS_CONNECT_BY_PATH (TITLE, '/')
FROM TB_MENU
WHERE ID = 50
START WITH PARENT IS NULL
CONNECT BY PARENT = PRIOR ID;

-- 從當(dāng)前節(jié)點開始：
SELECT SYS_CONNECT_BY_PATH (TITLE, '/')
FROM TB_MENU
START WITH ID = 50
CONNECT BY PRIOR PARENT = ID;

-- 列出當(dāng)前節(jié)點的根節(jié)點
-- CONNECT_BY_ROOT函數(shù)用來列的前面，記錄的是當(dāng)前節(jié)點的根節(jié)點的內(nèi)容。
SELECT CONNECT_BY_ROOT TITLE, TB_MENU.*
FROM TB_MENU
START WITH ID = 50
CONNECT BY PRIOR PARENT = ID;


-- 列出當(dāng)前節(jié)點是否為葉子。
-- 這個比較常見，尤其在動態(tài)目錄中，在查出的內(nèi)容是否還有下級節(jié)點時，這個函數(shù)是很適用的。
SELECT CONNECT_BY_ISLEAF, TB_MENU.*
FROM TB_MENU
START WITH PARENT IS NULL
CONNECT BY PARENT = PRIOR ID;

-- CONNECT_BY_ISLEAF函數(shù)用來判斷當(dāng)前節(jié)點是否包含下級節(jié)點，
-- 如果包含的話，說明不是葉子節(jié)點，這里返回0；反之，如果不包含下級節(jié)點，這里返回1。

至此，oracle樹型查詢基本上講完了，以上的例子中的數(shù)據(jù)是使用到做過的項目中的數(shù)據(jù)， 因為里面的內(nèi)容可能不好理解，所以就全部用一些新的例子來進(jìn)行闡述。
以上所有sql都在本機(jī)上測試通過，也都能實現(xiàn)相應(yīng)的功能，但是并不能保證是解決這類問題的最優(yōu)方案.

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容