SQL Server提示列名PREMIUM无效，但该列已在SELECT中定义

SQL Server的查询执行顺序中，HAVING子句的执行早于SELECT子句。这意味着当HAVING进行过滤时，SELECT里定义的别名PREMIUM还未被解析，所以会抛出“列名无效”的错误。而Snowflake的查询解析器允许在HAVING中直接引用SELECT的别名，这是两者语法规则的差异。

以下是几种可行的修改方式：

方案1：在HAVING中重复聚合表达式 #

直接把SELECT里定义PREMIUM的表达式复制到HAVING中：

SELECT QUIKPLAN.FORMS, 
       COALESCE(NEWQUIKVALF1.MVALDATE, '2022-09-30') AS ReportDate, 
       ISNULL((SUM(NEWQUIKVALF1.MANNLZD)/12),0) AS PREMIUM
FROM QUIKPLAN
LEFT JOIN dbo.NEWQUIKVALF1 ON QUIKPLAN.NEWPLAN=NEWQUIKVALF1.NPLAN
GROUP BY QUIKPLAN.FORMS, NEWQUIKVALF1.MVALDATE
HAVING QUIKPLAN.FORMS='L-4054' AND ISNULL((SUM(NEWQUIKVALF1.MANNLZD)/12),0) > 1

方案2：使用子查询/CTE提前计算聚合值 #

先通过子查询或CTE计算出包含PREMIUM的结果集，再在外层进行过滤：

子查询版本 #

SELECT *
FROM (
    SELECT QUIKPLAN.FORMS, 
           COALESCE(NEWQUIKVALF1.MVALDATE, '2022-09-30') AS ReportDate, 
           ISNULL((SUM(NEWQUIKVALF1.MANNLZD)/12),0) AS PREMIUM
    FROM QUIKPLAN
    LEFT JOIN dbo.NEWQUIKVALF1 ON QUIKPLAN.NEWPLAN=NEWQUIKVALF1.NPLAN
    GROUP BY QUIKPLAN.FORMS, NEWQUIKVALF1.MVALDATE
) AS temp
WHERE temp.FORMS='L-4054' AND temp.PREMIUM > 1

CTE版本 #

WITH AggregatedData AS (
    SELECT QUIKPLAN.FORMS, 
           COALESCE(NEWQUIKVALF1.MVALDATE, '2022-09-30') AS ReportDate, 
           ISNULL((SUM(NEWQUIKVALF1.MANNLZD)/12),0) AS PREMIUM
    FROM QUIKPLAN
    LEFT JOIN dbo.NEWQUIKVALF1 ON QUIKPLAN.NEWPLAN=NEWQUIKVALF1.NPLAN
    GROUP BY QUIKPLAN.FORMS, NEWQUIKVALF1.MVALDATE
)
SELECT *
FROM AggregatedData
WHERE FORMS='L-4054' AND PREMIUM > 1

方案3：使用CROSS APPLY计算别名 #

通过CROSS APPLY在分组后计算别名，再在HAVING中引用：

SELECT QUIKPLAN.FORMS, 
       COALESCE(NEWQUIKVALF1.MVALDATE, '2022-09-30') AS ReportDate, 
       calc.PREMIUM
FROM QUIKPLAN
LEFT JOIN dbo.NEWQUIKVALF1 ON QUIKPLAN.NEWPLAN=NEWQUIKVALF1.NPLAN
CROSS APPLY (
    SELECT ISNULL((SUM(NEWQUIKVALF1.MANNLZD)/12),0) AS PREMIUM
) AS calc
GROUP BY QUIKPLAN.FORMS, NEWQUIKVALF1.MVALDATE, calc.PREMIUM
HAVING QUIKPLAN.FORMS='L-4054' AND calc.PREMIUM > 1

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