目錄

• 概述
• 1、數(shù)據(jù)準(zhǔn)備
• 2 start with connect by prior遞歸查詢
• 2.1 查詢所有子節(jié)點(diǎn)
• 2.2 查詢所有父節(jié)點(diǎn)
• 2.3 查詢指定節(jié)點(diǎn)的根節(jié)點(diǎn)
• 2.4 查詢下行政組織遞歸路徑
• 3 with遞歸查詢
• 3.1 with遞歸子類
• 3.2 遞歸父類
• 4 MySQL 遞歸查找樹形結(jié)構(gòu)
• 總結(jié)

概述

實(shí)際生活有很多樹形結(jié)構(gòu)的數(shù)據(jù)，比如公司分為多個(gè)部門，部門下分為多個(gè)組，組下分為多個(gè)員工；省市縣的歸屬；頁(yè)面菜單欄等等。

如果想查詢某個(gè)節(jié)點(diǎn)的父節(jié)點(diǎn)或者子節(jié)點(diǎn)，一般通過(guò)表自身連接完成，但如果該節(jié)點(diǎn)的子節(jié)點(diǎn)還有多層結(jié)構(gòu)，就需要使用遞歸調(diào)用。但如果數(shù)據(jù)量特別大，遞歸的次數(shù)指數(shù)級(jí)上升，而且查詢數(shù)據(jù)庫(kù)的次數(shù)也指數(shù)級(jí)上升，導(dǎo)致程序和數(shù)據(jù)庫(kù)壓力劇增，查詢時(shí)間特別長(zhǎng)。那數(shù)據(jù)庫(kù)有沒(méi)有遞歸查詢語(yǔ)句呢？答案是肯定的。

start with connect by prior 遞歸查詢

1、數(shù)據(jù)準(zhǔn)備

create table area_test(  id number(10) not null,  parent_id  number(10),  name       varchar2(255) not null);alter table area_test add (constraint district_pk primary key (id));insert into area_test (ID, PARENT_ID, NAME) values (1, null, "中國(guó)");insert into area_test (ID, PARENT_ID, NAME) values (11, 1, "河南省"); insert into area_test (ID, PARENT_ID, NAME) values (12, 1, "北京市");insert into area_test (ID, PARENT_ID, NAME) values (111, 11, "鄭州市");insert into area_test (ID, PARENT_ID, NAME) values (112, 11, "平頂山市");insert into area_test (ID, PARENT_ID, NAME) values (113, 11, "洛陽(yáng)市");insert into area_test (ID, PARENT_ID, NAME) values (114, 11, "新鄉(xiāng)市");insert into area_test (ID, PARENT_ID, NAME) values (115, 11, "南陽(yáng)市");insert into area_test (ID, PARENT_ID, NAME) values (121, 12, "朝陽(yáng)區(qū)");insert into area_test (ID, PARENT_ID, NAME) values (122, 12, "昌平區(qū)");insert into area_test (ID, PARENT_ID, NAME) values (1111, 111, "二七區(qū)");insert into area_test (ID, PARENT_ID, NAME) values (1112, 111, "中原區(qū)");insert into area_test (ID, PARENT_ID, NAME) values (1113, 111, "新鄭市");insert into area_test (ID, PARENT_ID, NAME) values (1114, 111, "經(jīng)開區(qū)");insert into area_test (ID, PARENT_ID, NAME) values (1115, 111, "金水區(qū)");insert into area_test (ID, PARENT_ID, NAME) values (1121, 112, "湛河區(qū)");insert into area_test (ID, PARENT_ID, NAME) values (1122, 112, "舞鋼市");insert into area_test (ID, PARENT_ID, NAME) values (1123, 112, "寶豐市");insert into area_test (ID, PARENT_ID, NAME) values (11221, 1122, "尚店鎮(zhèn)");

2 start with connect by prior遞歸查詢

• start with 子句：遍歷起始條件。如果要查父結(jié)點(diǎn)，這里可以用子結(jié)點(diǎn)的列，反之亦然。
• connect by 子句：連接條件。prior 跟父節(jié)點(diǎn)列parentid放在一起，就是往父結(jié)點(diǎn)方向遍歷；prior 跟子結(jié)點(diǎn)列subid放在一起，則往葉子結(jié)點(diǎn)方向遍歷。parent_id、id兩列誰(shuí)放在 “=” 前都無(wú)所謂，關(guān)鍵是prior跟誰(shuí)在一起。
• order by 子句：排序。

常用的select項(xiàng)：

LEVEL：級(jí)別
connect_by_root：根節(jié)點(diǎn)
sys_connect_by_path：遞歸路徑

2.1 查詢所有子節(jié)點(diǎn)

select t.*,LEVELfrom area_test tstart with name ="鄭州市"connect by prior id=parent_id

其實(shí)，如果單層結(jié)構(gòu)，使用表自身連接也可以實(shí)現(xiàn)：

select * from area_test t1,area_test t2 where t1.PARENT_ID = t2.ID and t2.name="鄭州市";

當(dāng)查詢節(jié)點(diǎn)下有多層數(shù)據(jù)：

select t.*,LEVELfrom area_test tstart with name ="河南省"connect by prior id=parent_id

select * from area_test t1,area_test t2 where t1.PARENT_ID = t2.ID and t2.name="河南省";

如果使用自身連接，也只能查到子一級(jí)節(jié)點(diǎn)的數(shù)據(jù)，需要遍歷子一級(jí)節(jié)點(diǎn)，遞歸查詢每個(gè)子一級(jí)節(jié)點(diǎn)下的子節(jié)點(diǎn)。明顯麻煩很多！！！

2.2 查詢所有父節(jié)點(diǎn)

select t.*,levelfrom area_test tstart with name ="鄭州市"connect by prior t.parent_id=t.idorder by level asc;

2.3 查詢指定節(jié)點(diǎn)的根節(jié)點(diǎn)

select d.*,	   connect_by_root(d.id) rootid,	   connect_by_root(d.name) rootnamefrom area_test dwhere name="二七區(qū)"start with d.parent_id IS NULLconnect by prior d.id=d.parent_id

select d.*,	   connect_by_root(d.id) rootid,	   connect_by_root(d.name) rootnamefrom area_test dstart with d.parent_id IS NULLconnect by prior d.id=d.parent_id

2.4 查詢下行政組織遞歸路徑

select id, parent_id, name, sys_connect_by_path(name, "->") namepath, levelfrom area_teststart with name = "平頂山市"connect by prior id = parent_id

3 with遞歸查詢

3.1 with遞歸子類

with tmp(id, parent_id, name) as (	select id, parent_id, name    from area_test    where name = "平頂山市"    union all    select d.id, d.parent_id, d.name    from tmp, area_test d    where tmp.id = d.parent_id   )select * from tmp;

3.2 遞歸父類

with tmp(id, parent_id, name) as  (   select id, parent_id, name   from area_test   where name = "二七區(qū)"   union all   select d.id, d.parent_id, d.name   from tmp, area_test d   where tmp.parent_id = d.id   )select * from tmp;

4 MySQL 遞歸查找樹形結(jié)構(gòu)

參考文章：MySQL 遞歸查找樹形結(jié)構(gòu)，這個(gè)方法太實(shí)用了

參考文章：Oracle遞歸查詢

總結(jié)

到此這篇關(guān)于Oracle遞歸查詢樹形數(shù)據(jù)的文章就介紹到這了,更多相關(guān)Oracle遞歸查詢樹形數(shù)據(jù)內(nèi)容請(qǐng)搜索以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持！