oracle树形语句
oracle樹查詢的最重要的就是select…start with…connect by…prior語(yǔ)法了。依托于該語(yǔ)法，我們可以將一個(gè)表形結(jié)構(gòu)的以樹的順序列出來(lái)。在下面列述了oracle中樹型查詢的常用查詢方式以及經(jīng)常使用的與樹查詢相關(guān)的oracle特性函數(shù)等，在這里只涉及到一張表中的樹查詢方式而不涉及多表中的關(guān)聯(lián)等。
1、準(zhǔn)備測(cè)試表和測(cè)試數(shù)據(jù)
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 | --菜單目錄結(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); --一級(jí)菜單 insert into tb_menu(id, title, parent) values(6, '一級(jí)菜單6',1); insert into tb_menu(id, title, parent) values(7, '一級(jí)菜單7',1); insert into tb_menu(id, title, parent) values(8, '一級(jí)菜單8',1); insert into tb_menu(id, title, parent) values(9, '一級(jí)菜單9',2); insert into tb_menu(id, title, parent) values(10, '一級(jí)菜單10',2); insert into tb_menu(id, title, parent) values(11, '一級(jí)菜單11',2); insert into tb_menu(id, title, parent) values(12, '一級(jí)菜單12',3); insert into tb_menu(id, title, parent) values(13, '一級(jí)菜單13',3); insert into tb_menu(id, title, parent) values(14, '一級(jí)菜單14',3); insert into tb_menu(id, title, parent) values(15, '一級(jí)菜單15',4); insert into tb_menu(id, title, parent) values(16, '一級(jí)菜單16',4); insert into tb_menu(id, title, parent) values(17, '一級(jí)菜單17',4); insert into tb_menu(id, title, parent) values(18, '一級(jí)菜單18',5); insert into tb_menu(id, title, parent) values(19, '一級(jí)菜單19',5); insert into tb_menu(id, title, parent) values(20, '一級(jí)菜單20',5); --二級(jí)菜單 insert into tb_menu(id, title, parent) values(21, '二級(jí)菜單21',6); insert into tb_menu(id, title, parent) values(22, '二級(jí)菜單22',6); insert into tb_menu(id, title, parent) values(23, '二級(jí)菜單23',7); insert into tb_menu(id, title, parent) values(24, '二級(jí)菜單24',7); insert into tb_menu(id, title, parent) values(25, '二級(jí)菜單25',8); insert into tb_menu(id, title, parent) values(26, '二級(jí)菜單26',9); insert into tb_menu(id, title, parent) values(27, '二級(jí)菜單27',10); insert into tb_menu(id, title, parent) values(28, '二級(jí)菜單28',11); insert into tb_menu(id, title, parent) values(29, '二級(jí)菜單29',12); insert into tb_menu(id, title, parent) values(30, '二級(jí)菜單30',13); insert into tb_menu(id, title, parent) values(31, '二級(jí)菜單31',14); insert into tb_menu(id, title, parent) values(32, '二級(jí)菜單32',15); insert into tb_menu(id, title, parent) values(33, '二級(jí)菜單33',16); insert into tb_menu(id, title, parent) values(34, '二級(jí)菜單34',17); insert into tb_menu(id, title, parent) values(35, '二級(jí)菜單35',18); insert into tb_menu(id, title, parent) values(36, '二級(jí)菜單36',19); insert into tb_menu(id, title, parent) values(37, '二級(jí)菜單37',20); --三級(jí)菜單 insert into tb_menu(id, title, parent) values(38, '三級(jí)菜單38',21); insert into tb_menu(id, title, parent) values(39, '三級(jí)菜單39',22); insert into tb_menu(id, title, parent) values(40, '三級(jí)菜單40',23); insert into tb_menu(id, title, parent) values(41, '三級(jí)菜單41',24); insert into tb_menu(id, title, parent) values(42, '三級(jí)菜單42',25); insert into tb_menu(id, title, parent) values(43, '三級(jí)菜單43',26); insert into tb_menu(id, title, parent) values(44, '三級(jí)菜單44',27); insert into tb_menu(id, title, parent) values(45, '三級(jí)菜單45',28); insert into tb_menu(id, title, parent) values(46, '三級(jí)菜單46',28); insert into tb_menu(id, title, parent) values(47, '三級(jí)菜單47',29); insert into tb_menu(id, title, parent) values(48, '三級(jí)菜單48',30); insert into tb_menu(id, title, parent) values(49, '三級(jí)菜單49',31); insert into tb_menu(id, title, parent) values(50, '三級(jí)菜單50',31); commit; select * from tb_menu; |
parent字段存儲(chǔ)的是上級(jí)id，如果是頂級(jí)父節(jié)點(diǎn)，該parent為null(得補(bǔ)充一句，當(dāng)初的確是這樣設(shè)計(jì)的，不過現(xiàn)在知道，表中最好別有null記錄，這會(huì)引起全文掃描，建議改成0代替)。
2、樹操作
我們從最基本的操作，逐步列出樹查詢中常見的操作，所有查詢出來(lái)的節(jié)點(diǎn)以家族中的輩份作比方。
1)、查找樹中的所有頂級(jí)父節(jié)點(diǎn)（輩份最長(zhǎng)的人）。 假設(shè)這個(gè)樹是個(gè)目錄結(jié)構(gòu)，那么第一個(gè)操作總是找出所有的頂級(jí)節(jié)點(diǎn)，再根據(jù)該節(jié)點(diǎn)找到其下屬節(jié)點(diǎn)。
| 1 | select * from tb_menu m where m.parent is null; |
2)、查找一個(gè)節(jié)點(diǎn)的直屬子節(jié)點(diǎn)（所有兒子）。 如果查找的是直屬子類節(jié)點(diǎn)，也是不用用到樹型查詢的。
| 1 | select * from tb_menu m where m.parent=1; |
3)、查找一個(gè)節(jié)點(diǎn)的所有直屬子節(jié)點(diǎn)（所有后代）。
| 1 | select * from tb_menu m start with m.id=1 connect by m.parent=prior m.id; |
這個(gè)查找的是id為1的節(jié)點(diǎn)下的所有直屬子類節(jié)點(diǎn)，包括子輩的和孫子輩的所有直屬節(jié)點(diǎn)。
4)、查找一個(gè)節(jié)點(diǎn)的直屬父節(jié)點(diǎn)（父親）。 如果查找的是節(jié)點(diǎn)的直屬父節(jié)點(diǎn)，也是不用用到樹型查詢的。
| 1 2 3 4 | --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)、查找一個(gè)節(jié)點(diǎn)的所有直屬父節(jié)點(diǎn)（祖宗）。
| 1 | select * from tb_menu m start with m.id=38 connect by prior m.parent=m.id; |
這里查找的就是id為1的所有直屬父節(jié)點(diǎn)，打個(gè)比方就是找到一個(gè)人的父親、祖父等。但是值得注意的是這個(gè)查詢出來(lái)的結(jié)果的順序是先列出子類節(jié)點(diǎn)再列出父類節(jié)點(diǎn)，姑且認(rèn)為是個(gè)倒序吧。
上面列出兩個(gè)樹型查詢方式，第3條語(yǔ)句和第5條語(yǔ)句，這兩條語(yǔ)句之間的區(qū)別在于prior關(guān)鍵字的位置不同，所以決定了查詢的方式不同。 當(dāng)parent = prior id時(shí)，數(shù)據(jù)庫(kù)會(huì)根據(jù)當(dāng)前的id迭代出parent與該id相同的記錄，所以查詢的結(jié)果是迭代出了所有的子類記錄；而prior parent = id時(shí)，數(shù)據(jù)庫(kù)會(huì)跟據(jù)當(dāng)前的parent來(lái)迭代出與當(dāng)前的parent相同的id的記錄，所以查詢出來(lái)的結(jié)果就是所有的父類結(jié)果。
以下是一系列針對(duì)樹結(jié)構(gòu)的更深層次的查詢，這里的查詢不一定是最優(yōu)的查詢方式，或許只是其中的一種實(shí)現(xiàn)而已。
6)、查詢一個(gè)節(jié)點(diǎn)的兄弟節(jié)點(diǎn)（親兄弟）。
| 1 2 3 | --m.parent=m2.parent-->同一個(gè)父親 select * from tb_menu m where exists (select * from tb_menu m2 where m.parent=m2.parent and m2.id=6) |
7)、查詢與一個(gè)節(jié)點(diǎn)同級(jí)的節(jié)點(diǎn)（族兄弟）。?如果在表中設(shè)置了級(jí)別的字段，那么在做這類查詢時(shí)會(huì)很輕松，同一級(jí)別的就是與那個(gè)節(jié)點(diǎn)同級(jí)的，在這里列出不使用該字段時(shí)的實(shí)現(xiàn)!
| 1 2 3 4 5 6 7 8 | 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); |
這里使用兩個(gè)技巧，一個(gè)是使用了level來(lái)標(biāo)識(shí)每個(gè)節(jié)點(diǎn)在表中的級(jí)別，還有就是使用with語(yǔ)法模擬出了一張帶有級(jí)別的臨時(shí)表。
8)、查詢一個(gè)節(jié)點(diǎn)的父節(jié)點(diǎn)的的兄弟節(jié)點(diǎn)（伯父與叔父）。??????????
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | 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); |
這里查詢分成以下幾步。
首先，將第7個(gè)一樣，將全表都使用臨時(shí)表加上級(jí)別；
其次，根據(jù)級(jí)別來(lái)判斷有幾種類型，以上文中舉的例子來(lái)說，有三種情況：
（1）當(dāng)前節(jié)點(diǎn)為頂級(jí)節(jié)點(diǎn)，即查詢出來(lái)的lev值為1，那么它沒有上級(jí)節(jié)點(diǎn)，不予考慮。
（2）當(dāng)前節(jié)點(diǎn)為2級(jí)節(jié)點(diǎn)，查詢出來(lái)的lev值為2，那么就只要保證lev級(jí)別為1的就是其上級(jí)節(jié)點(diǎn)的兄弟節(jié)點(diǎn)。
（3）其它情況就是3以及以上級(jí)別，那么就要選查詢出來(lái)其上級(jí)的上級(jí)節(jié)點(diǎn)（祖父），再來(lái)判斷祖父的下級(jí)節(jié)點(diǎn)都是屬于該節(jié)點(diǎn)的上級(jí)節(jié)點(diǎn)的兄弟節(jié)點(diǎn)。
最后，就是使用union將查詢出來(lái)的結(jié)果進(jìn)行結(jié)合起來(lái)，形成結(jié)果集。
9)、查詢一個(gè)節(jié)點(diǎn)的父節(jié)點(diǎn)的同級(jí)節(jié)點(diǎn)（族叔）。
這個(gè)其實(shí)跟第7種情況是相同的。
| 1 2 3 4 5 6 7 8 | 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; |
基本上，常見的查詢?cè)诶锩媪?#xff0c;不常見的也有部分了。其中，查詢的內(nèi)容都是節(jié)點(diǎn)的基本信息，都是數(shù)據(jù)表中的基本字段，但是在樹查詢中還有些特殊需求，是對(duì)查詢數(shù)據(jù)進(jìn)行了處理的，常見的包括列出樹路徑等。
補(bǔ)充一個(gè)概念，對(duì)于數(shù)據(jù)庫(kù)來(lái)說，根節(jié)點(diǎn)并不一定是在數(shù)據(jù)庫(kù)中設(shè)計(jì)的頂級(jí)節(jié)點(diǎn)，對(duì)于數(shù)據(jù)庫(kù)來(lái)說，根節(jié)點(diǎn)就是start with開始的地方。
下面列出的是一些與樹相關(guān)的特殊需求。
10)、名稱要列出名稱全部路徑。
這里常見的有兩種情況，一種是從頂級(jí)列出，直到當(dāng)前節(jié)點(diǎn)的名稱（或者其它屬性）；一種是從當(dāng)前節(jié)點(diǎn)列出，直到頂級(jí)節(jié)點(diǎn)的名稱（或其它屬性）。舉地址為例：國(guó)內(nèi)的習(xí)慣是從省開始、到市、到縣、到居委會(huì)的，而國(guó)外的習(xí)慣正好相反（老師說的，還沒接過國(guó)外的郵件，誰(shuí)能寄個(gè)瞅瞅? ）。
從頂部開始：
| 1 2 3 4 5 | 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é)點(diǎn)開始：
| 1 2 3 4 | select sys_connect_by_path (title, '/') from tb_menu start with id = 50 connect by prior parent = id; |
在這里我又不得不放個(gè)牢騷了。oracle只提供了一個(gè)sys_connect_by_path函數(shù)，卻忘了字符串的連接的順序。在上面的例子中，第一個(gè)sql是從根節(jié)點(diǎn)開始遍歷，而第二個(gè)sql是直接找到當(dāng)前節(jié)點(diǎn)，從效率上來(lái)說已經(jīng)是千差萬(wàn)別，更關(guān)鍵的是第一個(gè)sql只能選擇一個(gè)節(jié)點(diǎn)，而第二個(gè)sql卻是遍歷出了一顆樹來(lái)。再次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開始的地方。
| 1 2 3 4 | select connect_by_root title, tb_menu.* from tb_menu start with id = 50 connect by prior parent = id; |
connect_by_root函數(shù)用來(lái)列的前面，記錄的是當(dāng)前節(jié)點(diǎn)的根節(jié)點(diǎn)的內(nèi)容。
12)、列出當(dāng)前節(jié)點(diǎn)是否為葉子。
這個(gè)比較常見，尤其在動(dòng)態(tài)目錄中，在查出的內(nèi)容是否還有下級(jí)節(jié)點(diǎn)時(shí)，這個(gè)函數(shù)是很適用的。
| 1 2 3 4 | select connect_by_isleaf, tb_menu.* from tb_menu start with parent is null connect by parent = prior id; |
connect_by_isleaf函數(shù)用來(lái)判斷當(dāng)前節(jié)點(diǎn)是否包含下級(jí)節(jié)點(diǎn)，如果包含的話，說明不是葉子節(jié)點(diǎn)，這里返回0；反之，如果不包含下級(jí)節(jié)點(diǎn)，這里返回1。
至此，oracle樹型查詢基本上講完了，以上的例子中的數(shù)據(jù)是使用到做過的項(xiàng)目中的數(shù)據(jù)，因?yàn)槔锩娴膬?nèi)容可能不好理解，所以就全部用一些新的例子來(lái)進(jìn)行闡述。以上所有sql都在本機(jī)上測(cè)試通過，也都能實(shí)現(xiàn)相應(yīng)的功能，但是并不能保證是解決這類問題的最優(yōu)方案（如第8條明顯寫成存儲(chǔ)過程會(huì)更好）.
轉(zhuǎn)載于:https://www.cnblogs.com/zourui4271/p/5039078.html
總結(jié)
以上是生活随笔為你收集整理的oracle树形语句的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 系统文件操作函数
- 下一篇： 第三个spring冲刺第5、6、7天（三