MYSQL avg time in where 子句

Question

表中的两列 cityWalkStartTime 和 cityWalkEndTime ，带有时间戳。

寻找将返回行 ID 的查询，以获得最小时间差。

Select rowId,TIME_TO_SEC(TIMEDIFF(cityWalkEndTime,cityWalkStartTime)) from walks where  <time diffence is minimum in the entire database>

此外，如果我想要时差小于 10 秒的行。

Select rowId,TIME_TO_SEC(TIMEDIFF(cityWalkEndTime,cityWalkStartTime)) from walks where  <time diffence is < 10 seconds>

Answer 1

非常简单:对应rowId的数据库中的最小值:

Select rowId,MIN(TIME_TO_SEC(TIMEDIFF(cityWalkEndTime,cityWalkStartTime)))
from walks 
GROUP BY rowId
ORDER BY MIN(TIME_TO_SEC(TIMEDIFF(cityWalkEndTime,cityWalkStartTime))) ASC
LIMIT 1;

只是值<10s

Select rowId,TIME_TO_SEC(TIMEDIFF(cityWalkEndTime,cityWalkStartTime)) 
from walks 
where TIME_TO_SEC(TIMEDIFF(cityWalkEndTime,cityWalkStartTime))<10;