SQL分组使用列别名时提示Invalid column name无效列名错误

SQL的逻辑执行顺序为：FROM → JOIN → WHERE → GROUP BY → 聚合计算 → HAVING → SELECT → ORDER BY → LIMIT，GROUP BY的执行优先级早于SELECT子句，因此你在SELECT中定义的别名warehouse_name在GROUP BY执行阶段还未被系统识别，因此会抛出无效列的错误。
另外你代码中存在笔误：总订单的别名拼写为toatl_orders，建议修正为total_orders。

方案1：GROUP BY直接使用原表达式（全数据库兼容） #

直接将SELECT中定义warehouse_name的表达式放到GROUP BY子句中替换别名，所有SQL数据库均支持该写法，修复后的代码如下：

SELECT
    Warehouse.warehouse_id,
    CONCAT(Warehouse.state,':', Warehouse.warehouse_alias) AS warehouse_name,
    COUNT(Orders.order_id) AS number_of_orders,
    (SELECT COUNT(*) FROM Orders) AS total_orders,
    CASE
        WHEN COUNT(Orders.order_id)/(SELECT COUNT(*) FROM Orders) <=0.20
        THEN 'Fulfilled 0-20% of Orders'
        WHEN COUNT(Orders.order_id)/(SELECT COUNT(*) FROM Orders) >0.20
        AND COUNT(Orders.order_id)/(SELECT COUNT(*) FROM Orders) <=0.60
        THEN 'Fulfilled 21-60% of Orders'
        ELSE 'Fulfilled more than 60% of Orders'
    END AS fulfillment_summary
FROM Warehouse
LEFT JOIN Orders
    ON Orders.warehouse_id=Warehouse.warehouse_id
GROUP BY
    Warehouse.warehouse_id,
    CONCAT(Warehouse.state,':', Warehouse.warehouse_alias)
HAVING 
    COUNT(Orders.order_id) >0

方案2：用CTE预定义字段（可读性更高） #

如果要避免重复写相同的表达式，可以用CTE先处理基础字段，再在外层做分组统计，同时可以用窗口函数优化总订单的重复子查询，提升执行效率：

WITH order_total AS (
    SELECT COUNT(*) AS total_orders FROM Orders
),
warehouse_base AS (
    SELECT
        w.warehouse_id,
        CONCAT(w.state,':', w.warehouse_alias) AS warehouse_name,
        o.order_id
    FROM Warehouse w
    LEFT JOIN Orders o ON o.warehouse_id = w.warehouse_id
)
SELECT
    warehouse_id,
    warehouse_name,
    COUNT(order_id) AS number_of_orders,
    (SELECT total_orders FROM order_total) AS total_orders,
    CASE
        WHEN COUNT(order_id)/(SELECT total_orders FROM order_total) <=0.20
        THEN 'Fulfilled 0-20% of Orders'
        WHEN COUNT(order_id)/(SELECT total_orders FROM order_total) >0.20
        AND COUNT(order_id)/(SELECT total_orders FROM order_total) <=0.60
        THEN 'Fulfilled 21-60% of Orders'
        ELSE 'Fulfilled more than 60% of Orders'
    END AS fulfillment_summary
FROM warehouse_base
GROUP BY
    warehouse_id,
    warehouse_name
HAVING COUNT(order_id) >0

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