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

[xiang1115]

同一个表中的一个字段是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:20

select id,status,datatime from 表名 group by id,status order by id,datatime这个是我分组查询的语句，在这个分组中有的上下两条的时间差就很大，如果大于五分钟就得记录，我最后得到的这一组中共出现了几次大于五分钟的情况。
数据库使用的是mysql数据库。

[ws_hgo]

这个是MSSQL版块啊

[dawugui]

MYSQL的话，帮顶。

[dawugui]

--如果是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

[喜-喜]

[Quote=引用楼主 xiang1115 的回复:]
同一个表中的一个字段是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……
[/Quote]

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

[dawugui]

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')
go

select 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 id

drop table tb

/*
id          cnt         
----------- ----------- 
1           3
2           2

（所影响的行数为 2 行）
*/

[xiang1115]

谢谢5楼
我先试试，不过我分组是有两个条件的

[ai_li7758521]

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'
go

WITH 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 行受影响)

[ws_hgo]

[Quote=引用 6 楼 xiang1115 的回复:]

谢谢5楼
我先试试，不过我分组是有两个条件的
[/Quote]

我也写个

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')
go

select 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.id

id          次数
----------- -----------
1           3
2           2

(2 行受影响)

[abuying]

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

[bray_08]

进来学习学习。。

[xiang1115]

[Quote=引用 9 楼 abuying 的回复:]
LZ发错了吧。
你用的是mysql,不是MS　SQL
不过有些语法可以通用。
楼上的可以去试试
[/Quote]
mysql的问题要再哪里提问呢，我没有找到这个模块啊。

[ws_hgo]

[Quote=引用 11 楼 xiang1115 的回复:]

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

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

兄弟这个帖子可以结了吧
大家写的这么辛苦


Mysql
版块在
Linux/Unix平台下面

[xiang1115]

谢谢LS所有的朋友。
楼上的答案我都试过了，但都不能满足要求。可能我说的不明白。
我要的是在分组、排序之后的出现大于五分钟的情况，这个时间的比较只能是在同一组中比较，不同的组没有比较的必要性。
数据库中的数据很多，都超过了几百万条的记录，查询中限制的条件也很多，要从这些记录中查到需要的记录

[喜-喜]

[Quote=引用 13 楼 xiang1115 的回复:]
谢谢LS所有的朋友。
楼上的答案我都试过了，但都不能满足要求。可能我说的不明白。
我要的是在分组、排序之后的出现大于五分钟的情况，这个时间的比较只能是在同一组中比较，不同的组没有比较的必要性。
数据库中的数据很多，都超过了几百万条的记录，查询中限制的条件也很多，要从这些记录中查到需要的记录
[/Quote]

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

[xiang1115]

[Quote=引用 14 楼 happycell188 的回复:]
引用 13 楼 xiang1115 的回复:
谢谢LS所有的朋友。
楼上的答案我都试过了，但都不能满足要求。可能我说的不明白。
我要的是在分组、排序之后的出现大于五分钟的情况，这个时间的比较只能是在同一组中比较，不同的组没有比较的必要性。
数据库中的数据很多，都超过了几百万条的记录，查询中限制的条件也很多，要从这些记录中查到需要的记录


3、5、8 楼的朋友的SQL都实现分组、排序……
[/Quote]

实现的是最后的分组、排序，中间中比较的记录就不是按分组、排序后的了

[ws_hgo]

Mysql
版块在
Linux/Unix平台下面