如何将SELECT语句中用CASE创建的5个计算列求和

嗨，我来帮你搞定这个问题～你遇到的核心问题是：在同一个SELECT子句里，SQL没办法直接引用刚刚用CASE定义的列别名——因为SQL解析是按顺序来的，当你写([AML] + [MLO] + ...)的时候，数据库还没识别到这些别名对应的计算逻辑，所以会报错。

下面给你几个实用的解决方案，你可以根据自己的需求选：

方案1：重复CASE表达式直接求和 #

这个方法最直接，虽然代码会变长，但不需要额外嵌套。你只需要把每个CASE的逻辑完整地写到求和表达式里就行：

SELECT
    -- 保留你原来的5个计算列
    [AML] = CASE
        WHEN [B].[rAML] = 1 THEN 30
        WHEN [B].[rAML] = 2 THEN 9
        WHEN [B].[rAML] = 3 THEN 0
        WHEN [B].[rAML] = 4 THEN -6
        WHEN [B].[rAML] = 5 THEN -9
        WHEN [B].[rAML] = 6 THEN -12
        WHEN [B].[rAML] = 7 THEN -13
        WHEN [B].[rAML] = 8 THEN -13
        ELSE -16
    END,
    [MLO] = CASE
        WHEN [A].[rMLO] = 1 THEN 30
        WHEN [A].[rMLO] = 2 THEN 11
        WHEN [A].[rMLO] = 3 THEN 1
        WHEN [A].[rMLO] = 4 THEN -6
        WHEN [A].[rMLO] = 5 THEN -9
        WHEN [A].[rMLO] = 6 THEN -11
        WHEN [A].[rMLO] = 7 THEN -13
        WHEN [A].[rMLO] = 8 THEN -14
        ELSE -15
    END,
    [SYS1] = CASE
        WHEN [B].[rSYS1] = 1 THEN 29
        WHEN [B].[rSYS1] = 2 THEN 12
        WHEN [B].[rSYS1] = 3 THEN 0
        WHEN [B].[rSYS1] = 4 THEN -5
        WHEN [B].[rSYS1] = 5 THEN -9
        WHEN [B].[rSYS1] = 6 THEN -12
        WHEN [B].[rSYS1] = 7 THEN -13
        WHEN [B].[rSYS1] = 8 THEN -13
        ELSE -14
    END,
    [K] = CASE
        WHEN [A].[rK] = 1 THEN 28
        WHEN [A].[rK] = 2 THEN 12
        WHEN [A].[rK] = 3 THEN 2
        WHEN [A].[rK] = 4 THEN -4
        WHEN [A].[rK] = 5 THEN -8
        WHEN [A].[rK] = 6 THEN -11
        WHEN [A].[rK] = 7 THEN -13
        WHEN [A].[rK] = 8 THEN -13
        ELSE -14
    END,
    [HTR] = CASE
        WHEN [A].[rHTR] = 1 THEN 27
        WHEN [A].[rHTR] = 2 THEN 10
        WHEN [A].[rHTR] = 3 THEN 0
        WHEN [A].[rHTR] = 4 THEN -3
        WHEN [A].[rHTR] = 5 THEN -9
        WHEN [A].[rHTR] = 6 THEN -11
        WHEN [A].[rHTR] = 7 THEN -11
        WHEN [A].[rHTR] = 8 THEN -12
        WHEN [A].[rHTR] = 9 THEN -14
        ELSE -15
    END,
    -- 直接把每个CASE逻辑代入求和
    (
        CASE WHEN [B].[rAML] = 1 THEN 30 WHEN [B].[rAML] = 2 THEN 9 WHEN [B].[rAML] = 3 THEN 0 WHEN [B].[rAML] = 4 THEN -6 WHEN [B].[rAML] = 5 THEN -9 WHEN [B].[rAML] = 6 THEN -12 WHEN [B].[rAML] = 7 THEN -13 WHEN [B].[rAML] = 8 THEN -13 ELSE -16 END +
        CASE WHEN [A].[rMLO] = 1 THEN 30 WHEN [A].[rMLO] = 2 THEN 11 WHEN [A].[rMLO] = 3 THEN 1 WHEN [A].[rMLO] = 4 THEN -6 WHEN [A].[rMLO] = 5 THEN -9 WHEN [A].[rMLO] = 6 THEN -11 WHEN [A].[rMLO] = 7 THEN -13 WHEN [A].[rMLO] = 8 THEN -14 ELSE -15 END +
        CASE WHEN [B].[rSYS1] = 1 THEN 29 WHEN [B].[rSYS1] = 2 THEN 12 WHEN [B].[rSYS1] = 3 THEN 0 WHEN [B].[rSYS1] = 4 THEN -5 WHEN [B].[rSYS1] = 5 THEN -9 WHEN [B].[rSYS1] = 6 THEN -12 WHEN [B].[rSYS1] = 7 THEN -13 WHEN [B].[rSYS1] = 8 THEN -13 ELSE -14 END +
        CASE WHEN [A].[rK] = 1 THEN 28 WHEN [A].[rK] = 2 THEN 12 WHEN [A].[rK] = 3 THEN 2 WHEN [A].[rK] = 4 THEN -4 WHEN [A].[rK] = 5 THEN -8 WHEN [A].[rK] = 6 THEN -11 WHEN [A].[rK] = 7 THEN -13 WHEN [A].[rK] = 8 THEN -13 ELSE -14 END +
        CASE WHEN [A].[rHTR] = 1 THEN 27 WHEN [A].[rHTR] = 2 THEN 10 WHEN [A].[rHTR] = 3 THEN 0 WHEN [A].[rHTR] = 4 THEN -3 WHEN [A].[rHTR] = 5 THEN -9 WHEN [A].[rHTR] = 6 THEN -11 WHEN [A].[rHTR] = 7 THEN -11 WHEN [A].[rHTR] = 8 THEN -12 WHEN [A].[rHTR] = 9 THEN -14 ELSE -15 END
    ) AS [TOT]
FROM 
    -- 这里写你的A和B表的INNER JOIN逻辑
    A INNER JOIN B ON A.xxx = B.xxx

方案2：用CTE（公共表表达式）简化代码 #

如果觉得重复CASE太冗余，用CTE先把计算列算好，再在外层求和会更清晰：

WITH CalculatedColumns AS (
    SELECT
        [AML] = CASE
            WHEN [B].[rAML] = 1 THEN 30
            WHEN [B].[rAML] = 2 THEN 9
            WHEN [B].[rAML] = 3 THEN 0
            WHEN [B].[rAML] = 4 THEN -6
            WHEN [B].[rAML] = 5 THEN -9
            WHEN [B].[rAML] = 6 THEN -12
            WHEN [B].[rAML] = 7 THEN -13
            WHEN [B].[rAML] = 8 THEN -13
            ELSE -16
        END,
        [MLO] = CASE
            WHEN [A].[rMLO] = 1 THEN 30
            WHEN [A].[rMLO] = 2 THEN 11
            WHEN [A].[rMLO] = 3 THEN 1
            WHEN [A].[rMLO] = 4 THEN -6
            WHEN [A].[rMLO] = 5 THEN -9
            WHEN [A].[rMLO] = 6 THEN -11
            WHEN [A].[rMLO] = 7 THEN -13
            WHEN [A].[rMLO] = 8 THEN -14
            ELSE -15
        END,
        [SYS1] = CASE
            WHEN [B].[rSYS1] = 1 THEN 29
            WHEN [B].[rSYS1] = 2 THEN 12
            WHEN [B].[rSYS1] = 3 THEN 0
            WHEN [B].[rSYS1] = 4 THEN -5
            WHEN [B].[rSYS1] = 5 THEN -9
            WHEN [B].[rSYS1] = 6 THEN -12
            WHEN [B].[rSYS1] = 7 THEN -13
            WHEN [B].[rSYS1] = 8 THEN -13
            ELSE -14
        END,
        [K] = CASE
            WHEN [A].[rK] = 1 THEN 28
            WHEN [A].[rK] = 2 THEN 12
            WHEN [A].[rK] = 3 THEN 2
            WHEN [A].[rK] = 4 THEN -4
            WHEN [A].[rK] = 5 THEN -8
            WHEN [A].[rK] = 6 THEN -11
            WHEN [A].[rK] = 7 THEN -13
            WHEN [A].[rK] = 8 THEN -13
            ELSE -14
        END,
        [HTR] = CASE
            WHEN [A].[rHTR] = 1 THEN 27
            WHEN [A].[rHTR] = 2 THEN 10
            WHEN [A].[rHTR] = 3 THEN 0
            WHEN [A].[rHTR] = 4 THEN -3
            WHEN [A].[rHTR] = 5 THEN -9
            WHEN [A].[rHTR] = 6 THEN -11
            WHEN [A].[rHTR] = 7 THEN -11
            WHEN [A].[rHTR] = 8 THEN -12
            WHEN [A].[rHTR] = 9 THEN -14
            ELSE -15
        END
    FROM 
        A INNER JOIN B ON A.xxx = B.xxx
)
SELECT
    AML, MLO, SYS1, K, HTR,
    (AML + MLO + SYS1 + K + HTR) AS [TOT]
FROM CalculatedColumns

方案3：用CROSS APPLY（适用于SQL Server） #

如果你的数据库是SQL Server，还可以用CROSS APPLY来定义这些计算列，然后直接引用别名求和，代码也很简洁：

SELECT
    calc.AML, calc.MLO, calc.SYS1, calc.K, calc.HTR,
    (calc.AML + calc.MLO + calc.SYS1 + calc.K + calc.HTR) AS [TOT]
FROM 
    A INNER JOIN B ON A.xxx = B.xxx
CROSS APPLY (
    SELECT
        [AML] = CASE WHEN [B].[rAML] = 1 THEN 30 WHEN [B].[rAML] = 2 THEN 9 WHEN [B].[rAML] = 3 THEN 0 WHEN [B].[rAML] = 4 THEN -6 WHEN [B].[rAML] = 5 THEN -9 WHEN [B].[rAML] = 6 THEN -12 WHEN [B].[rAML] = 7 THEN -13 WHEN [B].[rAML] = 8 THEN -13 ELSE -16 END,
        [MLO] = CASE WHEN [A].[rMLO] = 1 THEN 30 WHEN [A].[rMLO] = 2 THEN 11 WHEN [A].[rMLO] = 3 THEN 1 WHEN [A].[rMLO] = 4 THEN -6 WHEN [A].[rMLO] = 5 THEN -9 WHEN [A].[rMLO] = 6 THEN -11 WHEN [A].[rMLO] = 7 THEN -13 WHEN [A].[rMLO] = 8 THEN -14 ELSE -15 END,
        [SYS1] = CASE WHEN [B].[rSYS1] = 1 THEN 29 WHEN [B].[rSYS1] = 2 THEN 12 WHEN [B].[rSYS1] = 3 THEN 0 WHEN [B].[rSYS1] = 4 THEN -5 WHEN [B].[rSYS1] = 5 THEN -9 WHEN [B].[rSYS1] = 6 THEN -12 WHEN [B].[rSYS1] = 7 THEN -13 WHEN [B].[rSYS1] = 8 THEN -13 ELSE -14 END,
        [K] = CASE WHEN [A].[rK] = 1 THEN 28 WHEN [A].[rK] = 2 THEN 12 WHEN [A].[rK] = 3 THEN 2 WHEN [A].[rK] = 4 THEN -4 WHEN [A].[rK] = 5 THEN -8 WHEN [A].[rK] = 6 THEN -11 WHEN [A].[rK] = 7 THEN -13 WHEN [A].[rK] = 8 THEN -13 ELSE -14 END,
        [HTR] = CASE WHEN [A].[rHTR] = 1 THEN 27 WHEN [A].[rHTR] = 2 THEN 10 WHEN [A].[rHTR] = 3 THEN 0 WHEN [A].[rHTR] = 4 THEN -3 WHEN [A].[rHTR] = 5 THEN -9 WHEN [A].[rHTR] = 6 THEN -11 WHEN [A].[rHTR] = 7 THEN -11 WHEN [A].[rHTR] = 8 THEN -12 WHEN [A].[rHTR] = 9 THEN -14 ELSE -15 END
) AS calc

这三个方案都能解决你的问题，其中CTE和CROSS APPLY的代码可读性更好，适合后续维护～

备注：内容来源于stack exchange，提问作者user3229421