mysql 上级组织参数值_MySQL - 递归查询部门组织

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;