COUNT(*) 不与 group by 一起工作?

本文介绍了COUNT(*) 不与 group by 一起工作?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

为什么以下查询在 MySQL 5.6 中在空数据库上返回 NO RESULT ?

Why the following query returns NO RESULT in MySQL 5.6 on empty database ?

SELECT COUNT(*) FROM a_sec_banns WHERE ip = 'not-exist-ip' GROUP BY ip HAVING max(date_created) <= '10-10-2014' or count(*) > 3;

表的结构是这样的:

CREATE TABLE `a_sec_banns` ( `ID` bigint(20) NOT NULL AUTO_INCREMENT, `DATE_CREATED` datetime DEFAULT NULL, `IP` varchar(60) NOT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

我希望查询 COUNT(*) 将返回一个数字 > 0，如果没有，则返回 0，如果查询错误则返回异常.但绝不是 NULL - COUNT 的 NULL/NO 结果目的是什么???

I would expect the query COUNT(*) will return a number > 0, 0 if nothing, Exception if query is wrong. But never NULL - what is the NULL/NO result purpose on COUNT ???

推荐答案

这是查询:

SELECT COUNT(*) FROM a_sec_banns WHERE ip = 'not-exist-ip' GROUP BY ip HAVING max(date_created) <= '10-10-2014' or count(*) > 3;

如果您只在空数据库上运行第一部分:

If you ran just the first part on an empty database:

SELECT COUNT(*) FROM a_sec_banns WHERE ip = 'not-exist-ip' GROUP BY ip ;

您将获得值为 0 的一行.

You would get one row with the value of 0.

但是，sharing 子句过滤掉了这一行.max(date_created) 将是 NULL 并且 count(*) 将是 0，所以这两个条件都会失败.

However, the having clause filters out this one row. max(date_created) would be NULL and the count(*) would be 0, so both conditions would fail.

顺便说一下，您应该使用 ISO 标准日期格式.因此，have 子句应为:

By the way, you should use ISO standard date formats. So, the having clause should read:

HAVING max(date_created) <= '2014-10-10' or count(*) > 3;

当表格确实有数据时，这将有所帮助.

This will help when the table actually does have data.

这是 count() 的范围问题，也就是计算什么.使用聚合数据集 (GROUP BY..) 时，对聚合内的计数进行计数 - 在您的情况下，没有聚合，因此没有计数结果.在简单的 select..where.. 上使用 count() 时，它会计算整个查询的结果，因此即使是空的结果集也会被计算为 0

EDITED: it is a matter of the scope of count(), of what gets counted. When using aggregated datasets, (GROUP BY..), count counts within the aggregates - in your case there are no aggregates, so there are no count results. When using count() on a simple select..where.., it counts the results of the whole query, so even an empty resultset get counted as 0