CREATE TABLE test
(
id int PRIMARY KEY IDENTITY(1,1),
dealerId int,
actiontime datetime,
actiontype int
)
insert into dbo.Test
select '1','2013-6-24 12:30:00',1
union all
select '1','2013-6-24 12:30:30',1
union all
select '1','2013-6-24 12:31:00',1
union all
select '1','2013-6-24 12:32:00',1
--select * from dbo.Test
--创建临时表
if object_id('tempdb..#temp') is not null
Begin
drop table #temp
--select * from #temp
End
create table #temp
(
rownum int primary key,
id int ,
dealerid nvarchar(10),
actiontime datetime,
actiontype int
)
--读入数据到临时表
insert into #temp
select ROW_NUMBER() over(order by id) rownum,* from dbo.Test
where dealerid = '1' and actiontype = 1
SELECT * FROM #temp
--遍历临时表数据
declare @i int = 2,@lgh int = (select COUNT(*) from #temp),@actiontime datetime,@actiontime2datetime
while @i <= @lgh
begin
select @actiontime = actiontime from #temp where rownum = @i - 1
select @actiontime2 = actiontime from #temp where rownum = @i
if(ABS(DATEDIFF(s,@actiontime,@actiontime2)) < 60) delete from #temp where rownum =@i
set @i = @i + 1
end
select * from #temp