日本免费高清视频-国产福利视频导航-黄色在线播放国产-天天操天天操天天操天天操|www.shdianci.com

學(xué)無先后，達(dá)者為師

網(wǎng)站首頁 編程語言 正文

Oracle遞歸查詢簡(jiǎn)單示例_oracle

作者：michael-linux ? 更新時(shí)間： 2022-12-09 編程語言

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, '中國');
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, '新鄉(xiāng)市');
insert into area_test (ID, PARENT_ID, NAME) values (115, 11, '南陽市');
insert into area_test (ID, PARENT_ID, NAME) values (121, 12, '朝陽區(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遞歸查詢

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

select *
from area_test
start with name ='鄭州市'
connect by prior id=parent_id

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

select t.*,level
from area_test t
start with name ='鄭州市'
connect by prior t.parent_id=t.id
order by level asc;

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兩列誰放在“=”前都無所謂，關(guān)鍵是prior跟誰在一起。
order by 子句：排序。

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

select d.*,
	   connect_by_root(d.id) rootid,
	   connect_by_root(d.name) rootname
from area_test d
where name='二七區(qū)'
start with d.parent_id IS NULL
connect by prior d.id=d.parent_id

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

select id, parent_id, name, sys_connect_by_path(name, '->') namepath, level
from area_test
start 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;

補(bǔ)充：實(shí)例

我們稱表中的數(shù)據(jù)存在父子關(guān)系，通過列與列來關(guān)聯(lián)的，這樣的數(shù)據(jù)結(jié)構(gòu)為樹結(jié)構(gòu)。

現(xiàn)在有一個(gè)menu表，字段有id，pid，title三個(gè)。

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

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

將PRIOR關(guān)鍵字放在m.id前面，意思就是查詢pid是當(dāng)前記錄id的記錄，如此順延找到所有子節(jié)點(diǎn)。

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

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

總結(jié)

原文鏈接：https://blog.csdn.net/Michael_lcf/article/details/124433725

欄目分類
最近更新