为何该SQL查询报错？仅查order_id时提示max_quantity列不存在

核心原因 #

你碰到的报错，是因为SQL对HAVING子句可引用列的规则限制：

• 第一个查询仅SELECT order_max.order_id，HAVING里的max_quantity既不在SELECT列表中，也没通过表别名明确归属（比如order_max.max_quantity）。部分数据库（如开启ONLY_FULL_GROUP_BY模式的MySQL）会严格校验这类引用，判定未明确声明的列为“未知列”。
• 第二个查询用SELECT *会自动包含两个CTE的所有列（包括max_quantity和avg_quantity），此时HAVING引用这些列符合规则，因此能正常执行。

另外要注意：你原查询里的GROUP BY order_max.order_id完全多余——order_max和order_avg都是按order_id分组后的结果，JOIN后每个order_id仅对应一行数据，分组操作毫无意义，还可能加剧数据库的列引用校验严格性。还有，原JOIN未加ON条件，会产生笛卡尔积，结果是错误的，必须补上关联条件。

三种修正方案 #

1. 明确指定HAVING中列的表别名

WITH order_max AS
(
    SELECT order_id, MAX(quantity) max_quantity 
    FROM OrdersDetails
    GROUP BY order_id
),
order_avg AS 
( 
    SELECT order_id, SUM(quantity)/COUNT(*) avg_quantity 
    FROM OrdersDetails
    GROUP BY order_id
)
SELECT order_max.order_id
FROM order_max 
JOIN order_avg ON order_max.order_id = order_avg.order_id
HAVING order_max.max_quantity > order_avg.avg_quantity 

2. 将比较列加入SELECT列表

WITH order_max AS
(
    SELECT order_id, MAX(quantity) max_quantity 
    FROM OrdersDetails
    GROUP BY order_id
),
order_avg AS 
( 
    SELECT order_id, SUM(quantity)/COUNT(*) avg_quantity 
    FROM OrdersDetails
    GROUP BY order_id
)
SELECT order_max.order_id, order_max.max_quantity, order_avg.avg_quantity
FROM order_max 
JOIN order_avg ON order_max.order_id = order_avg.order_id
GROUP BY order_max.order_id, order_max.max_quantity, order_avg.avg_quantity
HAVING max_quantity > avg_quantity 

3. 移除多余GROUP BY，改用WHERE过滤
   因为JOIN后每行对应唯一order_id，直接用WHERE更高效：

WITH order_max AS
(
    SELECT order_id, MAX(quantity) max_quantity 
    FROM OrdersDetails
    GROUP BY order_id
),
order_avg AS 
( 
    SELECT order_id, SUM(quantity)/COUNT(*) avg_quantity 
    FROM OrdersDetails
    GROUP BY order_id
)
SELECT order_max.order_id
FROM order_max 
JOIN order_avg ON order_max.order_id = order_avg.order_id
WHERE order_max.max_quantity > order_avg.avg_quantity 

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