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 软起故障
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 软起故障
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
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型,截取哪里要转换一下在截取)