mysql - 计算两行之间的日期时间，显示超过 60 秒的日期时间

Question

例如我有一张 table

id, datetime

1 2014-01-01 01:23:23
2 2014-01-01 01:25:23
3 2014-01-01 01:26:23
4 2014-01-01 01:26:25
7 2014-01-01 01:27:25
8 2014-01-01 01:27:26

我想收到一个 id 列表，它们之间有超过 60 秒的差异。

我的表非常大，当我尝试运行查询时需要很长时间。有更容易的方法吗？表中的 ID 不是自增的，它们可以在它们之间跳转。

SELECT A.id, A.datetime, (B.datetime - A.datetime) AS timedifference
FROM MyTable A CROSS JOIN MyTable B
WHERE B.id IN (SELECT MIN(C.id) FROM MyTable C WHERE C.id > A.id 
AND C.datetime between '2014-08-10 00:00:00' AND '2014-08-10 00:01:00') 
AND A.datetime between '2014-08-10 00:00:00' AND '2014-08-10 00:01:00'  
AND B.datetime between '2014-08-10 00:00:00' AND '2014-08-10 00:01:00'  
having timedifference > 60 
ORDER BY A.id ASC;

Answer 1

如果我没理解错的话，你只是想找到一分钟或更长时间间隔的地方。我会建议一个相关的子查询:

select a.id, a.datetime,
       (select id
        from mytable b
        where b.datetime > a.datetime + interval 1 minute
        order by b.datetime
        limit 1
       ) as nextid
from mytable a
where A.datetime between '2014-08-10 00:00:00' AND '2014-08-10 00:01:00'
having nextid is not null;

为了提高性能，您需要在 mytable(datetime, id) 上建立索引。