SQL查询中WHERE子句使用自定义列别名提示无效列名问题

问题原因 #

SQL的执行逻辑顺序和语法书写顺序不一致，WHERE 子句的执行优先级高于 SELECT 子句。你在 SELECT 中才定义的别名 PurchaseType，在执行 WHERE 过滤阶段还未生成，因此会触发列不存在的报错。

解决方案 #

方案1：将CASE判断逻辑直接复用在WHERE子句中 #

无需调整整体查询结构，适合逻辑简单的场景：

SELECT
    InvoiceDate,
    BillingAddress,
    BillingCity,
    Total,
    CASE
        WHEN Total < 2.00 THEN 'Baseline Purchase'
        WHEN Total BETWEEN 2.00 AND 6.99 THEN 'Low Purchase'
        WHEN Total BETWEEN 7.00 AND 15.00 THEN 'Target Purchase'
        ELSE 'Top Performer'
    END AS PurchaseType
FROM
    invoices
WHERE 
    CASE
        WHEN Total < 2.00 THEN 'Baseline Purchase'
        WHEN Total BETWEEN 2.00 AND 6.99 THEN 'Low Purchase'
        WHEN Total BETWEEN 7.00 AND 15.00 THEN 'Target Purchase'
        ELSE 'Top Performer'
    END = 'Top Performer'
ORDER BY
    BillingCity

方案2：使用子查询嵌套 #

把带别名的查询封装为子查询，在外层做过滤，无需重复写CASE逻辑，可读性更强：

SELECT * FROM (
    SELECT
        InvoiceDate,
        BillingAddress,
        BillingCity,
        Total,
        CASE
            WHEN Total < 2.00 THEN 'Baseline Purchase'
            WHEN Total BETWEEN 2.00 AND 6.99 THEN 'Low Purchase'
            WHEN Total BETWEEN 7.00 AND 15.00 THEN 'Target Purchase'
            ELSE 'Top Performer'
        END AS PurchaseType
    FROM invoices
) AS invoice_with_type
WHERE PurchaseType = 'Top Performer'
ORDER BY BillingCity

方案3：使用CTE（公共表表达式） #

逻辑和子查询类似，结构更清晰，适合多步拼接的复杂查询场景：

WITH invoice_with_type AS (
    SELECT
        InvoiceDate,
        BillingAddress,
        BillingCity,
        Total,
        CASE
            WHEN Total < 2.00 THEN 'Baseline Purchase'
            WHEN Total BETWEEN 2.00 AND 6.99 THEN 'Low Purchase'
            WHEN Total BETWEEN 7.00 AND 15.00 THEN 'Target Purchase'
            ELSE 'Top Performer'
        END AS PurchaseType
    FROM invoices
)
SELECT * FROM invoice_with_type
WHERE PurchaseType = 'Top Performer'
ORDER BY BillingCity

额外说明：ORDER BY 子句的执行优先级低于SELECT，因此你直接在ORDER BY中使用PurchaseType别名不会报错，只有WHERE、GROUP BY、HAVING这些执行顺序早于SELECT的子句，不能直接调用SELECT中定义的别名。

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