Mysql 按小时分组不适用于周范围

Question

我在数据库中有时间范围:

date                   temp
2014-05-09 20:40:01    19.6875
2014-05-09 20:50:01    19.375
.....................
2014-05-10 00:10:01    17.5
........................
2014-05-23 08:25:01    27.4375

我想按小时获取周组的所有 AVG 温度。这里的sql查询:

SELECT AVG( `temp` ) , `date` FROM `temperature` WHERE `date` BETWEEN '2014-05-16 11:06' AND '2014-05-23 11:06' GROUP BY HOUR( `date` )

但结果我只有范围从 2014-05-16 23:06:02 到 2014-05-17 00:05:01

的值

并且没有错误。

帮我找出错误。

抱歉我的英语不好

Answer 1

这是因为当您使用 GROUP BY 并显示未分组的字段时，mysql 无法向您显示该字段的所有值。所以它只显示一个。

这个查询更有意义:

SELECT AVG( `temp` ) , HOUR(`date`) FROM `temperature` WHERE `date` BETWEEN '2014-05-16 11:06' AND '2014-05-23 11:06' GROUP BY HOUR( `date` )

或者这个(经过讨论)

SELECT AVG( temp ) ,
    YEAR(date),
    MONTH(date),
    DAY(date),
    HOUR(date)
FROM temperature
WHERE date BETWEEN '2014-05-16 11:06' AND '2014-05-23 11:06'
GROUP BY YEAR(date),MONTH(date),DAY(date),HOUR(date)