SQL Server中WHERE子句用别名price报错‘Invalid column name’的解决方法

这问题我之前调试SQL时也踩过坑，核心原因是SQL语句的执行顺序：数据库会先处理WHERE子句过滤数据，再执行SELECT里的列计算和别名赋值。你在WHERE里用的price是SELECT里刚定义的别名，这时候数据库还没生成这个列，自然会报错说找不到它。

下面给你两种可行的解决办法：

方法1：在WHERE子句中重复CASE表达式 #

直接把生成price的CASE逻辑复制到WHERE里，这样数据库就能直接计算过滤条件：

SELECT 
    CASE 
        WHEN salePriceDate BETWEEN salePriceStartDate AND salePriceEndDate THEN salePrice 
        ELSE 
            CASE 
                WHEN salePriceDate2 BETWEEN salePriceStartDate2 AND salePriceEndDate2 THEN salePrice2 
                ELSE normalPrice 
            END 
    END AS price 
FROM prices 
WHERE 
    CASE 
        WHEN salePriceDate BETWEEN salePriceStartDate AND salePriceEndDate THEN salePrice 
        ELSE 
            CASE 
                WHEN salePriceDate2 BETWEEN salePriceStartDate2 AND salePriceEndDate2 THEN salePrice2 
                ELSE normalPrice 
            END 
    END BETWEEN 1 AND 100

优点：写法直接，不需要额外嵌套；缺点：如果CASE逻辑复杂，代码会冗余，后期维护要改两处。

方法2：使用子查询/CTE先计算price #

先通过子查询或CTE把price计算出来，再在外层查询里用WHERE过滤，这种方式更清晰易维护：

子查询版本 #

SELECT *
FROM (
    SELECT 
        CASE 
            WHEN salePriceDate BETWEEN salePriceStartDate AND salePriceEndDate THEN salePrice 
            ELSE 
                CASE 
                    WHEN salePriceDate2 BETWEEN salePriceStartDate2 AND salePriceEndDate2 THEN salePrice2 
                    ELSE normalPrice 
                END 
        END AS price 
    FROM prices
) AS sub
WHERE sub.price BETWEEN 1 AND 100

CTE版本（SQL Server支持） #

WITH priceCalculations AS (
    SELECT 
        CASE 
            WHEN salePriceDate BETWEEN salePriceStartDate AND salePriceEndDate THEN salePrice 
            ELSE 
                CASE 
                    WHEN salePriceDate2 BETWEEN salePriceStartDate2 AND salePriceEndDate2 THEN salePrice2 
                    ELSE normalPrice 
                END 
        END AS price 
    FROM prices
)
SELECT *
FROM priceCalculations
WHERE price BETWEEN 1 AND 100

优点：逻辑分离，代码更整洁，维护时只需要改一处CASE逻辑；缺点：多了一层嵌套，但可读性更好，适合复杂场景。

另外，你也可以简化一下嵌套的CASE表达式，写成单层的更清爽：

CASE 
    WHEN salePriceDate BETWEEN salePriceStartDate AND salePriceEndDate THEN salePrice 
    WHEN salePriceDate2 BETWEEN salePriceStartDate2 AND salePriceEndDate2 THEN salePrice2 
    ELSE normalPrice 
END AS price

这样代码更简洁，完全不影响功能~

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