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

在MySQL数据库中，要高效查询指定部门及其所有子部门下的所有员工，通常需要处理部门之间的层级关系。假设你的数据库结构如下：

• departments 表：存储部门信息，包含 id 和 parent_id 字段来表示层级关系。
• employees 表：存储员工信息，包含 department_id 字段来关联员工所在的部门。

1. 使用递归CTE（Common Table Expressions）

MySQL 8.0及以上版本支持递归CTE，可以方便地处理层级查询。

WITH RECURSIVE DepartmentHierarchy AS (
    -- 基础查询：选择指定部门
    SELECT id
    FROM departments
    WHERE id = ?  -- 这里替换为你要查询的部门ID

    UNION ALL

    -- 递归查询：选择所有子部门
    SELECT d.id
    FROM departments d
    INNER JOIN DepartmentHierarchy dh ON d.parent_id = dh.id
)
-- 查询所有员工
SELECT e.*
FROM employees e
INNER JOIN DepartmentHierarchy dh ON e.department_id = dh.id;

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

如果你的部门表使用了嵌套集模型（left 和 right 字段来表示层级关系），查询会更加高效。

假设 departments 表有 left 和 right 字段：

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

3. 使用路径枚举模型（Path Enumeration Model）

如果你的部门表使用了路径枚举模型（path 字段来存储部门路径），查询也可以非常高效。

假设 departments 表有 path 字段，例如 /1/2/3/：

SELECT e.*
FROM employees e
INNER JOIN departments d ON e.department_id = d.id
WHERE d.path LIKE CONCAT((SELECT path FROM departments WHERE id = ?), '%');

4. 使用闭包表（Closure Table）

闭包表是一种专门用于处理层级关系的表结构。假设你有一个 department_closure 表，存储了部门之间的所有层级关系。

SELECT e.*
FROM employees e
INNER JOIN department_closure dc ON e.department_id = dc.descendant_id
WHERE dc.ancestor_id = ?;  -- 这里替换为你要查询的部门ID

总结

• 递归CTE：适用于MySQL 8.0及以上版本，代码简洁，易于理解。
• 嵌套集模型：查询效率高，但维护复杂。
• 路径枚举模型：查询效率高，路径维护简单。
• 闭包表：查询效率高，维护复杂，适合频繁查询的场景。

根据你的数据库设计和需求选择合适的方法。如果部门层级不深且数据量不大，递归CTE是一个不错的选择。如果数据量大且层级深，嵌套集模型或闭包表可能更适合。