帮助选择计数查询

Question

我想显示 count > 的所有城市5.。我试图将我的结果限制为超过 5，但它不起作用。

SELECT
user.city,
Count(user.city) AS cnt
FROM user
Inner Join zip ON zip.zip = user.zip
WHERE cnt > 5
GROUP BY user.city
WHERE cnt > 5 **<--------------- It fails here**

cnt 已经在字段列表中定义了，为什么不起作用？

Answer 1

您必须使用 having cnt > 4 当您分组

http: //dev.mysql.com/doc/refman/5.0/en/group-by-hidden-columns.html

Answer 2

尝试拥有

SELECT  user.city,
        COUNT(user.city) AS cnt
FROM user
INNER JOIN zip ON zip.zip = user.zip
GROUP BY user.city
HAVING COUNT(user.city) > 5

Answer 3

尝试使用 HAVING 语句：

例如：

select title, AVG(salary)
from employee_data
GROUP BY title 
HAVING AVG(salary) > 100000;