求一sql,查询重复数据，相隔时间三分钟。

[山城忙碌人]

我要查询出短时间内的重复数据，可以忽略日期，数据库为sqlserver2005。原数据如下

id name time
1 10001 2015-1-1 12:00:00
2 10002 2015-1-1 12:00:30
3 10001 2015-1-1 12:00:20
4 10001 2015-1-1 12:00:40
5 10001 2015-1-1 12:10:40
6 10002 2015-1-1 12:01:40
7 10002 2015-1-1 12:21:40
8 10001 2015-1-1 12:05:20
9 10001 2015-1-1 12:05:40



要求得到结果如下：
id name time
1 10001 2015-1-1 12:00:00
3 10001 2015-1-1 12:00:20
4 10001 2015-1-1 12:00:40
8 10001 2015-1-1 12:05:20
9 10001 2015-1-1 12:05:40

2 10002 2015-1-1 12:00:30
6 10002 2015-1-1 12:01:40


求sql语句怎么写，可以不用排序。


测试语句如下

create table mtest(id int primary key,name varchar(10),time datetime);
insert into mtest values(1,'10001','2015-1-1 12:00:00');
insert into mtest values(2,'10002','2015-1-1 12:00:30');
insert into mtest values(3,'10001','2015-1-1 12:00:20');
insert into mtest values(4,'10001','2015-1-1 12:00:40');
insert into mtest values(5,'10001','2015-1-1 12:10:40');
insert into mtest values(6,'10002','2015-1-1 12:01:40');
insert into mtest values(7,'10003','2015-1-1 12:21:40');
insert into mtest values(8,'10001','2015-1-1 12:05:20');
insert into mtest values(9,'10001','2015-1-1 12:05:40');

[LongRui888]

试试这个：

select *
from mtest
order by name,time

[山城忙碌人]

引用 2 楼 Cherise_huang 的回复:

create table mtest(id int primary key,name varchar(10),time datetime);
insert into mtest values(1,'10001','2015-1-1 12:00:00');
insert into mtest values(2,'10002','2015-1-1 12:00:30');
insert into mtest values(3,'10001','2015-1-1 12:00:20');
insert into mtest values(4,'10001','2015-1-1 12:00:40');
insert into mtest values(5,'10001','2015-1-1 12:10:40');
insert into mtest values(6,'10002','2015-1-1 12:01:40');
insert into mtest values(7,'10003','2015-1-1 12:21:40');
insert into mtest values(8,'10001','2015-1-1 12:05:20');
insert into mtest values(9,'10001','2015-1-1 12:05:40');

select * from mtest

SELECT  * ,
        RANK() OVER (PARTITION BY name ORDER BY [time])'rank' INTO    mtest1 FROM    mtest;
WITH t AS (SELECT    a.* ,
                     DATEDIFF(minute, a.[time], b.[time]) 'Bminute' ,
                     DATEDIFF(minute, c.[time], a.[time]) 'Cminute' 
               FROM  mtest1 a 
               LEFT JOIN mtest1 b ON a.name = b.name  AND a.rank = b.rank - 1
               LEFT JOIN mtest1 c ON a.name = c.name  AND a.rank = c.rank + 1)     
SELECT  t.id ,
        t.name ,
        t.[time]     
FROM  t  
WHERE  Bminute BETWEEN 0 AND 3  OR Cminute BETWEEN 0 AND 3 

结果如图：

得到了我想要的结果，谢谢！

[Cherise_huang]

不客气，互相学习！

[Haytor]

如果是使用SQL2012的版本，可以用这个方法

SELECT
	id,name,time
FROM (
	SELECT
		*,
		LAG(time,1,time) OVER(PARTITION BY name ORDER BY time) AS BeforTime,
		LEAD(time,1,GETDATE()) OVER(PARTITION BY name ORDER BY time) AS NextTime
	FROM mtest
) a
WHERE DATEDIFF(ss,BeforTime,time)<180 OR DATEDIFF(ss,time,NextTime)<180

[山城忙碌人]

引用 1 楼 yupeigu 的回复:

试试这个：

select *
from mtest
order by name,time

兄弟，你这个行不通。正确方法要用递归。参见2楼！

[山城忙碌人]

引用 5 楼 airsoft 的回复:

如果是使用SQL2012的版本，可以用这个方法

SELECT
	id,name,time
FROM (
	SELECT
		*,
		LAG(time,1,time) OVER(PARTITION BY name ORDER BY time) AS BeforTime,
		LEAD(time,1,GETDATE()) OVER(PARTITION BY name ORDER BY time) AS NextTime
	FROM mtest
) a
WHERE DATEDIFF(ss,BeforTime,time)<180 OR DATEDIFF(ss,time,NextTime)<180

谢谢，我服务器是2005

[Cherise_huang]

create table mtest(id int primary key,name varchar(10),time datetime);
insert into mtest values(1,'10001','2015-1-1 12:00:00');
insert into mtest values(2,'10002','2015-1-1 12:00:30');
insert into mtest values(3,'10001','2015-1-1 12:00:20');
insert into mtest values(4,'10001','2015-1-1 12:00:40');
insert into mtest values(5,'10001','2015-1-1 12:10:40');
insert into mtest values(6,'10002','2015-1-1 12:01:40');
insert into mtest values(7,'10003','2015-1-1 12:21:40');
insert into mtest values(8,'10001','2015-1-1 12:05:20');
insert into mtest values(9,'10001','2015-1-1 12:05:40');

select * from mtest

SELECT  * ,
        RANK() OVER (PARTITION BY name ORDER BY [time])'rank' INTO    mtest1 FROM    mtest;
WITH t AS (SELECT    a.* ,
                     DATEDIFF(minute, a.[time], b.[time]) 'Bminute' ,
                     DATEDIFF(minute, c.[time], a.[time]) 'Cminute' 
               FROM  mtest1 a 
               LEFT JOIN mtest1 b ON a.name = b.name  AND a.rank = b.rank - 1
               LEFT JOIN mtest1 c ON a.name = c.name  AND a.rank = c.rank + 1)     
SELECT  t.id ,
        t.name ,
        t.[time]     
FROM  t  
WHERE  Bminute BETWEEN 0 AND 3  OR Cminute BETWEEN 0 AND 3 

结果如图：