无限下钻级联部门数据查询SQL

需求背景：项目要求实现自定义的分级部门，且可灵活往下扩展子级。

这里以军队的部门划分为例：司令 - 军长 - 师长 - 旅长 - 团长 - 营长 - 连长 - 排长

• 指定用户所属部门为司令，可以查询到所有下钻子级的部门信息；
• 如果指定所属部门为营长，则可查询到营长，连长，排长等信息。

1. 创建表 k_dept

CREATE TABLE `k_dept` (
  `id` int NOT NULL AUTO_INCREMENT,
  `parent_id` int NOT NULL COMMENT '父级部门id',
  `name` varchar(255) NOT NULL COMMENT '部门名称',
  `desc` varchar(255) DEFAULT NULL COMMENT '部门描述',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb3;

2. 插入数据

insert into k_dept (id, parent_id, name) VALUES (1, 0, '司令');
insert into k_dept (id, parent_id, name) VALUES (2, 1, '军长');
insert into k_dept (id, parent_id, name) VALUES (3, 2, '师长A');
insert into k_dept (id, parent_id, name) VALUES (4, 2, '师长B');
insert into k_dept (id, parent_id, name) VALUES (5, 3, '旅长');
insert into k_dept (id, parent_id, name) VALUES (6, 5, '团长');
insert into k_dept (id, parent_id, name) VALUES (7, 6, '营长A');
insert into k_dept (id, parent_id, name) VALUES (8, 6, '营长B');
insert into k_dept (id, parent_id, name) VALUES (9, 7, '连长A');
insert into k_dept (id, parent_id, name) VALUES (10, 8, '连长B');
insert into k_dept (id, parent_id, name) VALUES (11, 8, '连长C');
insert into k_dept (id, parent_id, name) VALUES (12, 9, '排长A');
insert into k_dept (id, parent_id, name) VALUES (13, 11, '排长B');

3. 递归查询语句

/**
* 通过顶级部门查询所有下钻子级的部门数据
* 这里设置了 @id=1，即传入的条件为用户所属部门为司令
**/

SELECT
    T3.*
FROM
    (
        SELECT
            @id AS _ids,
            ( SELECT @id := GROUP_CONCAT( id ) FROM K_Dept WHERE FIND_IN_SET( Parent_id, @id ) ) AS T1
        FROM
            K_Dept,
            ( SELECT @id := 1 ) T4
        WHERE
            @id IS NOT NULL
    ) T2,
    K_Dept T3
WHERE
    FIND_IN_SET( T3.id, T2._ids )
ORDER BY id;

查询结果：

/**
* 通过任意中间子级部门查询其下钻子级的部门数据
* 这里设置了 @id=8，即传入的条件为用户所属部门为营长B
**/

SELECT
    T3.*
FROM
    (
        SELECT
            @id AS _ids,
            ( SELECT @id := GROUP_CONCAT( id ) FROM K_Dept WHERE FIND_IN_SET( Parent_id, @id ) ) AS T1
        FROM
            K_Dept,
            ( SELECT @id := 8 ) T4
        WHERE
            @id IS NOT NULL
    ) T2,
    K_Dept T3
WHERE
    FIND_IN_SET( T3.id, T2._ids )
ORDER BY id;

查询结果：