概述

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

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

start with connect by prior 递归查询

1、数据准备

create table area_test(id number(10) not null,parent_idnumber(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.*,LEVELfrom area_test tstart with name ='郑州市'connect by prior id=parent_id

其实,如果单层结构,使用表自身连接也可以实现:

select * from area_test t1,area_test t2where t1.ID = t2.PARENT_ID and t1.NAME='郑州市'

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

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

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

2.2 查询所有父节点

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

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

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, namefrom area_testwhere name = '平顶山市'union allselect d.id, d.parent_id, d.namefrom tmp, area_test dwhere 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递归查询