插件窝 干货文章 MySQL数据库中如何高效查询指定部门及其所有子部门下的所有员工?

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

递归 层级 部门 departments 503    来源:    2025-03-14

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

  • departments 表:存储部门信息,包含 idparent_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)

如果你的部门表使用了嵌套集模型(leftright 字段来表示层级关系),查询会更加高效。

假设 departments 表有 leftright 字段:

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是一个不错的选择。如果数据量大且层级深,嵌套集模型或闭包表可能更适合。