MySQL max() - 不返回 max() 值

Question

我有一个查询，我想返回 idevent 中给定传感器 ID (sensorID) 范围内的最高 ID 值，但是查询没有返回最高值。

我运行查询时减去 max() 语句的结果:

mysql> SELECT * FROM events WHERE timestamp BETWEEN "2015-03-09 10:45:35" - INTERVAL 4000 SECOND AND "2015-03-09 10:45:35" AND (sensorID = 34035434 OR sensorID = 34035492 OR sensorID = 34035426 OR sensorID = 34035482 OR sensorID = 34035125 OR sensorID = 34035498 OR sensorID = 34035508 OR sensorID = 34035444 OR sensorID = 34035418 OR sensorID = 34035466 OR sensorID = 34035128 OR sensorID = 34035119 OR sensorID = 34035448 OR sensorID = 34037294 OR sensorID = 34035549);
+---------+---------------------+----------+-------+
| idevent | timestamp           | sensorID | event |
+---------+---------------------+----------+-------+
|  117794 | 2015-03-09 10:14:58 | 34035434 |     9 |
|  117795 | 2015-03-09 10:15:03 | 34035508 |     9 |
|  117796 | 2015-03-09 10:15:17 | 34035508 |     1 |
+---------+---------------------+----------+-------+
3 rows in set (0.00 sec)

max()语句作为HAVING子句添加时给出的结果:

mysql> SELECT * FROM events WHERE timestamp BETWEEN "2015-03-09 10:45:35" - INTERVAL 4000 SECOND AND "2015-03-09 10:45:35" AND (sensorID = 34035434 OR sensorID = 34035492 OR sensorID = 34035426 OR sensorID = 34035482 OR sensorID = 34035125 OR sensorID = 34035498 OR sensorID = 34035508 OR sensorID = 34035444 OR sensorID = 34035418 OR sensorID = 34035466 OR sensorID = 34035128 OR sensorID = 34035119 OR sensorID = 34035448 OR sensorID = 34037294 OR sensorID = 34035549) HAVING max(idevent);
+---------+---------------------+----------+-------+
| idevent | timestamp           | sensorID | event |
+---------+---------------------+----------+-------+
|  117794 | 2015-03-09 10:14:58 | 34035434 |     9 |
+---------+---------------------+----------+-------+
1 row in set (0.00 sec)

为什么不返回最新值？

Answer 1

这可以通过 order by limit 来完成，所以您只需要在给定的第一个查询的末尾添加以下内容

order by idevent desc limit 1