mysql - 计算登录到 mysql 的条目数

Question

我有一个包含日志条目的数据库，例如:

CREATE TABLE `cheaters` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `date` datetime NOT NULL,
  `guid` int(10) unsigned NOT NULL,
  `type` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `guid` (`guid`)
) ENGINE=InnoDB AUTO_INCREMENT=973997 DEFAULT CHARSET=utf8

示例数据:

mysql> select * from cheaters LIMIT 10;
+----+---------------------+-------+-------------------------------+
| id | date                | guid  | type                          |
+----+---------------------+-------+-------------------------------+
|  1 | 2011-12-15 18:16:16 | 17567 | Speed-Hack detected           |
|  2 | 2011-12-15 18:16:28 | 69460 | Speed-Hack detected           |
|  3 | 2011-12-15 18:16:29 | 82077 | Walk on Water - Hack detected |
|  4 | 2011-12-15 18:16:50 | 55710 | Speed-Hack detected           |
|  5 | 2011-12-15 18:16:50 | 84229 | Speed-Hack detected           |
|  6 | 2011-12-15 18:16:52 | 55848 | Speed-Hack detected           |
|  7 | 2011-12-15 18:16:53 | 48774 | Speed-Hack detected           |
|  8 | 2011-12-15 18:16:54 | 48774 | Speed-Hack detected           |
|  9 | 2011-12-15 18:16:56 | 48092 | Speed-Hack detected           |
| 10 | 2011-12-15 18:16:56 | 81389 | Speed-Hack detected           |
+----+---------------------+-------+-------------------------------+

我想得到这样的东西:

+------------+---------------------+-------+
| DAY        | GUID                | COUNT |
+------------+---------------------+-------+
| 2011-12-15 | 17567               | 356   |
| 2011-12-15 | 69123               | 6     |
....                                    ....
| 2011-12-16 | 69123               | 8     |
....                                    ....

这是每天每个 guid 的条目数。我怎么才能得到它？哪个查询？

谢谢。

Answer 1

select 
date(`date`) as `day`,
guid,
count(*) as total
from cheaters
group by `day`,guid

更新的答案。

您必须在查询的底部添加 having 子句。

select 
date(`date`) as `day`,
guid,
count(*) as total
from cheaters
group by `day`,guid
having total > X