两列员工-经理表配对生成三列表的技术实现求助

我来帮你搞定这个员工配对的问题！之前的代码出现重叠，大概率是没按经理分组后的员工序号来精准控制配对逻辑，咱们一步步来解决：

首先得明确咱们的核心规则：

• 经理仅管1名员工：输出「经理ID + 员工ID + null」
• 经理管2名员工：输出1条「经理ID + 员工1 + 员工2」的配对记录
• 经理管3名员工：输出两条记录——一条「员工1+员工2」的配对，一条「员工3+null」的记录

第一步：准备测试数据（方便验证） #

先模拟一个员工-经理表，方便咱们测试逻辑：

CREATE TABLE emp_manager (
    employee_id INT,
    manager_id INT
);

INSERT INTO emp_manager VALUES
(1, 100), -- 经理100管2人
(2, 100),
(3, 200), -- 经理200管1人
(4, 300), -- 经理300管3人
(5, 300),
(6, 300);

第二步：用CTE给员工标记组内序号和人数 #

咱们先给每个经理手下的员工按ID排序（也可以按其他业务字段调整），同时算出每个经理的总下属数，这是后续判断的基础：

WITH emp_ranked AS (
    SELECT 
        employee_id,
        manager_id,
        -- 给每个经理的员工排唯一序号
        ROW_NUMBER() OVER (PARTITION BY manager_id ORDER BY employee_id) AS rn,
        -- 统计每个经理的下属总数
        COUNT(*) OVER (PARTITION BY manager_id) AS emp_count
    FROM emp_manager
)

第三步：分情况生成目标记录 #

用UNION ALL把三种情况的结果合并，这样能保证每种情况的记录独立生成，不会出现重叠：

-- 情况1：经理仅管1人，输出员工+null
SELECT 
    manager_id,
    employee_id AS emp1,
    NULL AS emp2
FROM emp_ranked
WHERE emp_count = 1

UNION ALL

-- 情况2：经理管2人，配对两个员工
SELECT 
    e1.manager_id,
    e1.employee_id AS emp1,
    e2.employee_id AS emp2
FROM emp_ranked e1
JOIN emp_ranked e2 
    ON e1.manager_id = e2.manager_id 
    AND e1.rn = 1 
    AND e2.rn = 2
WHERE e1.emp_count = 2

UNION ALL

-- 情况3：经理管3人，先配对前两个员工
SELECT 
    e1.manager_id,
    e1.employee_id AS emp1,
    e2.employee_id AS emp2
FROM emp_ranked e1
JOIN emp_ranked e2 
    ON e1.manager_id = e2.manager_id 
    AND e1.rn = 1 
    AND e2.rn = 2
WHERE e1.emp_count = 3

UNION ALL

-- 情况3补充：把第三个员工和null配对
SELECT 
    manager_id,
    employee_id AS emp1,
    NULL AS emp2
FROM emp_ranked
WHERE emp_count = 3 AND rn = 3

-- 最后按经理ID和员工1排序，结果更清晰
ORDER BY manager_id, emp1;

执行结果验证 #

跑上面的代码后，输出会完全符合预期：

这个逻辑通过序号锁定了每个员工的配对对象，从根源上避免了重叠问题。如果之后需要支持更多下属数量的情况，比如4个员工，只需要在UNION ALL里新增对应的规则逻辑即可。

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