如何递归获取部门层级结构下的所有员工？

公司部门采用层级树状结构，支持下设多级子部门。现有三张核心数据表，结构如下：

数据表结构 #

employee表（员工表） #

employee
  - id, name, ..., position_id, department_id

department表（部门表） #

department
  - id, name, ..., child_id

departmentToChildDepartment表（部门与子部门多对多关联表） #

departmentToChildDepartment (many-2-many)
  - department_id, child_department_id

需求描述 #

输入指定部门ID，需要获取该部门本身及其所有层级子部门下的全部员工。举个例子：如果Production部门下设有Team A和Team B两个子部门，输入Production的部门ID后，需返回Production、Team A、Team B这三个部门里的所有员工。

现有SQL的问题 #

以下SQL语句无法满足需求：

SELECT * 
FROM employee 
WHERE department_id = <Production_Department_Id>

原因很简单：这条语句只能筛选出指定部门下的员工，无法递归获取所有子部门中的员工。

解决方案：递归CTE查询 #

利用SQL的递归公共表表达式（CTE），可以先递归获取指定部门的所有子部门ID，再关联员工表拿到目标数据。以MySQL 8.0+为例，示例代码如下：

WITH RECURSIVE dept_hierarchy AS (
    -- 第一步：选中目标根部门
    SELECT id
    FROM department
    WHERE id = <Production_Department_Id>
    UNION ALL
    -- 第二步：递归遍历所有子部门
    SELECT d.id
    FROM department d
    JOIN departmentToChildDepartment dcd ON d.id = dcd.child_department_id
    JOIN dept_hierarchy dh ON dcd.department_id = dh.id
)
-- 关联员工表，获取所有符合条件的员工
SELECT e.*
FROM employee e
JOIN dept_hierarchy dh ON e.department_id = dh.id;

代码说明 #

1. 锚点成员：先选中指定的根部门ID，作为递归的起点。
2. 递归成员：通过多对多关联表departmentToChildDepartment，不断遍历子部门，直到没有更深层级的子部门为止。
3. 最后将递归得到的所有部门ID与员工表关联，筛选出对应部门下的所有员工。

内容的提问来源于stack exchange，提问作者BAXMAY