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

要高效查询MySQL数据库中某个部门及其所有子部门下的所有员工，并避免重复，可以使用递归查询（Recursive CTE）和适当的索引来优化查询性能。以下是一个详细的解决方案：

1. 数据库表结构

假设你有以下两张表：

• departments 表：存储部门信息

  CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    parent_id INT
  );
  

• employees 表：存储员工信息

  CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(id)
  );
  

2. 递归查询部门及其子部门

MySQL 8.0及以上版本支持递归CTE（Common Table Expressions），可以用来递归查询某个部门及其所有子部门。

WITH RECURSIVE DepartmentTree 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 DepartmentTree dt ON d.parent_id = dt.id
)

3. 查询员工信息

在递归查询的基础上，你可以进一步查询这些部门下的所有员工。

SELECT DISTINCT e.*
FROM employees e
JOIN DepartmentTree dt ON e.department_id = dt.id;

4. 完整查询

将上述两部分结合起来，完整的查询如下：

WITH RECURSIVE DepartmentTree 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 DepartmentTree dt ON d.parent_id = dt.id
)
SELECT DISTINCT e.*
FROM employees e
JOIN DepartmentTree dt ON e.department_id = dt.id;

5. 优化建议

• 索引：确保 departments 表的 parent_id 列和 employees 表的 department_id 列上有索引，以加快查询速度。

  CREATE INDEX idx_departments_parent_id ON departments(parent_id);
  CREATE INDEX idx_employees_department_id ON employees(department_id);
  

• 避免重复：使用 DISTINCT 关键字来确保查询结果中没有重复的员工记录。

6. 示例

假设你要查询部门ID为1的部门及其所有子部门下的员工：

WITH RECURSIVE DepartmentTree AS (
    SELECT id, name, parent_id
    FROM departments
    WHERE id = 1

    UNION ALL

    SELECT d.id, d.name, d.parent_id
    FROM departments d
    INNER JOIN DepartmentTree dt ON d.parent_id = dt.id
)
SELECT DISTINCT e.*
FROM employees e
JOIN DepartmentTree dt ON e.department_id = dt.id;

7. 总结

通过使用递归CTE和适当的索引，你可以高效地查询某个部门及其所有子部门下的所有员工，并确保结果中没有重复记录。这种方法在MySQL 8.0及以上版本中非常有效。