基于employment表生成员工职位的SQL查询请求

需求说明 #

基于employment表的字段规则，对员工进行职位分类：

• CEO：boss字段为空的员工
• 经理：被其他员工的boss字段关联的员工（即存在下属）
• 普通员工：不属于以上两类的员工

实现方案 #

方案一：直观子查询写法

适合数据量较小的场景，逻辑清晰易懂：

SELECT 
    id,
    name,
    CASE
        WHEN boss IS NULL THEN 'CEO'
        WHEN EXISTS (SELECT 1 FROM employment sub WHERE sub.boss = employment.id) THEN 'Manager'
        ELSE 'Employee'
    END AS position
FROM employment;

方案二：预统计优化性能

先提取所有有下属的员工ID，再关联分类，适合数据量较大的场景：

SELECT 
    e.id,
    e.name,
    CASE
        WHEN e.boss IS NULL THEN 'CEO'
        WHEN m.boss IS NOT NULL THEN 'Manager'
        ELSE 'Employee'
    END AS position
FROM employment e
LEFT JOIN (
    SELECT DISTINCT boss 
    FROM employment 
    WHERE boss IS NOT NULL
) m ON e.id = m.boss;

逻辑解释 #

1. 优先判断boss字段是否为空，满足则直接标记为CEO
2. 非CEO员工，验证是否存在下属（子查询直接校验/关联预统计列表），存在则标记为Manager
3. 剩余员工统一标记为Employee

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