blocks|key|1686767|text|试试这个；|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1686768|select+gid
from+`gd`
group+by+gid+
having+count(*)+>+10
order+by+lastupdated+desc|code-block|syntax|javascript|1686769|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

try this;

<pre><code>select gid
from `gd`
group by gid 
having count(*) &gt; 10
order by lastupdated desc
</code></pre>

blocks|key|1686800|text|我不知道你到底想做什么..。也许是像这样的|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1686801|SELECT+gid,+COUNT(*)+AS+num+FROM+gd+GROUP+BY+gid+HAVING+num+>+10+ORDER+BY+lastupdated+DESC|code-block|syntax|javascript|1686802|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I'm not sure about what you're trying to do... maybe something like

<pre><code>SELECT gid, COUNT(*) AS num FROM gd GROUP BY gid HAVING num &gt; 10 ORDER BY lastupdated DESC
</code></pre>

blocks|key|1686755|text|SELECT+COUNT(*)
FROM+`gd`
GROUP+BY+gid
HAVING+COUNT(gid)+>+10
ORDER+BY+lastupdated+DESC;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1686756|编辑(如果您只需要gids)：|unstyled|1686757|SELECT+MIN(gid)
FROM+`gd`
GROUP+BY+gid
HAVING+COUNT(gid)+>+10
ORDER+BY+lastupdated+DESC|1686758|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|L|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$B|C]]|$1|I|3|-4|5|F|7|N|8|@]|9|@]|A|$]]]|J|$]]

<pre><code>SELECT COUNT(*)
FROM `gd`
GROUP BY gid
HAVING COUNT(gid) &gt; 10
ORDER BY lastupdated DESC;
</code></pre>

EDIT (if you just want the gids):

<pre><code>SELECT MIN(gid)
FROM `gd`
GROUP BY gid
HAVING COUNT(gid) &gt; 10
ORDER BY lastupdated DESC
</code></pre>

blocks|key|1471755|text|只是学术版，没有having子句：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1471756|select+*
from+(
+++select+gid,+count(*)+as+tmpcount+from+gd+group+by+gid
)+as+tmp
where+tmpcount+>+10;|code-block|syntax|javascript|1471757|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Just academic version without having clause:

<pre><code>select *
from (
 select gid, count(*) as tmpcount from gd group by gid
) as tmp
where tmpcount &gt; 10;
</code></pre>

blocks|key|1471639|text|试一试|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1471640|SELECT+DISTINCT+gid
FROM+`gd`
group+by+gid
having+count(*)+>+10
ORDER+BY+max(lastupdated)+DESC|code-block|syntax|javascript|1471641|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

try

<pre><code>SELECT DISTINCT gid
FROM `gd`
group by gid
having count(*) &gt; 10
ORDER BY max(lastupdated) DESC
</code></pre>

blocks|key|1471785|text|不能有聚合函数(例如计数、最大值等)在WHERE子句中。因此，我们改用HAVING子句。因此，整个查询将类似于以下内容：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1471786|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;|code-block|syntax|javascript|1471787|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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:

<pre><code>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;
</code></pre>

blocks|key|1471684|text|--搜索缺少半小时记录的气象站|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1471685|SELECT+stationid
FROM+weather_data+
WHERE++`Timestamp`+LIKE+'2011-11-15+%25'++AND+
stationid+IN+(SELECT+`ID`+FROM+`weather_stations`)
GROUP+BY+stationid+
HAVING+COUNT(*)+!=+48;|code-block|syntax|javascript|1471686|--+yapiskan的变种..在..选择|1471687|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

-- searching for weather stations with missing half-hourly records

<pre><code>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;
</code></pre>

-- variation of yapiskan with a where .. in .. select

blocks|key|1686896|text|我认为你不能用where来添加count()。现在明白为什么..。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1686897|where和having不一样，having指的是你在工作或者在处理组和count的相同工作，它也是在处理整个组。|1686898|现在，它是如何作为整个组工作的|1686899|创建一个表并输入一些id，然后使用：|1686900|select+count(*)+from+table_name|code-block|syntax|javascript|1686901|你会发现合计的值意味着它表示某个组！所以在count()中添加了where；|1686902|entityMap^0|7|5|F|7|0|0|5|6|6|G|6|0|0|0|0|L|7|W|5|0^^$0|@$1|2|3|4|5|6|7|U|8|@$9|V|A|W|B|C]|$9|X|A|Y|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|Z|8|@$9|10|A|11|B|C]|$9|12|A|13|B|C]|$9|14|A|15|B|C]]|D|@]|E|$]]|$1|H|3|I|5|6|7|16|8|@]|D|@]|E|$]]|$1|J|3|K|5|6|7|17|8|@]|D|@]|E|$]]|$1|L|3|M|5|N|7|18|8|@]|D|@]|E|$O|P]]|$1|Q|3|R|5|6|7|19|8|@$9|1A|A|1B|B|C]|$9|1C|A|1D|B|C]]|D|@]|E|$]]|$1|S|3|-4|5|6|7|1E|8|@]|D|@]|E|$]]]|T|$]]

i think you can not add <code>count()</code> with <code>where</code>. now see why ....

<code>where</code> is not same as <code>having</code> , <code>having</code> 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: 

<pre><code>select count(*) from table_name
</code></pre>

you will find the total values means it is indicating some group ! so <code>where</code> does added with <code>count()</code> ;

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

<pre><code>SELECT DISTINCT gid
FROM `gd`
WHERE COUNT(*) &gt; 10
ORDER BY lastupdated DESC
</code></pre>

Is there a way to do this without using a (SELECT...) in the WHERE clause because that would seem like a waste of resources.

MySQL - Using COUNT(*) in the WHERE clause

MySQL

我正在尝试用MySQL (参见pseudo代码)完成以下工作SELECT DISTINCT gidFROM `gd`WHERE COUNT(*) > 10ORDER BY lastupdated DESC有没有一种方法可以不使用(SELECT...)在WHERE子句中，因为这看起来像是在浪费资源。

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

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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