我有一个名为"counter"的表,它有3个字段:
| Field | Type | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| record | datetime | YES | | NULL | |
| passed | tinyint(1) | YES | | NULL | |
+--------+------------+------+-----+---------+----------------+
我存储的数据是这样的
mysql> SELECT record, passed FROM counter;
+---------------------+--------+
| record | passed |
+---------------------+--------+
| 2019-09-19 00:00:00 | 1 |
我想做的是一个查询来获取特定日期的结果。
我使用了那个查询:
SELECT DATE_FORMAT(record, '%d-%m-%Y %H') AS ts,
COUNT(*) AS passed
FROM counter
GROUP BY ts
HAVING ts = 09;
问题是,如果数据库上有几个月,它将返回每月的 09 天。
+---------------+--------+
| ts | passed |
+---------------+--------+
| 09-10-2019 10 | 593 |
| 09-10-2019 11 | 667 |
| 09-10-2019 12 | 367 |
| 09-10-2019 13 | 35 |
+---------------+--------+
我想得到一个确切的日期,我试图输入确切的日期
SELECT DATE_FORMAT(record, '%d-%m-%Y %H') AS ts,
COUNT(*) AS passed
FROM counter
WHERE 09-10-2019;
但是我收到一个错误
错误 1140 (42000(:在没有 GROUP BY 的聚合查询中,SELECT 列表的表达式 #1 包含非聚合列 'iot.counter.record';这与 sql_mode=only_full_group_by 不兼容
如何解决问题?
问题是您在日期格式中包含小时,这意味着每天最多有 24 个子组......一天中每小时一个。删除%H
并修复WHERE
子句:
SELECT DATE_FORMAT(record, '%d-%m-%Y') AS ts, COUNT(*)
FROM counter
WHERE record >= '2019-10-09'
AND record < '2019-10-09' + INTERVAL 1 DAY
GROUP BY DATE_FORMAT(record, '%d-%m-%Y')
如果您只想获取当前日期第 9 小时的数据,则实际上不需要考虑全天的数据,然后使用 having 子句进行聚合和过滤。这是不必要的低效,因为数据树查找增加以及临时表大小增加(最终会被丢弃(。
相反,您可以只使用WHERE
子句来过滤掉数据并计数:
SELECT COUNT(*)
FROM counter
WHERE record >= '2019-10-09 09:00:00'
AND record < '2019-10-09 10:00:00'
上述查询将从(record)
列的覆盖指数中受益匪浅。