MySQL中无员工ID类数字列的表如何查询员工及其各层级上级姓名

适用场景说明 #

现有员工表仅存储员工姓名、直属上级姓名两个字段，无数字类型关联ID，需查询每位员工最多3层上级信息，无对应上级时字段填充no_supervisor。

方案1：全版本MySQL通用（三次自连接实现，适合固定层级场景） #

无需依赖递归CTE特性，MySQL 5.x及以上版本均可运行，代码如下：

SELECT 
    t1.Employee_Name,
    t1.Supervisor_Name AS Supervisor_Name,
    COALESCE(t2.Supervisor_Name, 'no_supervisor') AS Higher_Supervisor,
    COALESCE(t3.Supervisor_Name, 'no_supervisor') AS Next_higher_Supervisor
FROM DATABASE_TABLE t1
LEFT JOIN DATABASE_TABLE t2 ON t1.Supervisor_Name = t2.Employee_Name
LEFT JOIN DATABASE_TABLE t3 ON t2.Supervisor_Name = t3.Employee_Name
ORDER BY t1.Employee_Name;

逻辑说明：

• t1表取基础员工与直属上级对应关系
• t2左连接匹配直属上级的上级（隔级上级）
• t3左连接匹配隔级上级的上级（更高级上级）
• 用COALESCE函数将匹配不到的空值替换为指定的no_supervisor

方案2：MySQL 8.0+递归实现（适合层级可扩展场景） #

如果后续需要扩展更多上级层级，可使用递归CTE实现，代码如下：

WITH RECURSIVE emp_hierarchy AS (
    -- 锚点层：取员工与直属上级信息
    SELECT 
        Employee_Name,
        Supervisor_Name AS l1_supervisor,
        Supervisor_Name AS current_supervisor,
        1 AS level
    FROM DATABASE_TABLE
    UNION ALL
    -- 递归层：逐层向上匹配上级，最多向上查询3层
    SELECT 
        eh.Employee_Name,
        eh.l1_supervisor,
        dt.Supervisor_Name AS current_supervisor,
        eh.level + 1 AS level
    FROM emp_hierarchy eh
    JOIN DATABASE_TABLE dt ON eh.current_supervisor = dt.Employee_Name
    WHERE eh.level < 3
)
SELECT 
    e.Employee_Name,
    COALESCE(MAX(CASE WHEN eh.level = 1 THEN eh.l1_supervisor END), 'no_supervisor') AS Supervisor_Name,
    COALESCE(MAX(CASE WHEN eh.level = 2 THEN eh.current_supervisor END), 'no_supervisor') AS Higher_Supervisor,
    COALESCE(MAX(CASE WHEN eh.level = 3 THEN eh.current_supervisor END), 'no_supervisor') AS Next_higher_Supervisor
FROM DATABASE_TABLE e
LEFT JOIN emp_hierarchy eh ON e.Employee_Name = eh.Employee_Name
GROUP BY e.Employee_Name
ORDER BY e.Employee_Name;

输出验证 #

上述两种方案执行后均符合预期输出要求，示例输出如下：

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