mysql - 在 MySQL 的特定时间段内获取最大值

Question

我有一个这样的表(tblFuel):

time                    fuel
2014-11-04 17:11:08     231
2014-11-04 17:34:16     254
2014-11-04 18:03:48     241
2014-11-04 18:41:34     137
2014-11-04 18:43:42     111

现在我希望在每 1 小时内显示最大的燃料值。例如:max 从 17:00:00 到 17:59:59 等等。并按照前面的要求，预期的结果应该是:

time                    fuel
2014-11-04 17:34:16     254
2014-11-04 18:03:48     241

那么我应该怎么做才能达到这个结果呢？

Answer 1

create table tblFuel (time timestamp, fuel int);

insert into tblFuel values ('2014-11-04 17:11:08', 231);
insert into tblFuel values ('2014-11-04 17:34:16', 254);
insert into tblFuel values ('2014-11-04 18:03:48', 241);
insert into tblFuel values ('2014-11-04 18:41:34', 137);
insert into tblFuel values ('2014-11-04 18:43:42', 111);

select
   *
from tblFuel
where concat(date(time), hour(time), fuel) in
    (select
       concat(date(time), hour(time), max(fuel))
     from tblFuel
     group by
        date(time),
        hour(time))

返回:

time                 fuel
2014-11-04 17:34:16  254
2014-11-04 18:03:48  241