MySQL - 在 WHERE 子句中使用 COUNT(*)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/301793/
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
MySQL - Using COUNT(*) in the WHERE clause
提问by Ali Ers?z
I am trying to accomplish the following in MySQL (see pseudocode)
我正在尝试在 MySQL 中完成以下操作（见pseudo代码）
SELECT DISTINCT gid
FROM `gd`
WHERE COUNT(*) > 10
ORDER BY lastupdated DESC
Is there a way to do this without using a (SELECT...) in the WHERE clause because that would seem like a waste of resources.
有没有办法在 WHERE 子句中不使用 (SELECT...) 来做到这一点，因为这似乎是一种资源浪费。
回答by Ali Ers?z
try this;
尝试这个;
select gid
from `gd`
group by gid
having count(*) > 10
order by lastupdated desc
回答by Greg
I'm not sure about what you're trying to do... maybe something like
我不确定你想要做什么......也许像
SELECT gid, COUNT(*) AS num FROM gd GROUP BY gid HAVING num > 10 ORDER BY lastupdated DESC
回答by Winston Smith
SELECT COUNT(*)
FROM `gd`
GROUP BY gid
HAVING COUNT(gid) > 10
ORDER BY lastupdated DESC;
EDIT (if you just want the gids):
编辑（如果你只想要 gids）：
SELECT MIN(gid)
FROM `gd`
GROUP BY gid
HAVING COUNT(gid) > 10
ORDER BY lastupdated DESC
回答by Má?a - Stitod.cz
Just academic version without having clause:
只是没有条款的学术版本：
select *
from (
select gid, count(*) as tmpcount from gd group by gid
) as tmp
where tmpcount > 10;
回答by sme
try
尝试
SELECT DISTINCT gid
FROM `gd`
group by gid
having count(*) > 10
ORDER BY max(lastupdated) DESC
回答by pushkarr
There can't be aggregate functions (Ex. COUNT, MAX, etc.) in A WHERE clause. Hence we use the HAVING clause instead. Therefore the whole query would be similar to this:
WHERE 子句中不能有聚合函数（例如 COUNT、MAX 等）。因此，我们改用 HAVING 子句。因此，整个查询将类似于：
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;
回答by zzapper
-- searching for weather stations with missing half-hourly records
-- 搜索缺少半小时记录的气象站
SELECT stationid
FROM weather_data
WHERE `Timestamp` LIKE '2011-11-15 %' AND
stationid IN (SELECT `ID` FROM `weather_stations`)
GROUP BY stationid
HAVING COUNT(*) != 48;
-- variation of yapiskan with a where .. in .. select
-- yapiskan 的变体，带有 where .. in .. select
回答by Tushar Pandey
i think you can not add count()with where. now see why ....
我想你不能添加count()使用where。现在看看为什么....
whereis not same as having, havingmeans you are working or dealing with group and same work of count , it is also dealing with the whole group ,
where不一样having，having意味着你正在工作或处理组和计数的相同工作，它也在处理整个组，
now how count it is working as whole group
现在如何计数它作为整个组的工作
create a table and enter some id's and then use:
创建一个表并输入一些 ID，然后使用：
select count(*) from table_name
you will find the total values means it is indicating some group ! so wheredoes added with count();
您会发现总值意味着它表示某个组！所以where不添加count();
回答by Mridul Pandey
COUNT(*) can only be used with HAVING and must be used after GROUP BY statement Please find the following example:
COUNT(*) 只能与 HAVING 一起使用，并且必须在 GROUP BY 语句之后使用 请找到以下示例：
SELECT COUNT(*), M_Director.PID FROM Movie
INNER JOIN M_Director ON Movie.MID = M_Director.MID
GROUP BY M_Director.PID
HAVING COUNT(*) > 10
ORDER BY COUNT(*) ASC
