MySQL - 递归查询部门组织

Max.Bai

2019-11

 

0x00: 背景

项目需要查询某个人所属的部门，上级，上上上级部门，就是查到这个人所属的部门路径。

0x01: MySQL 代码

SELECT id, dept_name, `level`, parent_dept_id FROM ( SELECT @r AS _id, (SELECT @r := parent_dept_id FROM dept WHERE id = _id) AS parent_id, @l := @l + 1 AS lvl FROM (SELECT @r := 3478, @l := 0) vars, dept h WHERE @r <> 0 ) T1 JOIN dept T2 ON T1._id = T2.id ORDER BY `level`;

代码里面的3478 就是这个人所属的当前部门id，

查到结果：

0x02: 核心代码理解

SELECT @r AS _id, (SELECT @r := parent_dept_id FROM dept WHERE id = _id) AS parent_id, @l := @l + 1 AS lvl FROM (SELECT @r := 3478, @l := 0) vars, dept h WHERE @r <> 0

有2个变量

@r   就是部门id

@l   就是递归查询的层数

首先给变量赋值

(SELECT @r := 3478, @l := 0) vars,

@r<>0  就是递归结束的条件，不然一直递归下去了

select 里面的内容就是每次递归执行的内容，有3个部分：

1. 显示当前@r 变量的值，就是这里的当前部门id    @r AS _id,

2. 以当前id查到父id，显示父id，并把父id赋值给@r变量

(SELECT @r := parent_dept_id FROM dept WHERE id = _id) AS parent_id,

3. 层级加1 并显示当前层级  @l := @l + 1 AS lvl

from 里面是变量初始值，和部门表

where 里面是停止循环的条件，这里是当前部门id不等于0 的时候一直执行循环

 

0x03 查询所有部门的所有上级部门id

SELECT `dept_name`, id AS id, parent_dept_id AS PID, levels AS Level, CONCAT(paths,',',id) AS path FROM ( SELECT `dept_name`, id, parent_dept_id, @le := IF ( parent_dept_id = 0, 0, IF ( LOCATE( CONCAT( '|', parent_dept_id, ':' ), @pathlevel ) > 0, SUBSTRING_INDEX( SUBSTRING_INDEX( @pathlevel, CONCAT( '|', parent_dept_id, ':' ),- 1 ), '|', 1 ) + 1, @le + 1 ) ) levels, @pathlevel := CONCAT( @pathlevel, '|', id, ':', @le, '|' ) pathlevel, @pathnodes := IF ( parent_dept_id = 0, ',0', CONCAT_WS( ',', IF ( LOCATE( CONCAT( '|', parent_dept_id, ':' ), @pathall ) > 0, SUBSTRING_INDEX( SUBSTRING_INDEX( @pathall, CONCAT( '|', parent_dept_id, ':' ),- 1 ), '|', 1 ), @pathnodes ), parent_dept_id ) ) paths, @pathall := CONCAT( @pathall, '|', id, ':', @pathnodes, '|' ) pathall FROM dept, ( SELECT @le := 0, @pathlevel := '', @pathall := '', @pathnodes := '' ) vv ORDER BY parent_dept_id, id ) src ORDER BY parent_dept_id;