MySQL数据库中如何高效查询指定部门及其所有子部门下的所有员工？

在MySQL数据库中，要高效查询指定部门及其所有子部门下的所有员工，通常需要处理树形结构的部门数据。假设你的部门表（departments）和员工表（employees）结构如下：

• departments 表：

  • id：部门ID
  • name：部门名称
  • parent_id：父部门ID（如果为顶级部门，则为NULL）

• employees 表：

  • id：员工ID
  • name：员工名称
  • department_id：所属部门ID

1. 使用递归CTE（Common Table Expressions）查询所有子部门

MySQL 8.0及以上版本支持递归CTE，可以用来查询指定部门及其所有子部门。

WITH RECURSIVE sub_departments AS (
    SELECT id, name, parent_id
    FROM departments
    WHERE id = ?  -- 指定部门的ID
    UNION ALL
    SELECT d.id, d.name, d.parent_id
    FROM departments d
    INNER JOIN sub_departments sd ON d.parent_id = sd.id
)
SELECT e.*
FROM employees e
JOIN sub_departments sd ON e.department_id = sd.id;

2. 使用闭包表（Closure Table）设计

如果部门层级结构非常复杂且需要频繁查询，可以考虑使用闭包表（Closure Table）来优化查询性能。闭包表是一种专门用于处理树形结构的设计模式。

闭包表结构

• department_closure 表：
  • ancestor：祖先部门ID
  • descendant：后代部门ID
  • depth：层级深度（可选）

查询所有子部门

SELECT e.*
FROM employees e
JOIN department_closure dc ON e.department_id = dc.descendant
WHERE dc.ancestor = ?;  -- 指定部门的ID

3. 使用路径枚举（Path Enumeration）设计

如果部门层级结构相对简单，可以使用路径枚举（Path Enumeration）来存储部门路径。

路径枚举表结构

• departments 表：
  • id：部门ID
  • name：部门名称
  • path：部门路径（例如：1/2/3 表示部门3的父部门是2，2的父部门是1）

查询所有子部门

SELECT e.*
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE d.path LIKE ?;  -- 例如：'1/%' 表示部门1及其所有子部门

4. 使用嵌套集模型（Nested Set Model）

嵌套集模型是另一种处理树形结构的方法，适合频繁查询但较少更新的场景。

嵌套集表结构

• departments 表：
  • id：部门ID
  • name：部门名称
  • lft：左值
  • rgt：右值

查询所有子部门

SELECT e.*
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE d.lft BETWEEN (SELECT lft FROM departments WHERE id = ?)
                AND (SELECT rgt FROM departments WHERE id = ?);

总结

• 递归CTE：适用于MySQL 8.0及以上版本，简单易用，适合层级不深的部门结构。
• 闭包表：适合层级复杂且频繁查询的场景，查询性能较好，但需要额外的存储空间。
• 路径枚举：适合层级较浅且结构简单的场景，查询简单但更新复杂。
• 嵌套集模型：适合频繁查询但较少更新的场景，查询性能好但更新复杂。

根据你的具体需求和数据库版本选择合适的方案。