MYSQL max() 和 group by 错误:only_full_group_by

Question

我对更新 MySQL-5.7 以来记录错误的 MySQL 查询有疑问。错误是“only_full_group_by”，它将在 stackoverflow 上被讲出来。

在许多答案中都表示不要禁用此选项，而是改进您的 sql 查询。

我使用的查询返回每小时计数器的最小值和最大值。

SELECT MAX( counter ) AS max, 
       MIN( counter ) AS min, 
       DATE_FORMAT(date_time, '%H:%i') AS dt 
FROM table1 
WHERE date_time >= NOW() - INTERVAL 1 DAY 
GROUP BY YEAR(date_time), MONTH(date_time), DAY(date_time), HOUR(date_time)

据我从错误消息中了解到，我缺少 GROUP BY 原因中 SELECT 原因中的一项。但是，无论我恢复/删除/添加项目，我都没有得到升级到 MySQL-5.7 之前得到的结果。

我尝试对主查询进行子查询以改进 SQL 查询。但不知怎的，我无法重新创建结果。

我缺少什么？

Answer 1

MySQL 无法确定 GROUP BY 子句中的表达式与 SELECT 列表中的表达式之间的函数依赖关系。

SELECT 列表中的非聚合表达式 (DATE_FORMAT(date_time, '%H:%i') 包含分钟部分。GROUP BY 子句将仅按小时将行折叠成组。因此分钟的值是不确定的...我们知道它将来自组中的某些行，但无法保证是哪一行。

(对ONLY_FULL_GROUP_BY的问题引用似乎表明我们对不确定值有了一些了解......)

最简单(最少)的更改修复方法是将表达式包装在 MIN 或 MAX 函数中。

 SELECT MAX(t.counter)                          AS `max`
      , MIN(t.counter)                          AS `min`
      , MIN(DATE_FORMAT(t.date_time,'%H:%i'))   AS `dt`
   FROM table1 t
  WHERE t.date_time >= NOW() - INTERVAL 1 DAY 
  GROUP
     BY YEAR(t.date_time)
      , MONTH(t.date_time)
      , DAY(t.date_time)
      , HOUR(t.date_time)
  ORDER
     BY YEAR(t.date_time)
      , MONTH(t.date_time)
      , DAY(t.date_time)
      , HOUR(t.date_time)

如果我们希望以特定顺序返回行，我们应该包含一个 ORDER BY 子句，而不是依赖于 MySQL 特定的扩展或 GROUP BY 行为(可能会在未来版本中消失。)

<小时/>

执行GROUP BY年、月、日而不将这些值包含在 SELECT 列表中有点奇怪。 (这样做并不是无效，只是有点奇怪。WHERE 子句中的条件保证 date_time 的跨度不超过 24 小时。

我的偏好是对与 SELECT 列表中的非聚合相同的表达式执行GROUP BY。如果我需要超过 24 小时，我会包含日期部分:

 SELECT MAX(t.counter)                             AS `max`
      , MIN(t.counter)                             AS `min`
      , DATE_FORMAT(t.date_time,'%Y-%m-%d %H:00') + INTERVAL 0 DAY AS `dt`
   FROM table1 t
  WHERE t.date_time >= NOW() - INTERVAL 1 DAY 
  GROUP
     BY DATE_FORMAT(t.date_time,'%Y-%m-%d %H:00') + INTERVAL 0 DAY
  ORDER
     BY DATE_FORMAT(t.date_time,'%Y-%m-%d %H:00') + INTERVAL 0 DAY

--或--

如果我们总是知道这只是一天的date_time，并且我们只想返回小时，那么我们可以仅按小时进行分组。与 SELECT 列表中的表达式相同。

 SELECT MAX(t.counter)                             AS `max`
      , MIN(t.counter)                             AS `min`
      , DATE_FORMAT(t.date_time,'%H:00')           AS `dt`
   FROM table1 t
  WHERE t.date_time >= NOW() - INTERVAL 1 DAY 
  GROUP
     BY DATE_FORMAT(t.date_time,'%H:00')
      , DATE_FORMAT(t.date_time,'%Y-%m-%d %H')
  ORDER
     BY DATE_FORMAT(t.date_time,'%Y-%m-%d %H')