如何避免错误“在WHERE中不允许聚合函数".

本文介绍了如何避免错误“在WHERE中不允许聚合函数".的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

此sql代码抛出

WHERE中不允许使用汇总函数

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;

如何避免此错误?

推荐答案

用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时).

HAVING is similar to WHERE, that is both are used to filter the resulting records but HAVING is used to filter on aggregated data (when GROUP BY is used).