mysql-递归查询输出部门组织架构

0. 背景

最近接触到的业务中需要通过mysql查询部门的组织架构层级关系，最一开始的思路是想通过自定义函数来完成，但是查询效率真的是“感人”。又另辟蹊径找到mysql的递归查询，最终很好的实现了业务诉求。回过头来记录一下。

1. 公用表表达式-CTE

公用表表达式是一个命名的临时结果集，不作为对象存储，只在执行期间存在。

CTE基本语法：

with cite_name as
(
    query
)
select *
from cite_name
;

2. CTE的递归查询

CTE的递归查询就是需要不断的去“引用”CTE本身。

基本语法：

with recursive cte_name as
(
    initial_query     -- anchor member
    union all
    recursive_query -- 引用CTE名称的递归成员
)
select * 
from cte_name
;

CTE递归查询主要有三部分：

① 初始查询，形成递归查询的初始结果集
② 递归查询部分，引用CTE名称的查询
③ 终止条件，确保查询在不满足条件时终止

WITH RECURSIVE cte_count (n) 
AS (
      SELECT 1
      UNION ALL
      SELECT n + 1 
      FROM cte_count 
      WHERE n < 3
    )
SELECT n 
FROM cte_count;

输出结果：

+---+
| n |
+---+
| 1 |
| 2 |
| 3 |
+---+
3 rows in set (0.01 sec)

注意：

递归部分，不能使用聚合函数，distinct ，group by 子句， order by 子句，limit子句。

3. 测试数据

drop table if exists recursive_test;
create table recursive_test(
id          int(8) AUTO_INCREMENT PRIMARY KEY   comment '自增主键',
dept_id            int(8)            comment '部门ID',
dept_name        varchar(40)            comment '部门名称',
parent_id               int(8)            comment '父级部门ID'
)engine=InnoDB DEFAULT charset=utf8mb4 comment '部门表'
;

insert into recursive_test(dept_id, dept_name, parent_id) values(1, '宇宙总公司', 0);
insert into recursive_test(dept_id, dept_name, parent_id) values(2, '湖北分公司', 1);
insert into recursive_test(dept_id, dept_name, parent_id) values(3, '北京分公司', 1);
insert into recursive_test(dept_id, dept_name, parent_id) values(4, '上海分公司', 1);
insert into recursive_test(dept_id, dept_name, parent_id) values(5, '杭州分公司', 1);
insert into recursive_test(dept_id, dept_name, parent_id) values(123456, '上海黄浦办事处', 4);
insert into recursive_test(dept_id, dept_name, parent_id) values(123457, '上海长宁办事处', 4);
insert into recursive_test(dept_id, dept_name, parent_id) values(123458, '上海杨浦办事处', 4);
insert into recursive_test(dept_id, dept_name, parent_id) values(123459, '上海静安办事处', 4);
insert into recursive_test(dept_id, dept_name, parent_id) values(1234567, '上海黄浦办事处-1', 123456);
insert into recursive_test(dept_id, dept_name, parent_id) values(1234568, '上海黄浦办事处-2', 123456);
insert into recursive_test(dept_id, dept_name, parent_id) values(1234569, '上海黄浦办事处-3', 123456);

表结构如下：

从recursive_test表结构中较难对这些部门的层级关系有清晰的认识，对表中的组织层级关系绘制出来就一目了然了：

宇宙总公司

上海分公司

湖北分公司

北京分公司

杭州分公司

上海长宁办事处

上海杨浦办事处

上海黄浦办事处

上海静安办事处

上海黄浦办事处-1

上海黄浦办事处-2

上海黄浦办事处-3

如果我们想将宇宙总公司——>上海分公司——>上海黄浦办事处——>上海黄浦办事处-1 这些组织层级关系串起来，在mysql中用一个字段来表示该如何处理呢？

4. 解决思路

① 通过left join去关联表实现

当组织架构的层级是固定且层级数较少（建议最多3层）可以通过该方式实现，一旦层级数过多，这种方式不再适用。

② 自定义函数

通过自定义函数，可以先获取当前部门所在链路上的上级部门ID，然后对每个部门ID进行部门名称匹配（可在自定义函数中直接输出部门名称，自行尝试）

SET GLOBAL log_bin_trust_function_creators = 1;
 
delimiter $$
 
drop function if exists find_parent_list_test $$
 
create function find_parent_list_test(son_id  varchar(20)) returns varchar(256)
 
begin
 
    declare parent_list varchar(256);
       
    select group_concat(a.departmentid) into parent_list
    from
        (
        select 
        (select @dept_id := parent_id from recursive_test where dept_id=@dept_id) as departmentid
        from recursive_test as a, (select @dept_id := son_id) as b
        )as a
    where a.departmentid is not null;
    
    return parent_list;
 
end
 
$$
 
delimiter ;

但是有个较大的缺点是自定义函数的查询效率真的很低很低！！！因测试样本数据较少，运行效率还OK。

③ 递归查询

根据根部门ID信息，遍历出所有的组织链路，通过 concat函数拼接起来。整体查询效率和便捷性还是很不错的。

with recursive party_detail as
( 
select dept_id, dept_name, dept_name as dept_structure
from recursive_test
where dept_id=1     -- 根部门ID
 
union
 
select a.dept_id ,a.dept_name, concat(b.dept_structure, ' > ', a.dept_name) as dept_structure
from party_detail as b 
join recursive_test as a on b.dept_id=a.parent_id
)
select *
from party_detail
;

参考来源：
https://www.begtut.com/mysql/mysql-recursive-cte.html

posted @ 2023-03-28 20:27 大大章鱼阅读(1046) 评论(0) 编辑收藏举报

刷新页面返回顶部

大大章鱼