Oracle遞歸查詢簡單示例

目錄

• 1 數據準備
• 2 start with connect by prior遞歸查詢
• 2.1 查詢所有子節點
• 2.2 查詢所有父節點
• 2.3 查詢指定節點的，根節點
• 2.4 查詢巴中市下行政組織遞歸路徑
• 3 with遞歸查詢
• 3.1 with遞歸子類
• 3.2 遞歸父類
• 補充：實例
• 總結

1 數據準備

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, "中國");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, "洛陽市");insert into area_test (ID, PARENT_ID, NAME) values (114, 11, "新鄉市");insert into area_test (ID, PARENT_ID, NAME) values (115, 11, "南陽市");insert into area_test (ID, PARENT_ID, NAME) values (121, 12, "朝陽區");insert into area_test (ID, PARENT_ID, NAME) values (122, 12, "昌平區");insert into area_test (ID, PARENT_ID, NAME) values (1111, 111, "二七區");insert into area_test (ID, PARENT_ID, NAME) values (1112, 111, "中原區");insert into area_test (ID, PARENT_ID, NAME) values (1113, 111, "新鄭市");insert into area_test (ID, PARENT_ID, NAME) values (1114, 111, "經開區");insert into area_test (ID, PARENT_ID, NAME) values (1115, 111, "金水區");insert into area_test (ID, PARENT_ID, NAME) values (1121, 112, "湛河區");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, "尚店鎮");

2 start with connect by prior遞歸查詢

2.1 查詢所有子節點

select *from area_teststart with name ="鄭州市"connect by prior id=parent_id

2.2 查詢所有父節點

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

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

2.3 查詢指定節點的，根節點

select d.*,	   connect_by_root(d.id) rootid,	   connect_by_root(d.name) rootnamefrom area_test dwhere name="二七區"start 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 = "二七區"   union all   select d.id, d.parent_id, d.name   from tmp, area_test d   where tmp.parent_id = d.id   )select * from tmp;

補充：實例

我們稱表中的數據存在父子關系，通過列與列來關聯的，這樣的數據結構為樹結構。

現在有一個menu表，字段有id，pid，title三個。

查詢菜單id為10的所有子菜單。

SELECT * FROM tb_menu m START WITH m.id=10 CONNECT BY m.pid=PRIOR m.id;

將PRIOR關鍵字放在m.id前面，意思就是查詢pid是當前記錄id的記錄，如此順延找到所有子節點。

查詢菜單id為40的所有父菜單。

SELECT * FROM tb_menu m START WITH m.id=40 CONNECT BY PRIOR m.pid= m.id ORDER BY ID;

總結

到此這篇關于Oracle遞歸查詢的文章就介紹到這了,更多相關Oracle遞歸查詢內容請搜索以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持！