Oracle 樹操作(select…start with…connect by…prior)

標籤：

oracle樹查詢的最重要的就是select…start with…connect by…prior文法了。依託於該文法，我們可以將一個表形結構的以樹的順序列出來。在下面列述了oracle中樹型查詢的常用查詢方式以及經常使用的與樹查詢相關的oracle特性函數等，在這裡只涉及到一張表中的樹查詢方式而不涉及多表中的關聯等。

1、準備測試表和測試資料

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364

--菜單目錄結構表createtabletb_menu(   id     number(10)notnull,--主鍵id   title  varchar2(50),--標題   parent number(10)--parent id) --父菜單insertintotb_menu(id, title, parent)values(1,‘父菜單1‘,null);insertintotb_menu(id, title, parent)values(2,‘父菜單2‘,null);insertintotb_menu(id, title, parent)values(3,‘父菜單3‘,null);insertintotb_menu(id, title, parent)values(4,‘父菜單4‘,null);insertintotb_menu(id, title, parent)values(5,‘父菜單5‘,null);--一級菜單insertintotb_menu(id, title, parent)values(6,‘一級菜單6‘,1);insertintotb_menu(id, title, parent)values(7,‘一級菜單7‘,1);insertintotb_menu(id, title, parent)values(8,‘一級菜單8‘,1);insertintotb_menu(id, title, parent)values(9,‘一級菜單9‘,2);insertintotb_menu(id, title, parent)values(10,‘一級菜單10‘,2);insertintotb_menu(id, title, parent)values(11,‘一級菜單11‘,2);insertintotb_menu(id, title, parent)values(12,‘一級菜單12‘,3);insertintotb_menu(id, title, parent)values(13,‘一級菜單13‘,3);insertintotb_menu(id, title, parent)values(14,‘一級菜單14‘,3);insertintotb_menu(id, title, parent)values(15,‘一級菜單15‘,4);insertintotb_menu(id, title, parent)values(16,‘一級菜單16‘,4);insertintotb_menu(id, title, parent)values(17,‘一級菜單17‘,4);insertintotb_menu(id, title, parent)values(18,‘一級菜單18‘,5);insertintotb_menu(id, title, parent)values(19,‘一級菜單19‘,5);insertintotb_menu(id, title, parent)values(20,‘一級菜單20‘,5);--二級菜單insertintotb_menu(id, title, parent)values(21,‘二級菜單21‘,6);insertintotb_menu(id, title, parent)values(22,‘二級菜單22‘,6);insertintotb_menu(id, title, parent)values(23,‘二級菜單23‘,7);insertintotb_menu(id, title, parent)values(24,‘二級菜單24‘,7);insertintotb_menu(id, title, parent)values(25,‘二級菜單25‘,8);insertintotb_menu(id, title, parent)values(26,‘二級菜單26‘,9);insertintotb_menu(id, title, parent)values(27,‘二級菜單27‘,10);insertintotb_menu(id, title, parent)values(28,‘二級菜單28‘,11);insertintotb_menu(id, title, parent)values(29,‘二級菜單29‘,12);insertintotb_menu(id, title, parent)values(30,‘二級菜單30‘,13);insertintotb_menu(id, title, parent)values(31,‘二級菜單31‘,14);insertintotb_menu(id, title, parent)values(32,‘二級菜單32‘,15);insertintotb_menu(id, title, parent)values(33,‘二級菜單33‘,16);insertintotb_menu(id, title, parent)values(34,‘二級菜單34‘,17);insertintotb_menu(id, title, parent)values(35,‘二級菜單35‘,18);insertintotb_menu(id, title, parent)values(36,‘二級菜單36‘,19);insertintotb_menu(id, title, parent)values(37,‘二級菜單37‘,20);--三級菜單insertintotb_menu(id, title, parent)values(38,‘三級菜單38‘,21);insertintotb_menu(id, title, parent)values(39,‘三級菜單39‘,22);insertintotb_menu(id, title, parent)values(40,‘三級菜單40‘,23);insertintotb_menu(id, title, parent)values(41,‘三級菜單41‘,24);insertintotb_menu(id, title, parent)values(42,‘三級菜單42‘,25);insertintotb_menu(id, title, parent)values(43,‘三級菜單43‘,26);insertintotb_menu(id, title, parent)values(44,‘三級菜單44‘,27);insertintotb_menu(id, title, parent)values(45,‘三級菜單45‘,28);insertintotb_menu(id, title, parent)values(46,‘三級菜單46‘,28);insertintotb_menu(id, title, parent)values(47,‘三級菜單47‘,29);insertintotb_menu(id, title, parent)values(48,‘三級菜單48‘,30);insertintotb_menu(id, title, parent)values(49,‘三級菜單49‘,31);insertintotb_menu(id, title, parent)values(50,‘三級菜單50‘,31);commit; select*fromtb_menu;

parent欄位儲存的是上級id，如果是頂級父節點，該parent為null(得補充一句，當初的確是這樣設計的，不過現在知道，表中最好別有null記錄，這會引起全文掃描，建議改成0代替)。

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

1)、尋找樹中的所有頂級父節點（輩份最長的人）。 假設這個樹是個目錄結構，那麼第一個操作總是找出所有的頂級節點，再根據該節點找到其下屬節點。

1	select * from tb_menu m where m.parent isnull;

2)、尋找一個節點的直屬子節點（所有兒子）。 如果尋找的是直屬子類節點，也是不用用到樹型查詢的。

1	select * from tb_menu m where m.parent=1;

3)、尋找一個節點的所有直屬子節點（所有後代）。

1	select * from tb_menu m start with m.id=1connect by m.parent=prior m.id;

這個尋找的是id為1的節點下的所有直屬子類節點，包括子輩的和孫子輩的所有直屬節點。

4)、尋找一個節點的直屬父節點（父親）。 如果尋找的是節點的直屬父節點，也是不用用到樹型查詢的。

1234	--c-->child, p->parentselect c.id, c.title, p.id parent_id, p.title parent_titlefrom tb_menu c, tb_menu pwhere c.parent=p.id and c.id=6

5)、尋找一個節點的所有直屬父節點（祖宗）。

1	select * from tb_menu m start with m.id=38connect by prior m.parent=m.id;

這裡尋找的就是id為1的所有直屬父節點，打個比方就是找到一個人的父親、祖父等。但是值得注意的是這個查詢出來的結果的順序是先列出子類節點再列出父類節點，姑且認為是個倒序吧。

上面列出兩個樹型查詢方式，第3條語句和第5條語句，這兩條語句之間的區別在於prior關鍵字的位置不同，所以決定了查詢的方式不同。 當parent = prior id時，資料庫會根據當前的id迭代出parent與該id相同的記錄，所以查詢的結果是迭代出了所有的子類記錄；而prior parent = id時，資料庫會跟據當前的parent來迭代出與當前的parent相同的id的記錄，所以查詢出來的結果就是所有的父類結果。

以下是一系列針對樹結構的更深層次的查詢，這裡的查詢不一定是最優的查詢方式，或許只是其中的一種實現而已。

6)、查詢一個節點的兄弟節點（親兄弟）。

123	--m.parent=m2.parent-->同一個父親select * from tb_menu mwhere exists (select * from tb_menu m2 where m.parent=m2.parent and m2.id=6)

7)、查詢與一個節點同級的節點（族兄弟）。 如果在表中設定了層級的欄位，那麼在做這類查詢時會很輕鬆，同一層級的就是與那個節點同級的，在這裡列出不使用該欄位時的實現!

12345678

with tmp as(      select a.*, level leaf             from tb_menu a                     start with a.parent isnull           connect by a.parent = prior a.id)select *                              from tmp                            where leaf = (select leaf from tmp where id =50);

這裡使用兩個技巧，一個是使用了level來標識每個節點在表中的層級，還有就是使用with文法類比出了一張帶有層級的暫存資料表。

8)、查詢一個節點的父節點的的兄弟節點（伯父與叔父）。          

1234567891011121314151617181920212223

with tmp as(    select tb_menu.*, level lev    from tb_menu    start with parent isnull    connect by parent = prior id)    select b.*from tmp b,(select *            from tmp            where id =21and lev =2) awhere b.lev =1 union all select *from tmpwhere parent = (select distinct x.id                from tmp x, --祖父                     tmp y, --父親                     (select *                      from tmp                      where id =21and lev >2) z --兒子                where y.id = z.parent and x.id = y.parent);

這裡查詢分成以下幾步。
首先，將第7個一樣，將全表都使用暫存資料表加上層級；
其次，根據層級來判斷有幾種類型，以上文中舉的例子來說，有三種情況：
（1）當前節點為頂級節點，即查詢出來的lev值為1，那麼它沒有上級節點，不予考慮。
（2）當前節點為2級節點，查詢出來的lev值為2，那麼就只要保證lev層級為1的就是其上級節點的兄弟節點。
（3）其它情況就是3以及以上層級，那麼就要選查詢出來其上級的上級節點（祖父），再來判斷祖父的下級節點都是屬於該節點的上級節點的兄弟節點。
最後，就是使用union將查詢出來的結果進行結合起來，形成結果集。

9)、查詢一個節點的父節點的同級節點（族叔）。
這個其實跟第7種情況是相同的。

12345678

with tmp as(      select a.*, level leaf             from tb_menu a                     start with a.parent isnull           connect by a.parent = prior a.id)select *                              from tmp                            where leaf = (select leaf from tmp where id =6) -1;

基本上，常見的查詢在裡面了，不常見的也有部分了。其中，查詢的內容都是節點的基本資料，都是資料表中的基本欄位，但是在樹查詢中還有些特殊需求，是對查詢資料進行了處理的，常見的包括列出樹路徑等。

補充一個概念，對於資料庫來說，根節點並不一定是在資料庫中設計的頂級節點，對於資料庫來說，根節點就是start with開始的地方。

下面列出的是一些與樹相關的特殊需求。

10)、名稱要列出名稱全部路徑。
這裡常見的有兩種情況，一種是從頂級列出，直到當前節點的名稱（或者其它屬性）；一種是從當前節點列出，直到頂級節點的名稱（或其它屬性）。舉地址為例：國內的習慣是從省開始、到市、到縣、到居委會的，而國外的習慣正好相反（老師說的，還沒接過國外的郵件，誰能寄個瞅瞅  ）。
從頂部開始：

12345

select sys_connect_by_path (title,‘/‘)from tb_menuwhere id =50start with parent isnullconnect by parent = prior id;

從當前節點開始：

1234	select sys_connect_by_path (title,‘/‘)from tb_menustart with id =50connect by prior parent = id;

在這裡我又不得不放個牢騷了。oracle只提供了一個sys_connect_by_path函數，卻忘了字串的串連的順序。在上面的例子中，第一個sql是從根節點開始遍曆，而第二個sql是直接找到當前節點，從效率上來說已經是千差萬別，更關鍵的是第一個sql只能選擇一個節點，而第二個sql卻是遍曆出了一顆樹來。再次ps一下。

sys_connect_by_path函數就是從start with開始的地方開始遍曆，並記下其遍曆到的節點，start with開始的地方被視為根節點，將遍曆到的路徑根據函數中的分隔字元，組成一個新的字串，這個功能還是很強大的。

11)、列出當前節點的根節點。
在前面說過，根節點就是start with開始的地方。

1234	select connect_by_root title, tb_menu.*from tb_menustart with id =50connect by prior parent = id;

connect_by_root函數用來列的前面，記錄的是當前節點的根節點的內容。

12)、列出當前節點是否為葉子。
這個比較常見，尤其在動態目錄中，在查出的內容是否還有下級節點時，這個函數是很適用的。

1234	select connect_by_isleaf, tb_menu.*from tb_menustart with parent isnullconnect by parent = prior id;

connect_by_isleaf函數用來判斷當前節點是否包含下級節點，如果包含的話，說明不是葉子節點，這裡返回0；反之，如果不包含下級節點，這裡返回1。

至此，oracle樹型查詢基本上講完了，以上的例子中的資料是使用到做過的項目中的資料，因為裡面的內容可能不好理解，所以就全部用一些新的例子來進行闡述。以上所有sql都在本機上測試通過，也都能實現相應的功能，但是並不能保證是解決這類問題的最優方案（如第8條明顯寫成預存程序會更好）.

Oracle 樹操作(select…start with…connect by…prior)