Oracle递归查询树形数据实例代码

目录

• 概述
• 1、数据准备
• 2 start with connect by prior递归查询
• 2.1 查询所有子节点
• 2.2 查询所有父节点
• 2.3 查询指定节点的根节点
• 2.4 查询下行政组织递归路径
• 3 with递归查询
• 3.1 with递归子类
• 3.2 递归父类
• 4 MySQL 递归查找树形结构
• 总结

概述

实际生活有很多树形结构的数据，比如公司分为多个部门，部门下分为多个组，组下分为多个员工；省市县的归属；页面菜单栏等等。

如果想查询某个节点的父节点或者子节点，一般通过表自身连接完成，但如果该节点的子节点还有多层结构，就需要使用递归调用。但如果数据量特别大，递归的次数指数级上升，而且查询数据库的次数也指数级上升，导致程序和数据库压力剧增，查询时间特别长。那数据库有没有递归查询语句呢？答案是肯定的。

start with connect by prior 递归查询

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递归查询

• start with 子句：遍历起始条件。如果要查父结点，这里可以用子结点的列，反之亦然。
• connect by 子句：连接条件。prior 跟父节点列parentid放在一起，就是往父结点方向遍历；prior 跟子结点列subid放在一起，则往叶子结点方向遍历。parent_id、id两列谁放在 “=” 前都无所谓，关键是prior跟谁在一起。
• order by 子句：排序。

常用的select项：

LEVEL：级别
connect_by_root：根节点
sys_connect_by_path：递归路径

2.1 查询所有子节点

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="郑州市";

当查询节点下有多层数据：

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="河南省";

如果使用自身连接，也只能查到子一级节点的数据，需要遍历子一级节点，递归查询每个子一级节点下的子节点。明显麻烦很多！！！

2.2 查询所有父节点

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

2.3 查询指定节点的根节点

select d.*,
	   connect_by_root(d.id) rootid,
	   connect_by_root(d.name) rootname
from area_test d
where name="二七区"
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 = "二七区"
   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 递归查找树形结构

参考文章：MySQL 递归查找树形结构，这个方法太实用了

参考文章：Oracle递归查询

总结

到此这篇关于Oracle递归查询树形数据的文章就介绍到这了,更多相关Oracle递归查询树形数据内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！