如何用MySQL查询遍历同表中的上下级关联关系？

当然可以用MySQL的递归CTE（Common Table Expressions）实现这个需求，相比PHP嵌套循环，它能在数据库端一次性完成层级遍历，减少多次数据库交互带来的性能损耗，效率更高。

假设你的people表结构如下：

CREATE TABLE people (
    id INT PRIMARY KEY,
    firstname VARCHAR(50),
    lastname VARCHAR(50),
    supervisor INT NULL,
    FOREIGN KEY (supervisor) REFERENCES people(id)
);

1. 查询某人员的所有上级（直至最高层） #

以查询ID为5的员工所有上级为例，递归CTE会从直接上级开始，逐层向上遍历直到无上级（supervisor为NULL）：

WITH RECURSIVE supervisor_hierarchy AS (
    -- 起始节点：目标员工的直接上级
    SELECT id, firstname, lastname, supervisor, 1 AS level
    FROM people
    WHERE id = (SELECT supervisor FROM people WHERE id = 5)
    UNION ALL
    -- 递归遍历：获取当前节点的上级
    SELECT p.id, p.firstname, p.lastname, p.supervisor, sh.level + 1
    FROM people p
    JOIN supervisor_hierarchy sh ON p.id = sh.supervisor
)
-- 输出所有上级，level字段表示层级（1为直接上级，数字越大层级越高）
SELECT * FROM supervisor_hierarchy
ORDER BY level ASC;

如果需要把目标员工自己也包含在结果里，可以修改起始节点：

WITH RECURSIVE supervisor_hierarchy AS (
    SELECT id, firstname, lastname, supervisor, 0 AS level
    FROM people
    WHERE id = 5
    UNION ALL
    SELECT p.id, p.firstname, p.lastname, p.supervisor, sh.level + 1
    FROM people p
    JOIN supervisor_hierarchy sh ON p.id = sh.supervisor
)
SELECT * FROM supervisor_hierarchy
ORDER BY level ASC;

2. 查询某人员的所有下属（含下属的下属，直至最底层） #

以查询ID为2的员工所有下属为例，递归CTE从直接下属开始，逐层向下遍历：

WITH RECURSIVE subordinate_hierarchy AS (
    -- 起始节点：目标员工的直接下属
    SELECT id, firstname, lastname, supervisor, 1 AS level
    FROM people
    WHERE supervisor = 2
    UNION ALL
    -- 递归遍历：获取当前节点的下属
    SELECT p.id, p.firstname, p.lastname, p.supervisor, sh.level + 1
    FROM people p
    JOIN subordinate_hierarchy sh ON p.supervisor = sh.id
)
-- 输出所有下属，level字段表示层级（1为直接下属，数字越大层级越低）
SELECT * FROM subordinate_hierarchy
ORDER BY level ASC;

同样，如果要包含目标员工自己，调整起始节点即可：

WITH RECURSIVE subordinate_hierarchy AS (
    SELECT id, firstname, lastname, supervisor, 0 AS level
    FROM people
    WHERE id = 2
    UNION ALL
    SELECT p.id, p.firstname, p.lastname, p.supervisor, sh.level + 1
    FROM people p
    JOIN subordinate_hierarchy sh ON p.supervisor = sh.id
)
SELECT * FROM subordinate_hierarchy
ORDER BY level ASC;

优势说明 #

递归CTE是数据库原生支持的层级查询方式，相比PHP嵌套while循环：

• 减少了PHP与数据库的多次交互，避免了循环中反复发起查询的开销
• 数据库端的递归优化更高效，尤其是数据量较大时，性能提升明显
• 逻辑更集中，便于维护和修改

内容的提问来源于stack exchange，提问作者RBurns