如何高效查询MySQL数据库中某个部门及其所有子部门下的所有员工，并避免重复？

要高效查询MySQL数据库中某个部门及其所有子部门下的所有员工，并避免重复，可以使用递归查询（Recursive CTE）和适当的JOIN操作。以下是一个示例SQL查询，假设你有两个表：departments 和 employees。

表结构假设

• departments 表：

  • id (部门ID)
  • name (部门名称)
  • parent_id (父部门ID，如果为顶级部门则为NULL)

• employees 表：

  • id (员工ID)
  • name (员工姓名)
  • department_id (所属部门ID)

查询步骤

1. 递归查询所有子部门：使用递归CTE查询某个部门及其所有子部门。
2. 查询员工：将递归查询的结果与employees表进行JOIN，获取所有员工。

SQL查询示例

WITH RECURSIVE SubDepartments 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 SubDepartments sd ON d.parent_id = sd.id
)
-- 查询所有子部门的员工
SELECT DISTINCT e.id, e.name, e.department_id
FROM employees e
INNER JOIN SubDepartments sd ON e.department_id = sd.id;

解释

1. WITH RECURSIVE SubDepartments AS (...)：这是一个递归CTE，用于查询指定部门及其所有子部门。

   • 基础查询部分选择指定的部门。
   • 递归部分通过INNER JOIN将子部门与父部门关联起来，直到没有更多的子部门为止。

2. SELECT DISTINCT e.id, e.name, e.department_id：最后，将递归查询的结果与employees表进行JOIN，获取所有员工，并使用DISTINCT来避免重复。

注意事项

• 递归深度：MySQL默认的递归深度是1000，如果你的部门层级非常深，可能需要调整cte_max_recursion_depth参数。
• 性能：递归查询在大数据集上可能会有性能问题，建议在适当的情况下对departments表进行索引优化。

索引建议

• 在departments表的parent_id列上创建索引，以加速递归查询。
• 在employees表的department_id列上创建索引，以加速JOIN操作。

通过这种方式，你可以高效地查询某个部门及其所有子部门下的所有员工，并确保结果中没有重复记录。