SQL员工层级查询：无下属分配时如何保留2级经理的记录

你当前的查询以EmpID表作为Level3（最底层员工）的驱动表，向上关联查询上级层级，只有存在对应Level3员工的链路才会被保留。没有下属的经理没有对应的Level3关联记录，因此无法出现在Level2的位置。

方案1：最小改动适配现有逻辑 #

直接在你原有查询的基础上，用UNION ALL补全无下属经理的记录即可：

-- 原有查询得到有完整链路的层级记录
SELECT 
    pc2.sourceid AS Level_1,
    pc.sourceid AS Level_2,
    eid.empID AS Level_3
FROM empid as eid
LEFT JOIN parentchild as pc on eid.empID = pc.destinationID
LEFT JOIN empid as eid2 on pc.sourceId = eid2.empid
LEFT JOIN parentchild as pc2 on eid2.empid = pc2.destinationID

UNION ALL

-- 补全无下属的经理记录
SELECT 
    NULL AS Level_1,
    pc.destinationID AS Level_2,
    pc.sourceID AS Level_3
FROM parentchild pc
-- 过滤没有子节点的经理：不存在以该ID为SourceID的关联记录
LEFT JOIN parentchild pc_child ON pc.destinationID = pc_child.sourceID
WHERE pc_child.destinationID IS NULL

方案2：递归CTE通用方案（支持更多层级扩展） #

如果后续可能扩展层级数量，用递归CTE的方案更易维护：

WITH RECURSIVE hierarchy AS (
    -- 锚点：顶层节点（无上级）
    SELECT 
        DestinationID AS emp_id,
        SourceID AS parent_id,
        3 AS lvl
    FROM parentchild
    WHERE SourceID NOT IN (SELECT DestinationID FROM parentchild)
    
    UNION ALL
    
    -- 递归向下遍历子节点
    SELECT
        pc.DestinationID AS emp_id,
        pc.SourceID AS parent_id,
        h.lvl - 1 AS lvl
    FROM hierarchy h
    JOIN parentchild pc ON h.emp_id = pc.SourceID
)
-- 行列转换输出三级结构
SELECT
    MAX(IF(lvl=1, emp_id, NULL)) AS Level_1,
    MAX(IF(lvl=2, emp_id, NULL)) AS Level_2,
    MAX(IF(lvl=3, emp_id, NULL)) AS Level_3
FROM hierarchy
GROUP BY parent_id

两种方案都可以得到你要求的预期结果。

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