SQL Case表达式覆盖问题：多业务单元员工Individual Type统一赋值

问题背景 #

员工被分配至多个业务单元时，需根据以下规则推导Individual Type：

• 规则1：若员工仅属于DIST、CS-IA、GS-IA，且无规则2中的业务单元，Individual Type为'IA'；
• 规则2：若员工属于CI-CLMS、CI-DIST、GS-CLMS、GS-DIST、PL-CLMS、SFCLMS、PI-CRC、PI-DRC、PI-FLDSLS、PI-HODST、EXTLV中的任意一个业务单元（含实际数据中的变体如PLCLM8-BI、PI-FldSls2、PI-HODst4），无论是否拥有其他业务单元，所有行的Individual Type均为'EMP'；
• 规则3：若员工属于PI-IA、CI-Clms或PI-temp（含实际数据中的CI-ClmsTA）中的任意一个业务单元，且不属于规则2中的业务单元，Individual Type为'CWK'。

当前问题：员工编号1617413同时拥有DIST和PI-FldSls2业务单元，原SQL仅按单行业务单元判断，导致DIST行的Individual Type为'IA'，但期望所有行统一为'EMP'。

原SQL代码 #

select 
 distinct
    i.lastname,
    I.firstname,
    I.empnumber, 
    b.businessunit,
case 
when b.businessunit = 'DIST' or b.businessunit in ('CI-IA_1')
    THEN 'IA'
when b.businessunit in ('PLCLM8-BI') 
        or b.businessunit in ( 'PI-FldSls2') 
        or b.businessunit in ('PI-HODst4' ) 
        or b.businessunit in ('EXTLV')
    THEN 'EMP'
When b.businessunit in ('CI-ClmsTA') 
    THEN 'CWK'
END
as 'individualtype'
from
individual i left join businessunit b
on i.internalid = b.internalid
WHERE I.status IN ('Active','Inactive','Pending') 
      AND B.status = 'ACTIVE' 
      and i.empnumber = '1617413' 

当前查询结果 #

期望查询结果 #

表结构 #

individual表 #

businessunit表 #

修改后的SQL代码 #

SELECT 
    i.lastname,
    i.firstname,
    i.empnumber, 
    b.businessunit,
    CASE
        -- 规则2优先级最高：检查员工是否拥有任意规则2范围内的业务单元
        WHEN EXISTS (
            SELECT 1 
            FROM businessunit b2 
            WHERE b2.internalid = i.internalid 
              AND b2.status = 'ACTIVE'
              AND b2.businessunit IN (
                  'CI-CLMS', 'CI-DIST', 'GS-CLMS', 'GS-DIST', 'PL-CLMS', 
                  'SFCLMS', 'PI-CRC', 'PI-DRC', 'PI-FLDSLS', 'PI-HODST', 
                  'EXTLV', 'PLCLM8-BI', 'PI-FldSls2', 'PI-HODst4'
              )
        ) THEN 'EMP'
        -- 规则3：检查员工是否拥有规则3范围内的业务单元且不符合规则2
        WHEN EXISTS (
            SELECT 1 
            FROM businessunit b3 
            WHERE b3.internalid = i.internalid 
              AND b3.status = 'ACTIVE'
              AND b3.businessunit IN ('PI-IA', 'CI-Clms', 'PI-temp', 'CI-ClmsTA')
        ) THEN 'CWK'
        -- 规则1：仅拥有规则1范围内的业务单元且不符合前两个规则
        WHEN EXISTS (
            SELECT 1 
            FROM businessunit b1 
            WHERE b1.internalid = i.internalid 
              AND b1.status = 'ACTIVE'
              AND b1.businessunit IN ('DIST', 'CS-IA', 'GS-IA', 'CI-IA_1')
        )
        AND NOT EXISTS (
            SELECT 1 
            FROM businessunit b_other 
            WHERE b_other.internalid = i.internalid 
              AND b_other.status = 'ACTIVE'
              AND b_other.businessunit NOT IN ('DIST', 'CS-IA', 'GS-IA', 'CI-IA_1')
        ) THEN 'IA'
    END AS individualtype
FROM individual i 
LEFT JOIN businessunit b ON i.internalid = b.internalid
WHERE i.status IN ('Active','Inactive','Pending') 
  AND b.status = 'ACTIVE' 
  AND i.empnumber = '1617413' 
GROUP BY i.lastname, i.firstname, i.empnumber, b.businessunit;

说明 #

• 使用EXISTS子查询检查员工的所有业务单元，而非仅当前行的业务单元，确保规则优先级覆盖；
• 规则2优先判断：只要员工有任意规则2的业务单元，所有行返回EMP；
• 规则3次之：仅当员工不符合规则2时才判断；
• 规则1最后：需确认员工仅拥有规则1的业务单元且不符合前两个规则。

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