mysql如何查询每个部门的最上级部门

原创

mob64ca12da726f 2024-10-22 05:59:07 ©著作权

©著作权归作者所有：来自51CTO博客作者mob64ca12da726f的原创作品，请联系作者获取转载授权，否则将追究法律责任

如何查询每个部门的最上级部门

在企业数据库中，部门结构常常是分层次的，也就是说某些部门可能是其他部门的子部门。因此，当我们需要查询每个部门的最上级部门时，必须理解如何有效地处理这样的层级关系。

数据库设计

假设我们有一个部门表 departments，其结构如下：

字段	类型	描述
id	INT	部门ID，主键
name	VARCHAR(100)	部门名称
parent_id	INT	上级部门的ID

在这个表中，parent_id 字段指向该部门的上级部门。根部门的 parent_id 为 NULL。

示例数据

我们可以插入一些示例数据，以帮助理解：

INSERT INTO departments (id, name, parent_id) VALUES
(1, '公司总部', NULL),
(2, '人事部', 1),
(3, '技术部', 1),
(4, '软件部', 3),
(5, '硬件部', 3),
(6, '财务部', 1);

在这个数据模型中，'公司总部' 是所有部门的最上级部门，其他部门逐级向下。

查询最上级部门

为了查询每个部门的最上级部门，我们可以使用递归查询（在 MySQL 8.0 及以上版本中）或使用临时表。如果使用自连接的方式也可以，但递归查询更直观且易于管理层级关系。

使用递归CTE

以下是使用递归公共表表达式（CTE）的查询示例：

WITH RECURSIVE DepartmentCTE AS (
    SELECT id, name, parent_id
    FROM departments
    WHERE parent_id IS NULL -- 从根部门开始
    UNION ALL
    SELECT d.id, d.name, d.parent_id
    FROM departments d
    INNER JOIN DepartmentCTE cte ON d.parent_id = cte.id
)
SELECT d.id AS department_id, 
       d.name AS department_name, 
       cte.id AS top_level_department_id, 
       cte.name AS top_level_department_name
FROM departments d
LEFT JOIN DepartmentCTE cte ON d.parent_id = cte.id OR d.id = cte.id;

在这个查询中，首先，我们从根部门开始，然后递归地查找所有子部门。最后，通过连接将每个部门与其最上级部门相关联。

查询结果解读

查询结果将会返回所有部门及其对应的最上级部门，例如：

department_id	department_name	top_level_department_id	top_level_department_name
1	公司总部	1	公司总部
2	人事部	1	公司总部
3	技术部	1	公司总部
4	软件部	1	公司总部
5	硬件部	1	公司总部
6	财务部	1	公司总部

处理复杂的层级关系

当部门结构更加复杂时，此种方法同样有效。例如，如果每个部门可以有不同的层级，或存在多个根部门，递归的CTE依然能够自适应处理，确保每个部门都能准确地找到其最上级的部门。

为了进一步理解这个过程，我们可以用序列图来描述查询过程的逻辑。

序列图展示

sequenceDiagram
    participant D as Database
    participant A as Application
    participant C as Recursive CTE
    
    A->>D: 查询所有部门
    D-->>A: 返回部门信息
    A->>C: 启动递归CTE
    C->>D: 查询根部门
    D-->>C: 返回根部门
    C->>D: 查询子部门
    D-->>C: 返回子部门
    C->>C: 继续递归
    C-->>A: 返回每个部门的最上级部门