MySQL 如何避免错误“WHERE 中不允许使用聚合函数”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20991729/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to avoid error "aggregate functions are not allowed in WHERE"
提问by D-Lef
This sql code throws an
这个 sql 代码抛出一个
aggregate functions are not allowed in WHERE
WHERE 中不允许使用聚合函数
SELECT o.ID , count(p.CAT)
FROM Orders o
INNER JOIN Products p ON o.P_ID = p.P_ID
WHERE count(p.CAT) > 3
GROUP BY o.ID;
How can I avoid this error?
我怎样才能避免这个错误？
回答by Aziz Shaikh
Replace WHEREclause with HAVING, like this:
用 替换WHERE子句HAVING，像这样：
SELECT o.ID , count(p.CAT)
FROM Orders o
INNER JOIN Products p ON o.P_ID = p.P_ID
GROUP BY o.ID
HAVING count(p.CAT) > 3;
HAVINGis similar to WHERE, that is both are used to filter the resulting records but HAVINGis used to filter on aggregated data (when GROUP BYis used).
HAVING类似于WHERE，即两者都用于过滤结果记录，但HAVING用于过滤聚合数据（何时GROUP BY使用）。
回答by Saharsh Shah
Use HAVINGclause instead of WHERE
使用HAVING子句代替WHERE
Try this:
尝试这个：
SELECT o.ID, COUNT(p.CAT) cnt
FROM Orders o
INNER JOIN Products p ON o.P_ID = p.P_ID
GROUP BY o.ID HAVING cnt > 3
回答by user7872783
Will self join will go for a toss with join where we have a condition to list prices that are greater than the median of the prices listed in the order table?
在我们有条件列出大于订单表中所列价格中位数的价格时，self join 是否会与 join 折腾？
Eg. order_item, Order_Price
例如。订单商品，订单价格
Since aggregate functions cannot be used in a WHERE clause >
由于不能在 WHERE 子句中使用聚合函数 >
select a.order_item_product_price, count(distinct
a.order_item_product_price) from
default.order_items a , default.order_items b
where a.order_item_product_price = b.order_item_product_price
group by a.order_item_product_price
having a.order_item_product_price > appx_median(b.order_item_product_price)
order by a.order_item_product_price limit 10
