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

山城忙碌人 java软件工程师  2015-08-25 05:09:39
我要查询出短时间内的重复数据,可以忽略日期,数据库为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');
...全文
246 点赞 收藏 7
写回复
7 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
Cherise_huang 2015-08-26

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

结果如图:
回复
山城忙碌人 2015-08-26
引用 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
回复
山城忙碌人 2015-08-26
引用 1 楼 yupeigu 的回复:
试试这个:
select *
from mtest
order by name,time
兄弟,你这个行不通。正确方法要用递归。参见2楼!
回复
Haytor 2015-08-26
如果是使用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
回复
Cherise_huang 2015-08-26
不客气,互相学习!
回复
山城忙碌人 2015-08-26
引用 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 
结果如图:
得到了我想要的结果,谢谢!
回复
试试这个:
select *
from mtest
order by name,time
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2015-08-25 05:09
社区公告
暂无公告