mysql中，筛选出今天“相邻两条数据差值5分钟”以上的数据，表数据量比较大，应该怎么写这个sql？

set @tmp = '2000-1-1'; select * from (select *, TIMESTAMPDIFF(SECOND, @tmp, time) as diff,  @tmp := time  from 表名 ) as t1 where diff > 300;

试试这行么? 用临时变量 @tmp 保存上一条记录的time, 然后和本条记录做秒差

另外如果可以生成新表的话, 那建个 auto increment 的主键字段, 然后用连表条件select * from 表 as a left join 表 as b on a.主键 = b.主键+1 where TIMESTAMPDIFF(SECOND, a.time, b.time) > 300 差行连表, 再计算

还有个技巧, 不知道性能如何, 应该可以用, 把select字段选少一些就行.

set @i = 0; set @j = 0; select *, a2 - a1 from ( select *, @i := @i + 1 as a1 from lag1 ) as t1 left join (  select *, @j := @j + 1 as a2 from lag1 ) as t2 on t1.a1 + 1 = t2.a2 ;