Mysql:查找行,其中时间戳差异小于x [英] Mysql: Find rows, where timestamp difference is less than x
本文介绍了Mysql:查找行,其中时间戳差异小于x的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我的表结构如下:
Field Type
id int(11) AI
user varchar(64)
date timestamp
key int(11)
我需要做的是查找行(从给定的日期开始),其中两个连续行之间的时间差(最近的时间戳记(给定用户))小于1300.
What I need to do, is find rows (from given day), where difference between two successive rows (closest timestamp (for given user) is less than 1300.
有人告诉我,使用这样的查询:
I was told, to use query like this:
select t.*
from (select t.*, @nextdate as nextdate, @nextdate := date
from my_table t
order by date desc
) t
where t.nextdate - t.date < 1300;
但是它似乎没有用.谁能帮助我解决任务?
But it didn't seem to work. Can anyone help me solve my task?
推荐答案
尝试一下:-
select t1.user, t1.date d1,t2.date d2 ,t1.date-t2.date
from (select @val:=@val+1 rowid,user, date
from mytable,(select @val:=0) a
order by user,date) t1,
(select @val1:=@val1+1 rowid,user, date
from mytable,(select @val1:=1) b
order by user,date) t2
where t1.rowid = t2.rowid
and t1.user = t2.user
and t1.date-t2.date < 1300;
请参见演示
这篇关于Mysql:查找行,其中时间戳差异小于x的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文