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

I am trying to accomplish the following in MySQL (see pseudo code)

我正在尝试在MySQL中完成以下操作(参见伪代码)

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…)，因为那样看起来会浪费资源。

8 个解决方案

#1

216

try this;

试试这个;

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

#2

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

#3

try

试一试

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

#4

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

#5

Just academic version without having clause:

只有学术版本没有条款:

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

#6

-- 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的变异。在. .选择

#7

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子句中不能有聚合函数(Ex. COUNT、MAX等)。因此我们用have子句代替。因此，整个查询将与以下内容类似:

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;

#8

i think you can not add count() with where. now see why ....

我认为你不能在其中添加count()。现在明白为什么....

where is not same as having , having means you are working or dealing with group and same work of count , it is also dealing with the whole group ,

与拥有不一样的是，拥有意味着你在工作或处理群体的工作和相同的计数工作，它也在处理整个群体，

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 where does added with count() ;

你会发现总值意味着它表示某个组!那么count()

#1

216