mysql - 如何避免错误 "aggregate functions are not allowed in WHERE"

Question

这个sql代码抛出一个

aggregate functions are not allowed in 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;

我怎样才能避免这个错误？

Answer 1

用HAVING替换WHERE子句，像这样:

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;

HAVING 与 WHERE 类似，都用于过滤结果记录，但 HAVING 用于过滤聚合数据(当使用 GROUP BY 时)。