mysql - 如何为语句 SELECT COUNT(*) as num... 编写子句 : WHERE num =. ..？

Question

我有声明

SELECT COUNT(*) as num_requests, ip_address FROM requests
GROUP BY ip_address
ORDER BY num_requests DESC;

当我使用以下子句 WHERE num_requests = 3 时出现语法错误

SELECT COUNT(*) as num_requests, ip_address 
FROM requests
WHERE num_requests = 3
GROUP BY ip_address
ORDER BY num_requests DESC;

“where 子句”中的未知列“num_requests”

有什么方法可以使用此 WHERE 子句而不用对上面返回的整个结果集编写嵌套查询或连接语句？

Answer 1

你需要having :)

SELECT COUNT(*) as num_requests, ip_address FROM requests
GROUP BY ip_address
HAVING COUNT(*) = 3
ORDER BY num_requests DESC;

添加关于原因的注释 WHERE无法使用但HAVING : 聚合如 SUM, MIN,MAX,AVG,COUNT等WHERE不工作。所以你必须使用后者。