select *,0 as flag into #t from 表 order by iddeclare @logtime datetimeupdate #t set flag = case when @logtime is null or datediff(ss,@logtime,logtime)>300 then 1 else 0 end, @logtime = case when @logtime is null or datediff(ss,@logtime,logtime)>300 then logtime else @logtime endselect * from #t where flag = 1 order by id
select * from 表 t where ( exists(select 1 from 表 where id=(select max(id) from 表 where id<t.id) and datediff(minute,logtime,t.logtime)>=5) and exists(select 1 from 表 where id=(select min(id) from 表 where id>t.id) and datediff(minute,t.logtime,logtime)>=5) ) or ( t.id=(select max(id) from 表) and exists(select 1 from 表 where id=(select max(id) from 表 where id<t.id) and datediff(minute,logtime,t.logtime)>=5) ) or ( t.id=(select min(id) from 表) and exists(select 1 from 表 where id=(select min(id) from 表 where id>t.id) and datediff(minute,t.logtime,logtime)>=5) )
--看楼主的数据很多,性能上可能差了点。暂时没有想到更好的方法。 --测试表及数据 declare @tb table(id int,logtime datetime) insert into @tb select 1, '2005-09-26 15:18:15.000' union all select 2, '2005-09-26 15:20:15.000' union all select 3, '2005-09-26 15:21:16.000' union all select 5, '2005-09-26 15:26:19.000' union all select 12, '2005-09-26 15:29:30.000' union all select 13, '2005-09-26 15:35:31.000' union all select 14, '2005-09-26 15:37:31.000'--辅助表 select *,flag=0 into # from @tb --处理 declare @flag int,@dt1 datetime update # set @flag=case when @dt1 is null then 1 else (case when datediff(mi,@dt1,logtime)>5 then 1 else 0 end) end, @dt1=case when @flag=1 then logtime else @dt1 end, flag=@flag --获取结果 select id,logtime from # where flag=1--删除辅助表 drop table #
这样id为5的记录就不保留了
如果你是希望插入完成后在过滤,那估计一条sql实现不了吧,写个函数可以实现。
set
flag = case when @logtime is null or datediff(ss,@logtime,logtime)>300 then 1 else 0 end,
@logtime = case when @logtime is null or datediff(ss,@logtime,logtime)>300 then logtime else @logtime endselect * from #t where flag = 1 order by id
where (
exists(select 1
from 表
where id=(select max(id) from 表 where id<t.id)
and datediff(minute,logtime,t.logtime)>=5)
and
exists(select 1
from 表
where id=(select min(id) from 表 where id>t.id)
and datediff(minute,t.logtime,logtime)>=5)
)
or
(
t.id=(select max(id) from 表)
and exists(select 1
from 表
where id=(select max(id) from 表 where id<t.id)
and datediff(minute,logtime,t.logtime)>=5)
)
or
(
t.id=(select min(id) from 表)
and
exists(select 1
from 表
where id=(select min(id) from 表 where id>t.id)
and datediff(minute,t.logtime,logtime)>=5)
)
你的方法中好像 @logtime有问题
用一条sql恐怕很难做。
--测试表及数据
declare @tb table(id int,logtime datetime)
insert into @tb
select 1, '2005-09-26 15:18:15.000' union all
select 2, '2005-09-26 15:20:15.000' union all
select 3, '2005-09-26 15:21:16.000' union all
select 5, '2005-09-26 15:26:19.000' union all
select 12, '2005-09-26 15:29:30.000' union all
select 13, '2005-09-26 15:35:31.000' union all
select 14, '2005-09-26 15:37:31.000'--辅助表
select *,flag=0 into #
from @tb
--处理
declare @flag int,@dt1 datetime
update #
set @flag=case when @dt1 is null then 1 else (case when datediff(mi,@dt1,logtime)>5 then 1 else 0 end) end,
@dt1=case when @flag=1 then logtime else @dt1 end,
flag=@flag
--获取结果
select id,logtime from # where flag=1--删除辅助表
drop table #