亚洲免费在线-亚洲免费在线播放-亚洲免费在线观看-亚洲免费在线观看视频-亚洲免费在线看-亚洲免费在线视频

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

系統 1948 0

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

1、準備測試表和測試數據

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
--菜單目錄結構表
create table tb_menu(
   id     number(10) not null , --主鍵id
   title  varchar2(50), --標題
   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,如果是頂級父節點,該parent為null(得補充一句,當初的確是這樣設計的,不過現在知道,表中最好別有null記錄,這會引起全文掃描,建議改成0代替)。

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

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

1
select * from tb_menu m where m.parent is null ;

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

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

3)、查找一個節點的所有直屬子節點(所有后代)。

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

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

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

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)、查找一個節點的所有直屬父節點(祖宗)。

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

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

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

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

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

1
2
3
--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 )

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 = 50 );

這里使用兩個技巧,一個是使用了level來標識每個節點在表中的級別,還有就是使用with語法模擬出了一張帶有級別的臨時表。

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

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個一樣,將全表都使用臨時表加上級別; 其次,根據級別來判斷有幾種類型,以上文中舉的例子來說,有三種情況: (1)當前節點為頂級節點,即查詢出來的lev值為1,那么它沒有上級節點,不予考慮。 (2)當前節點為2級節點,查詢出來的lev值為2,那么就只要保證lev級別為1的就是其上級節點的兄弟節點。 (3)其它情況就是3以及以上級別,那么就要選查詢出來其上級的上級節點(祖父),再來判斷祖父的下級節點都是屬于該節點的上級節點的兄弟節點。 最后,就是使用union將查詢出來的結果進行結合起來,形成結果集。

9)、查詢一個節點的父節點的同級節點(族叔)。 這個其實跟第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 ;

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

補充一個概念,對于數據庫來說,根節點并不一定是在數據庫中設計的頂級節點,對于數據庫來說,根節點就是start with開始的地方。

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

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

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;

從當前節點開始:

1
2
3
4
select sys_connect_by_path (title, '/' )
from tb_menu
start with id = 50
connect 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開始的地方。

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函數用來列的前面,記錄的是當前節點的根節點的內容。

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

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函數用來判斷當前節點是否包含下級節點,如果包含的話,說明不是葉子節點,這里返回0;反之,如果不包含下級節點,這里返回1。

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

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


更多文章、技術交流、商務合作、聯系博主

微信掃碼或搜索:z360901061

微信掃一掃加我為好友

QQ號聯系: 360901061

您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。

【本文對您有幫助就好】

您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描上面二維碼支持博主2元、5元、10元、自定義金額等您想捐的金額吧,站長會非常 感謝您的哦!!!

發表我的評論
最新評論 總共0條評論
主站蜘蛛池模板: sihu永久在线播放地址 | 911精品国产91久久久久 | 国产成人毛片亚洲精品不卡 | 欧美一级毛片免费看 | 欧美亚洲精品在线 | 夜夜精品视频一区二区 | 精品四虎免费观看国产高清午夜 | 福利在线影院 | 九色九色九色在线综合888 | 亚洲视频一区在线播放 | 欧美日韩国产一区二区三区 | 狠狠色丁香婷婷久久 | 国产欧美二区三区 | 久99久热只有精品国产99 | 日本成本人观看免费视频fc2 | 毛片大片 | 精品久久久久久影院免费 | 成人亚洲天堂 | 日本边添边爱边做视频 | 日本不卡在线视频 | 亚洲免费在线播放 | 狠狠操天天 | 欧美美女一区二区三区 | 99热久久精品国产66 | 狠狠色噜噜狠狠狠狠色综合网 | 日日噜噜夜夜躁躁狠狠 | 婷婷色基地 | 国产亚洲精品xxx | 久热国产精品视频 | 欧美aⅴ| 亚洲天堂久久精品 | 五月激情丁香婷婷综合第九 | 麻豆69 | 国产极品嫩模在线观看91精品 | 夜夜操狠狠操 | 日韩精品中文字幕在线 | 亚洲视频毛片 | 波多野结衣亚洲一区 | 天天色天天干天天 | 97久久国产一区二区三区四区 | 日韩一区二区三区免费视频 |