删除时间范围内，重复的数据

[bibite51520]

NO SID Time text
1 0001 2012-4-10 10:48:03 2#风机工频故障
2 0001 2012-4-10 10:51:26 1#风机工频故障
3 0001 2012-4-10 10:52:17 1#风机工频故障
4 0001 2012-4-10 11:05:14 1#风机工频故障
5 0001 2012-4-10 11:05:56 1#风机工频故障
6 0001 2012-4-10 11:07:04 2#风机工频故障
7 0001 2012-4-10 11:07:46 2#风机工频故障
8 0001 2012-4-10 11:08:54 软起故障
9 0001 2012-4-10 11:10:10 方管输送机故障
10 0001 2012-4-10 11:11:26 除尘卸料故障
11 0001 2012-4-10 11:12:08 除尘卸料故障
12 0001 2012-4-10 11:13:16 输送卸料故障
13 0001 2012-4-10 11:13:58 输送卸料故障
14 0001 2012-4-10 11:19:45 1#风机工频故障
15 0001 2012-4-10 11:20:27 1#风机工频故障
16 0001 2012-4-10 12:52:57 软起故障



当SID和Text相同时，Time在1分钟内只保留1条数据，结果如下
NO SID Time text
1 0001 2012-4-10 10:48:03 2#风机工频故障
2 0001 2012-4-10 10:51:26 1#风机工频故障
4 0001 2012-4-10 11:05:14 1#风机工频故障
6 0001 2012-4-10 11:07:04 2#风机工频故障
8 0001 2012-4-10 11:08:54 软起故障
9 0001 2012-4-10 11:10:10 方管输送机故障
10 0001 2012-4-10 11:11:26 除尘卸料故障
12 0001 2012-4-10 11:13:16 输送卸料故障
14 0001 2012-4-10 11:19:45 1#风机工频故障
16 0001 2012-4-10 12:52:57 软起故障

[Me_zzx]

DELETE 表名 WHERE NO NOT IN ( SELECT MAX(NO) FROM 表名 GROUP SID,TIME,TEXT )

[学习中-------]

create table tb (no int,sid varchar(50),time datetime,text varchar(50))

insert tb select 1 ,'0001','2012-4-10 10:48:03','2#风机工频故障'
insert tb select 2 ,'0001','2012-4-10 10:51:26','1#风机工频故障'
insert tb select 3 ,'0001','2012-4-10 10:52:17','1#风机工频故障'
insert tb select 4 ,'0001','2012-4-10 11:05:14','1#风机工频故障'
insert tb select 5 ,'0001','2012-4-10 11:05:56','1#风机工频故障'
insert tb select 6, '0001','2012-4-10 11:07:04','2#风机工频故障'

with etc as (
select *,sid+'-'+text aa,row_number()over( partition by sid+'-'+text order by sid+'-'+text,time)na from tb )
select * from etc a where  exists (select * from etc b where 
( b.na>a.na and datediff(ss,a.time,b.time)<60 )  ) or not exists ( select * from etc b where
b.na<a.na and datediff(ss,a.time,b.time)<60 )
order by no

[Me_zzx]

[Quote=引用 1 楼 的回复:]

SQL code

DELETE 表名 WHERE NO NOT IN ( SELECT MAX(NO) FROM 表名 GROUP SID,TIME,TEXT ) --这个精确到秒了

[/Quote]

DELETE 表名 WHERE NO NOT IN ( SELECT MAX(NO) FROM ( 
SELECT SUBSTRING(TIME,1,LEN(TIME)-3) AS TIME1,* FROM 表名) AS TABLE1 GROUP SID,TIME1,TEXT) --精确到分，还不知道你TIME类型（时间类型为VARCHAR,如果是DATETIME型，截取哪里要转换一下在截取）

[qq381289113]

小倪的代码很大的问题，group 不能用作子语句的