如何编写SQL自连接查询实现员工层级经理阶梯式展示

原始员工表 #

需求说明 #

需要为每个员工生成l1-l4字段，规则如下：

• l1：组织最高层级（level 1）的经理姓名
• l2：员工的直属上一级经理（仅当员工层级≥3时填充）
• l3：员工的上上级经理（仅当员工层级=4时填充）
• l4：当前最高层级为4，所有记录均为NULL

实现方法 #

方法1：多次自连接（适合固定少量层级） #

逻辑直观，适合层级数量明确且较少的场景：

SELECT 
    e.emp_id,
    e.emp,
    e.level,
    e.manager_id,
    e.manager,
    -- level≥2时，l1为最高层经理
    CASE WHEN e.level >= 2 THEN l1.emp ELSE NULL END AS l1,
    -- level≥3时，l2为直属上一级经理
    CASE WHEN e.level >= 3 THEN e.manager ELSE NULL END AS l2,
    -- level=4时，l3为直属上一级经理
    CASE WHEN e.level = 4 THEN e.manager ELSE NULL END AS l3,
    NULL AS l4
FROM employees e
LEFT JOIN employees l1 ON e.manager_id = l1.emp_id AND l1.level = 1
ORDER BY e.emp_id;

方法2：递归CTE（适合层级不固定的场景） #

扩展性更强，可自动适配后续新增的层级：

WITH RECURSIVE emp_hierarchy AS (
    -- 起始节点：最高层级员工
    SELECT 
        emp_id,
        emp,
        level,
        manager_id,
        manager,
        CAST(NULL AS VARCHAR(50)) AS l1,
        CAST(NULL AS VARCHAR(50)) AS l2,
        CAST(NULL AS VARCHAR(50)) AS l3,
        CAST(NULL AS VARCHAR(50)) AS l4
    FROM employees
    WHERE level = 1

    UNION ALL

    -- 递归遍历下一层级员工
    SELECT 
        e.emp_id,
        e.emp,
        e.level,
        e.manager_id,
        e.manager,
        CASE WHEN e.level = 2 THEN h.emp ELSE h.l1 END,
        CASE WHEN e.level = 3 THEN e.manager ELSE h.l2 END,
        CASE WHEN e.level = 4 THEN e.manager ELSE h.l3 END,
        h.l4
    FROM employees e
    JOIN emp_hierarchy h ON e.manager_id = h.emp_id
)
SELECT * FROM emp_hierarchy ORDER BY emp_id;

最终结果 #

执行上述任一SQL后，将得到如下目标表：

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