欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

Oracle遞歸查詢樹形數(shù)據(jù)實例代碼

 更新時間:2022年11月09日 10:35:43   作者:不會叫的狼  
Oracle數(shù)據(jù)庫中常要處理一些父子關(guān)系的記錄,在OLTP中要用得多一些,oracle提供了遞歸查詢可以很容易的滿足這個需求,下面這篇文章主要給大家介紹了關(guān)于Oracle遞歸查詢樹形數(shù)據(jù)的相關(guān)資料,需要的朋友可以參考下

概述

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

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

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, '中國');
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遞歸查詢

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

常用的select項:

LEVEL:級別
connect_by_root:根節(jié)點
sys_connect_by_path:遞歸路徑

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

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

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

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

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

select t.*,LEVEL
from area_test t
start 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='河南省';

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

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

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

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

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

select d.*,
	   connect_by_root(d.id) rootid,
	   connect_by_root(d.name) rootname
from area_test d
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;

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

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

參考文章:Oracle遞歸查詢

總結(jié)

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

相關(guān)文章

最新評論