在表MySQL中查找偶数值

In MySQL, i have a table with a column full of positive integers and i want to filter out all the odd integers. It seems like there is nothing in the MySQL documentation. I tried the following query.

在MySQL中,我有一个表格,其中一列充满正整数,我想过滤掉所有奇数整数。似乎MySQL文档中没有任何内容。我尝试了以下查询。

select kapsule.owner_name, 
       kapsule.owner_domain, 
       count(xform_action) 
  from kapsule, rec_xform 
 where rec_xform.g_conf_id=kapsule.g_conf_id 
   and (count(xform_action))%2=0 
 group by kapsule.owner_name;

I want to keep only those values where count(xform_action) is even. The table looks like this.

我想只保留count(xform_action)是偶数的那些值。表格看起来像这样。

2 个解决方案

#1

To filter out resultset after GROUP BY you need to use HAVING clause. WHERE clause is used to filter source rows before GROUP BY occurs.

要在GROUP BY之后过滤掉结果集,您需要使用HAVING子句。 WHERE子句用于在GROUP BY发生之前过滤源行。

Try

SELECT k.owner_name, 
       k.owner_domain, 
       COUNT(x.xform_action) cnt -- < you probably meant to use SUM() instead of COUNT() here
  FROM kapsule k JOIN rec_xform x -- < use JOIN notation for clarity
    ON x.g_conf_id = k.g_conf_id 
 GROUP BY k.owner_name
HAVING cnt % 2 = 0

You probably meant to use SUM() (sums values of a column of all rows in a group) aggregate instead of COUNT() (returns number of rows in a group)

您可能打算使用SUM()(组中所有行的列的值的总和)聚合而不是COUNT()(返回组中的行数)

Here is SQLFiddle demo (for both SUM() and COUNT())

这是SQLFiddle演示(对于SUM()和COUNT())

#2

For aggregate functions like COUNT(*) using GROUP BY you need to use HAVING clause

对于使用GROUP BY的COUNT(*)等聚合函数,您需要使用HAVING子句

select kapsule.owner_name, kapsule.owner_domain, 
count(xform_action) from kapsule,   rec_xform 
where rec_xform.g_conf_id=kapsule.g_conf_id and 
group by kapsule.owner_name, kapsule.owner_domain
HAVING (count(xform_action))%2=0

or you could use alias (i.e. AS) like:

或者你可以使用别名(即AS),如:

select kapsule.owner_name, kapsule.owner_domain, 
count(xform_action) count_form from kapsule,   rec_xform 
where rec_xform.g_conf_id=kapsule.g_conf_id and 
group by kapsule.owner_name, kapsule.owner_domain
HAVING count_form%2=0

And you could use JOIN as more efficient than the old one of joining tables. And by the way if you have GROUP BY the fields before the aggregate function should be in GROUP BY like:

并且您可以使用JOIN比连接表的旧效率更高效。顺便说一句,如果你有聚合函数应该在GROUP BY之前的字段GROUP BY像:

select kapsule.owner_name, kapsule.owner_domain, 
count(xform_action) count_form from kapsule A
INNER JOIN rec_xform B
ON A.g_conf_id=B.g_conf_id and 
GROUP BY by A.owner_name, A.owner_domain
HAVING count_form%2=0

See examples here

见这里的例子

#1