SQL创建多列报错：无效列名问题的修复方案咨询

你的SQL报错有两个核心原因：

• 同一个SELECT里不能直接引用刚定义的列别名（比如[CollectiveProvisions]），SQL解析时会先处理所有列的计算逻辑，此时别名还不被视为有效列；
• 缺少GROUP BY [Currency]——你在CASE里用到了非聚合列[Currency]，但同时用了SUM聚合函数，必须按[Currency]分组，才能让聚合结果和对应货币值匹配。

下面是两种可行的修复方式：

方式一：重复聚合表达式 #

直接把SUM([FinalECL]) + SUM([EarlyCorrections])代入CASE语句，同时补上GROUP BY：

SELECT 
    SUM([FinalECL]) + SUM([EarlyCorrections]) AS [CollectiveProvisions],
    CASE
        WHEN [Currency] = 'EUR' THEN SUM([FinalECL]) + SUM([EarlyCorrections])
        WHEN [Currency] = 'DKK' THEN (SUM([FinalECL]) + SUM([EarlyCorrections])) * 0.13
        WHEN [Currency] = 'NOK' THEN (SUM([FinalECL]) + SUM([EarlyCorrections])) * 0.1
        ELSE (SUM([FinalECL]) + SUM([EarlyCorrections])) * 0.094
    END AS [CollProv_New],
    [Currency] -- 可选，需要显示货币类型的话加上
FROM
    CollectiveProvision_Q22022
WHERE 
    [MONTH] IN ('202204')
GROUP BY [Currency]

方式二：用CTE提前计算聚合值 #

通过公共表表达式先算出CollectiveProvisions，外层直接引用，代码更清晰：

WITH ProvisionsData AS (
    SELECT 
        SUM([FinalECL]) + SUM([EarlyCorrections]) AS [CollectiveProvisions],
        [Currency]
    FROM
        CollectiveProvision_Q22022
    WHERE 
        [MONTH] IN ('202204')
    GROUP BY [Currency]
)
SELECT 
    [CollectiveProvisions],
    CASE
        WHEN [Currency] = 'EUR' THEN [CollectiveProvisions]
        WHEN [Currency] = 'DKK' THEN [CollectiveProvisions] * 0.13
        WHEN [Currency] = 'NOK' THEN [CollectiveProvisions] * 0.1
        ELSE [CollectiveProvisions] * 0.094
    END AS [CollProv_New],
    [Currency]
FROM ProvisionsData

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