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

我试图在 MySQL 中完成以下任务(参见 pseudo代码)

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

有没有办法不在 WHERE 子句中使用(SELECT...)来实现这一点，因为这看起来像是在浪费资源。

353716 次浏览
小开 
SELECT COUNT(*)
FROM `gd`
GROUP BY gid
HAVING COUNT(gid) > 10
ORDER BY lastupdated DESC;

编辑(如果你只想要 GID) :

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

试试这个

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 DISTINCT gid
FROM `gd`
group by gid
having count(*) > 10
ORDER BY max(lastupdated) DESC
小开

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

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;

--雅皮斯坎的变体，带有一个 where. . in. . select

小开

我认为你不能在 where中加入 count()。现在看看为什么... 。

where不同于 having，having意味着你在工作或处理小组和同样的计数工作，它也处理整个小组,

现在怎么计算它是作为一个整体工作

创建一个表，输入一些 id，然后使用:

select count(*) from table_name

你会发现总值意味着它是指示一些组! 所以 where确实增加了 count();

小开

只是没有条款的学术版本:

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

不能有聚合函数(Ex。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;
小开

COUNT (*)只能与 HAVING 一起使用，并且必须在 GROUPBY 之后使用 请参考以下例子:

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

提交答案