MySQL - 在WHERE子句中使用COUNT(*)

我试图在MySQL中完成以下内容(请参阅pseudo代码)

SELECT DISTINCT gid
FROM `gd`
WHERE COUNT(*) > 10
ORDER BY lastupdated DESC

有没有办法在不使用WHERE子句中的(SELECT ...)的情况下执行此操作,因为这看起来像是浪费资源.

试试这个;

select gid
from `gd`
group by gid 
having count(*) > 10
order by lastupdated desc

我不确定你要做什么......也许是这样的

SELECT gid, COUNT(*) AS num FROM gd GROUP BY gid HAVING num > 10 ORDER BY lastupdated DESC

SELECT COUNT(*)
FROM `gd`
GROUP BY gid
HAVING COUNT(gid) > 10
ORDER BY lastupdated DESC;

编辑(如果你只是想要gids):

SELECT MIN(gid)
FROM `gd`
GROUP BY gid
HAVING COUNT(gid) > 10
ORDER BY lastupdated DESC

尝试

SELECT DISTINCT gid
FROM `gd`
group by gid
having count(*) > 10
ORDER BY max(lastupdated) DESC

只是没有条款的学术版:

select *
from (
   select gid, count(*) as tmpcount from gd group by gid
) as tmp
where tmpcount > 10;

A 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;

- 搜索缺少半小时记录的气象站

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;

- yapiskan的变化与where .. in .. select