create table ttt(id int identity,name varchar(10),createtime datetime)
delete a from ttt a
where exists(select 1 from ttt where a.name=name and datediff(mi,a.createtime,createtime)<=3 and a.createtime<createtime)
create table ttt(id int identity,name varchar(10),createtime datetime)
delete a from ttt a
where exists(select 1 from ttt where a.name=name and datediff(mi,a.createtime,createtime)<=3 and a.createtime<createtime)
create table iddt(id int ,dt datetime)declare @dt datetime
set @dt = '2007-08-08 18:25:37'
insert into iddt select 1,dateadd(minute,-1,@dt)
union all select 1,dateadd(minute,1,@dt)
union all select 1,dateadd(minute,2,@dt)
union all select 2,dateadd(minute,-1,@dt)
union all select 1,dateadd(minute,3,@dt)
union all select 2,dateadd(minute,1,@dt)
union all select 2,dateadd(minute,2,@dt)
union all select 2,dateadd(minute,3,@dt)
union all select 3,dateadd(minute,-1,@dt)--创建临时表,添加自增列i,辅助实现循环
select i=identity(int,1,1),* into #
from iddt
order by id ,dtselect * from # --显示1declare @pos int,@num int
select @pos = 1,@num=max(i) from #declare @id int,@dt datetime, @temp intwhile @pos < @num --最后一条不处理
begin
select @id = id ,@dt = dt from # where i = @pos
select @temp = count(*) --得到将删除的列数,用于下次循环定位
from #
where
i > @pos
and id = @id and datediff(ss,@dt,dt) <=180 delete #
where
i > @pos
and id = @id and datediff(ss,@dt,dt) <=180
set @pos = @pos+@temp+1
endselect * from # --显示2--显示1 结果
/*
1 1 2007-08-08 18:24:37.000
2 1 2007-08-08 18:26:37.000
3 1 2007-08-08 18:27:37.000
4 1 2007-08-08 18:28:37.000
5 2 2007-08-08 18:24:37.000
6 2 2007-08-08 18:26:37.000
7 2 2007-08-08 18:27:37.000
8 2 2007-08-08 18:28:37.000
9 3 2007-08-08 18:24:37.000
*/--显示2 结果
/*
1 1 2007-08-08 18:24:37.000
4 1 2007-08-08 18:28:37.000
5 2 2007-08-08 18:24:37.000
8 2 2007-08-08 18:28:37.000
9 3 2007-08-08 18:24:37.000
*/
use kq
go--创建临时表if(exists (select * from sysobjects where name = 'tb_temp'))
begin
drop table tb_temp
end
go
create table tb_temp
(
dates varchar(10),
times datetime,
cardID bigint,
equName int,
state int,
d_id varchar(50)
)
drop procedure pr_del
go
create procedure pr_del
as
begindeclare @cardID bigint,@times datetime,@dates bigint,@count intdeclare temp_cursor cursor for
select distinct(cardid) from tb_temp open temp_cursor
fetch next from temp_cursor into @cardID
while(@@fetch_status=0)
begin
fetch next from temp_cursor into @cardId select @times = min(times) from tb_temp where cardID = @cardID
select @count = count(*) from tb_temp where cardID = @cardID
select @times = min(times) from tb_temp where cardID = @cardID while(@count>1) begin
delete tb_temp where cardID = @cardID and DATEDIFF(minute,@times,times)<3 and DATEDIFF(second,@times,times)>0 and DATEDIFF(second,@times,times)<>0 select @times = min(times) from tb_temp where cardID = @cardID and times>@times
select @count = count(*) from tb_temp where cardID = @cardID and times>@times
endendclose temp_cursor
deallocate temp_cursor
end----------------- 完 ----------------------------------
-- delete tb_temp
-- delete emp_icflowk
-- delete icflowk
--
-- select * from tb_temp order by cardID
-- select * from emp_icflowk order by pnid
-- select * from icflowk order by icno
--
-- insert tb_temp select * from tb_temp2
--
-- exec pr_del