SQL中WHERE子句无法使用别名Small_BagsPercent筛选的问题求助

你遇到的报错是因为SQL的执行顺序规则：数据库会先执行FROM和WHERE子句筛选数据，之后才会执行SELECT子句生成列和别名。所以在WHERE阶段，Small_BagsPercent这个别名还没被创建，自然会提示“未识别名称”。

不需要纠结必须用子查询，有三种常用解决方式：

方式1：重复计算表达式 #

直接在WHERE里写完整的计算逻辑，代替别名：

SELECT
    Date,
    Region,
    Total_Bags,
    Small_Bags,
    (Small_Bags / Total_Bags) * 100 AS Small_BagsPercent
FROM sql-data-analys-corso.avocado_dataset.avocado_prices
WHERE Total_Bags <> 0
  AND (Small_Bags / Total_Bags) * 100 > 10
ORDER BY Small_BagsPercent

方式2：使用子查询 #

把原查询的结果作为临时表，在外层筛选别名：

SELECT *
FROM (
    SELECT
        Date,
        Region,
        Total_Bags,
        Small_Bags,
        (Small_Bags / Total_Bags) * 100 AS Small_BagsPercent
    FROM sql-data-analys-corso.avocado_dataset.avocado_prices
    WHERE Total_Bags <> 0
) AS temp
WHERE Small_BagsPercent > 10
ORDER BY Small_BagsPercent

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

比子查询更易读，适合复杂逻辑：

WITH avocado_calculations AS (
    SELECT
        Date,
        Region,
        Total_Bags,
        Small_Bags,
        (Small_Bags / Total_Bags) * 100 AS Small_BagsPercent
    FROM sql-data-analys-corso.avocado_dataset.avocado_prices
    WHERE Total_Bags <> 0
)
SELECT *
FROM avocado_calculations
WHERE Small_BagsPercent > 10
ORDER BY Small_BagsPercent

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