SQL Server CASE语句的THEN子句能否包含多个表达式？

在SQL Server中，CASE语句的THEN/ELSE子句不能直接包含多个表达式来返回多列——CASE本质是一个标量表达式，每次只能输出单个值（对应一列的结果）。你给出的写法在语法上是不合法的。

如果需要根据条件返回不同的多列，正确的做法是为每一列单独编写CASE逻辑，示例如下：

SELECT 
    EmployeeId,
    -- 针对每一列分别设置条件判断
    CASE WHEN Dateofjoin > '2000-01-01' THEN Column1 ELSE Column5 END AS ResultColumn1,
    CASE WHEN Dateofjoin > '2000-01-01' THEN Column2 ELSE Column6 END AS ResultColumn2,
    CASE WHEN Dateofjoin > '2000-01-01' THEN Column3 ELSE Column6 END AS ResultColumn3
FROM EmployeeTABLE

如果条件判断逻辑比较复杂，不想重复写相同的条件，可以先通过CTE或子查询提前计算条件结果，再在后续的CASE语句中引用：

WITH EmployeeWithCondition AS (
    SELECT 
        EmployeeId,
        Dateofjoin,
        Column1, Column2, Column3,
        Column5, Column6,
        -- 提前算出条件标识
        CASE WHEN Dateofjoin > '2000-01-01' THEN 1 ELSE 0 END AS IsJoinedAfter2000
    FROM EmployeeTABLE
)
SELECT 
    EmployeeId,
    CASE WHEN IsJoinedAfter2000 = 1 THEN Column1 ELSE Column5 END AS ResultColumn1,
    CASE WHEN IsJoinedAfter2000 = 1 THEN Column2 ELSE Column6 END AS ResultColumn2,
    CASE WHEN IsJoinedAfter2000 = 1 THEN Column3 ELSE Column6 END AS ResultColumn3
FROM EmployeeWithCondition

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