--建立测试环境
Create Table 表(id varchar(10),编号 varchar(10),时间 datetime)
--插入数据
insert into 表
select '1016','90377B972E','2005-04-21 13:42:00' union
select '1017','A0543DAD66','2005-04-21 13:42:00' union
select '1018','90377B972E','2005-04-21 13:43:00' union
select '1019','A0543DAD66','2005-04-21 13:43:00' union
select '1020','90377B972E','2005-04-21 13:43:00' union
select '1021','A0543DAD66','2005-04-21 13:43:00' union
select '1022','90377B972E','2005-04-21 13:43:00' union
select '1023','A0543DAD66','2005-04-21 13:43:00' union
select '1024','90377B972E','2005-04-21 13:43:00' union
select '1025','90377B972E','2005-04-21 13:43:00' union
select '1026','A0543DAD66','2005-04-21 13:43:00'
select
a.*
from
表 a
where
id = (select top 1 id from 表 where 编号 = a.编号 and datediff(mi,时间,a.时间)<10 )
--删除测试环境
Drop Table 表/*
(所影响的行数为 11 行)id 编号 时间
---------- ---------- ------------------------------------------------------
1016 90377B972E 2005-04-21 13:42:00.000
1017 A0543DAD66 2005-04-21 13:42:00.000
*/
Create Table 表(id varchar(10),编号 varchar(10),时间 datetime)
--插入数据
insert into 表
select '1016','90377B972E','2005-04-21 13:42:00' union
select '1017','A0543DAD66','2005-04-21 13:42:00' union
select '1018','90377B972E','2005-04-21 13:43:00' union
select '1019','A0543DAD66','2005-04-21 13:43:00' union
select '1020','90377B972E','2005-04-21 13:43:00' union
select '1021','A0543DAD66','2005-04-21 13:43:00' union
select '1022','90377B972E','2005-04-21 13:43:00' union
select '1023','A0543DAD66','2005-04-21 13:43:00' union
select '1024','90377B972E','2005-04-21 13:43:00' union
select '1025','90377B972E','2005-04-21 13:43:00' union
select '1026','A0543DAD66','2005-04-21 13:43:00'
select
a.*
from
表 a
where
id = (select top 1 id from 表 where 编号 = a.编号 and datediff(mi,时间,a.时间)<10 )
--删除测试环境
Drop Table 表/*
(所影响的行数为 11 行)id 编号 时间
---------- ---------- ------------------------------------------------------
1016 90377B972E 2005-04-21 13:42:00.000
1017 A0543DAD66 2005-04-21 13:42:00.000
*/
我在你提示下做了一点小小的修改,继续向你学习
delete table1 where id in (
select a.id from table1 a
where id= (select top 1 id from table1 where 编号 = a.编号 and datediff(mi,时间,a.时间)<10 ))