Mysql:获取相应时间的最大值并按天分组

Question

我的 table 是:

timelog              | roomtemp  
2014-07-14 01:16:04  | 25.0    |
2014-07-14 02:14:05  | 26.0    |
2014-07-14 03:13:05  | 25.0    |
2014-07-14 05:13:11  | 29.0    |
2014-07-14 06:15:05  | 25.0    |
2014-07-14 07:17:13  | 30.0    |
       .
       .
2014-07-15 01:13:05  | 25.0    |
2014-07-15 01:16:04  | 31.0    |
2014-07-15 02:14:05  | 25.0    |
2014-07-15 03:13:05  | 28.0    |
2014-07-15 05:13:05  | 25.0    |

我想获得 max(roomtemp) 和每天相应的时间日志比如:

2014-07-14 07:17:13  | 30.0    |
2014-07-15 01:16:04  | 31.0    |

2014-07-14我累了，但是失败了~:

$sql="SELECT timelog, MAX(roomtemp) FROM $tablename WHERE timelog BETWEEN '2014-07-14 00:00:00' AND '2014-07-14 23:59:59' ";<p>
$result=mysqli_query($db, $sql) or die("ERR201: Error");

显示错误信息

如果我在 SELECT 之后删除时间日志，我可以获得最大室温

$sql="SELECT MAX(roomtemp) FROM $tablename WHERE timelog BETWEEN '2014-07-14 00:00:00' AND '2014-07-14 23:59:59' ";<p>
$result=mysqli_query($db, $sql) or die("ERR201: Error");

我也尝试过按 DAY(timelog) 分组，但是 timelog 值不正确...

Answer 1

查询当天的日期和最高温度:

select date(timelog) as day,max(roomtemp) from table_name group by day;

输出:

查询获取一天的时间和最高温度:

SELECT timelog AS curr_day,MAX(roomtemp) FROM (SELECT * FROM day_temperature ORDER BY roomtemp DESC) c GROUP BY DATE(curr_day);

输出:

SQL 转储:

/*
SQLyog Ultimate v10.00 Beta1
MySQL - 5.5.24-log 
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;

create table `day_temperature` (
    `timelog` datetime ,
    `roomtemp` float 
); 
insert into `day_temperature` (`timelog`, `roomtemp`) values('2014-07-19 10:27:20','25');
insert into `day_temperature` (`timelog`, `roomtemp`) values('2014-07-19 10:30:21','27');
insert into `day_temperature` (`timelog`, `roomtemp`) values('2014-07-20 11:10:20','28');
insert into `day_temperature` (`timelog`, `roomtemp`) values('2014-07-20 12:00:00','23');