MySQL：按日期时间对记录进行分组，忽略“秒”

Question

我有一个包含数千条记录的表，如下所示：

[Row]    ActionTime              Score
=======================================
[#1]   2011-08-06 12:30:15       30
[#2]   2011-08-06 12:30:20       50
[#3]   2011-08-06 12:30:47       40
[#4]   2011-08-06 12:40:12       30
[#5]   2011-08-06 12:40:28       10
[#5]   2011-08-06 12:45:12       60

我想以分钟为单位对数据进行分组，并找到每组的最高分数。

所以结果是这样的：

[Row]  ActionTime (without "second")          Score        
========================================================
[#1]   2011-08-06 12:30:00                     50  
[#2]   2011-08-06 12:40:00                     30
[#3]   2011-08-06 12:45:00                     60

我如何通过MySQL来做到这一点？

谢谢！

Answer 1

select 
date_format(actiontime,'%Y-%m-%d %H:%i:00') as act_time,
max(score) as greater
from table
group by act_time

Answer 2

您不需要选择格式化字段，可以直接在GROUP BY中使用它。

SELECT * FROM table_name
GROUP BY date_format(datetime_field, '%Y-%m-%d %H:%i:00');