SQL Server 2019多条件新增Business、Plant列及报错解决

你之前执行报错的语句：

select *,Business case when code in ('A231', B343') and Rep='Alex' then Business='Steel' else Null End as Business

存在3处语法问题：

• SELECT * 后冗余书写了Business，导致CASE表达式的语法结构被破坏，这是提示「关键字'case'附近有语法错误」的直接原因
• IN条件列表里的B343'缺失前导单引号，字符串常量必须用成对单引号包裹
• CASE是逐行计算返回值的表达式，不是赋值语句，THEN后直接写要返回的取值即可，不需要写Business='Steel'这类赋值格式。

将语句中的替换为你的实际表名替换为你当前操作的数据表真实名称即可，语法完全兼容SQL Server 2019：

SELECT
    *,
    CASE
        WHEN Code IN ('A231','B343') AND Rep = 'Alex' AND Area = 'Del' THEN 'Steel'
        WHEN Code IN ('A231','B343') AND Rep = 'Alen' AND Area = 'Mum' THEN 'Iron'
        ELSE NULL
    END AS Business,
    CASE
        WHEN (Code IN ('A231','B343') AND Rep = 'Alex' AND Area = 'Del')
          OR (Code IN ('A231','B343') AND Rep = 'Alen' AND Area = 'Mum') THEN 'EXR'
        ELSE NULL
    END AS Plant
FROM 替换为你的实际表名;

执行后会返回原表的Code、Rep、Area三个字段，同时新增匹配规则填充的Business、Plant字段，和预期输出结果一致。如果后续需要新增更多匹配规则，在两个CASE语句里追加WHEN分支即可。

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