sql语句中的比较同一列中的两条记录的时间差

Question

同一个表中的一个字段是datetime类型的，如何比较他的上下两条记录的时间差是小于五分钟的，如果大于五分钟则记录为一次。
例如：表结构：
id   status   datatime
1       2     2010-09-20 10:20:20
1       2     2010-09-20 10:22:20
1       2     2010-09-20 10:40:20
1       3     2010-09-20 10:50:20
1       2     2010-09-20 11:20:20
2       4     2010-09-20 10:22:20
2       3     2010-09-20 10:28:20
2       5     2010-09-20 10:29:20
2       3     2010-09-20 10:59:20select id,status,datatime from 表名 group by id,status order by id,datatime这个是我分组查询的语句，在这个分组中有的上下两条的时间差就很大，如果大于五分钟就得记录，我最后得到的这一组中共出现了几次大于五分钟的情况。
数据库使用的是mysql数据库。

Answer 1

--如果是MS SQL则如下：
select id , count(1) from 
(
  select t.* , datediff(mi,m.datatime,(select top 1 datatime from tb where datatime > t.datatime and id = t.id)) dt from tb t
) m
where dt >= 5
group by id

Answer 2

思路：根据需求，为各个分组添加连续 id ，然后错位自连接，如：from a join b on a.id=b.id-1！接下来计算时间差并判断..

Answer 3

create table tb(id int,status int,datatime datetime)
insert into tb values(1 ,2 ,'2010-09-20 10:20:20')
insert into tb values(1 ,2 ,'2010-09-20 10:22:20')
insert into tb values(1 ,2 ,'2010-09-20 10:40:20')
insert into tb values(1 ,3 ,'2010-09-20 10:50:20')
insert into tb values(1 ,2 ,'2010-09-20 11:20:20')
insert into tb values(2 ,4 ,'2010-09-20 10:22:20')
insert into tb values(2 ,3 ,'2010-09-20 10:28:20')
insert into tb values(2 ,5 ,'2010-09-20 10:29:20')
insert into tb values(2 ,3 ,'2010-09-20 10:59:20')
goselect id , count(1) cnt from 
(
  select t.* , datediff(mi,t.datatime,(select top 1 datatime from tb where datatime > t.datatime and id = t.id order by datatime)) dt from tb t
) m
where dt >= 5
group by iddrop table tb/*
id          cnt         
----------- ----------- 
1           3
2           2（所影响的行数为 2 行）
*/

Answer 4

CREATE TABLE #1(id int,status int,datatime datetime)INSERT #1 
SELECT 1 ,2 ,'2010-09-20 10:20:20'union all 
SELECT 1 ,2 ,'2010-09-20 10:22:20'union all 
SELECT 1 ,2 ,'2010-09-20 10:40:20'union all 
SELECT 1 ,3 ,'2010-09-20 10:50:20'union all 
SELECT 1 ,2 ,'2010-09-20 11:20:20'union all 
SELECT 2 ,4 ,'2010-09-20 10:22:20'union all 
SELECT 2 ,3 ,'2010-09-20 10:28:20'union all 
SELECT 2 ,5 ,'2010-09-20 10:29:20'union all 
SELECT 2 ,3 ,'2010-09-20 10:59:20'
goWITH Ailly
AS
(
SELECT r=ROW_NUMBER() over(order by id,status,datatime),*
from #1
)SELECT b.id,b.status,cnt=COUNT(1)
FROM Ailly a JOIN Ailly b 
on a.r=b.r-1
where DATEDIFF(N,b.datatime,a.datatime)>5
group by b.id,b.status id          status      cnt
----------- ----------- -----------
1           3           1
2           3           1
2           4           1(3 行受影响)

Answer 5

我也写个if object_id('tb') is not null drop table tb
go
create table tb(id int,status int,datatime datetime)
insert into tb values(1 ,2 ,'2010-09-20 10:20:20')
insert into tb values(1 ,2 ,'2010-09-20 10:22:20')
insert into tb values(1 ,2 ,'2010-09-20 10:40:20')
insert into tb values(1 ,3 ,'2010-09-20 10:50:20')
insert into tb values(1 ,2 ,'2010-09-20 11:20:20')
insert into tb values(2 ,4 ,'2010-09-20 10:22:20')
insert into tb values(2 ,3 ,'2010-09-20 10:28:20')
insert into tb values(2 ,5 ,'2010-09-20 10:29:20')
insert into tb values(2 ,3 ,'2010-09-20 10:59:20')
goselect t.id,count(t.id) '次数'
from
(
 select *,
       (select count(*)+1 from tb where id=t.id and datatime<t.datatime) rank
from tb t
) t
join
(
select *,
       (select count(*)+1 from tb where id=t.id and datatime<t.datatime) rank
from tb t
) t1
on t.id=t1.id and t.rank=t1.rank-1 
and datediff(minute,t.datatime,t1.datatime)>5
group by t.idid          次数
----------- -----------
1           3
2           2(2 行受影响)

Answer 6

LZ发错了吧。
你用的是mysql,不是MS　SQL
不过有些语法可以通用。
楼上的可以去试试

Answer 7

mysql的问题要再哪里提问呢，我没有找到这个模块啊。

Answer 8

兄弟这个帖子可以结了吧
大家写的这么辛苦
Mysql
版块在
Linux/Unix平台下面

Answer 9

3、5、8 楼的朋友的SQL都实现分组、排序啦...

Answer 10

Mysql
版块在
Linux/Unix平台下面