使用HAVING子句时遭遇Column does not exist错误求助

核心原因 #

SQL的执行顺序是：FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY。HAVING子句执行时，SELECT里定义的别名Quantity_Counts还没被创建，所以识别不了；而ORDER BY是最后执行的，这时候别名已经存在，所以能正常使用。

两种修改方案 #

1. 直接在HAVING里使用聚合函数
   把HAVING里的别名替换成原始的聚合表达式：

SELECT Quantity, COUNT(*) AS Quantity_Counts
FROM sqlbank3
WHERE UnitPrice >= 5
GROUP BY Quantity
HAVING COUNT(*) < 450
ORDER BY Quantity_Counts DESC
LIMIT 10;

2. 用子查询包装结果后过滤
   如果聚合逻辑复杂，也可以先把分组结果查出来，再在外层过滤：

SELECT *
FROM (
    SELECT Quantity, COUNT(*) AS Quantity_Counts
    FROM sqlbank3
    WHERE UnitPrice >= 5
    GROUP BY Quantity
) AS grouped_data
WHERE Quantity_Counts < 450
ORDER BY Quantity_Counts DESC
LIMIT 10;

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