mysql 所有下级

原创

mob64ca12ef9b85 2024-10-10 04:59:27 ©著作权

文章标签 MySQL 递归查询 sql 文章分类 MySQL 数据库

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

MySQL 中的层级关系与下级查询

在实际开发中，尤其是涉及到树型结构（如组织架构、分类等）时，我们常常需要从数据库中查询一个节点的所有下级。本文将探讨如何在 MySQL 中实现这一功能，并给出详细的代码示例。

层级关系的模型

在数据库中，层级关系通常使用自关联（self-referential）表来表示。假设我们有一个员工表 employees，其结构如下：

id (主键)
name (员工姓名)
manager_id (上级员工的 id)

这个表中，manager_id 字段指向同一表中的 id 字段，从而形成了树形结构。

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT,
    FOREIGN KEY (manager_id) REFERENCES employees(id)
);

数据插入示例

我们可以插入一些初始数据来构建树形结构：

INSERT INTO employees (id, name, manager_id) VALUES
(1, 'CEO', NULL),
(2, 'Manager A', 1),
(3, 'Manager B', 1),
(4, 'Employee A1', 2),
(5, 'Employee A2', 2),
(6, 'Employee B1', 3);

在此示例中，CEO 是根节点，Manager A 和 Manager B 是它的直接下级，而 Employee A1 和 Employee A2 则是 Manager A 的下级，Employee B1 是 Manager B 的下级。

关系图

下面是我们关系模型的抽象表示：

erDiagram
    EMPLOYEES {
        INT id PK "员工ID"
        VARCHAR name "员工姓名"
        INT manager_id "上级员工ID"
    }
    EMPLOYEES ||--o{ EMPLOYEES : "上属"

获取所有下级的查询

为了获取某个特定员工的所有下级，通常我们需要用递归查询来达成目标。但在 MySQL 中，可以使用临时表或递归 CTE（Common Table Expressions）来实现。假设我们想获取 Manager A 的所有下级，使用了递归查询的方式如下：

在 MySQL 8.0 中，可以使用 CTE 语法：

WITH RECURSIVE subordinates AS (
    SELECT id, name, manager_id
    FROM employees
    WHERE manager_id = 2  -- 假设我们要查询的上级ID是2

    UNION ALL

    SELECT e.id, e.name, e.manager_id
    FROM employees e
    INNER JOIN subordinates s ON e.manager_id = s.id
)

SELECT * FROM subordinates;