对表中的一顺序列,判断他们相邻两个的差是否在一个范围中

Question

IPAddress            ReigsterTime                                           select rowid = identity(int,1,1),* into #tmp from tableselect IPAddress,
       ReigsterTime,
       datediff(ms,a.ReigsterTime,b.ReigsterTime) as [count]
from   table a,
       (select * from #tmp where rowid = a.rowid + 1) b
where 
       a.rowid + 1 = b.rowid

Answer 1

declare @t table(IPAddress varchar(15),ReigsterTime datetime)
insert into @t select '211.103.103.68','2006-01-20 00:31:00.000'
insert into @t select '211.103.103.68','2006-01-20 00:33:00.000'
insert into @t select '211.103.103.68','2006-01-20 00:34:00.000'
insert into @t select '211.103.103.68','2006-01-20 00:35:00.000'
insert into @t select '211.103.103.68','2006-01-20 00:36:00.000'
insert into @t select '211.103.103.68','2006-01-20 00:39:00.000'
insert into @t select '211.103.103.68','2006-01-20 00:40:00.000'
insert into @t select '211.103.103.68','2006-01-20 00:42:00.000'select 
    distinct IPAddress 
from 
    @t a 
where 
    exists(select 
               1 
           from 
               @t 
           where 
               IPAddress=a.IPAddress 
               and 
               ReigsterTime<a.ReigsterTime 
               and 
               datediff(mi,ReigsterTime,a.ReigsterTime)<10)

Answer 2

declare @t table(IPAddress varchar(15),ReigsterTime datetime)
insert into @t select '211.103.103.68','2006-01-20 00:31:00.000'
insert into @t select '211.103.103.68','2006-01-20 00:33:00.000'
insert into @t select '211.103.103.68','2006-01-20 00:34:00.000'
insert into @t select '211.103.103.68','2006-01-20 00:35:00.000'
insert into @t select '211.103.103.68','2006-01-20 00:36:00.000'
insert into @t select '211.103.103.68','2006-01-20 00:39:00.000'
insert into @t select '211.103.103.68','2006-01-20 00:40:00.000'
insert into @t select '211.103.103.68','2006-01-20 00:42:00.000'if exists(select 
              1 
          from 
              @t a 
          where 
              exists(select 
                         1 
                     from 
                         @t 
                     where 
                         IPAddress=a.IPAddress 
                         and 
                         ReigsterTime!=a.ReigsterTime 
                         and 
                         datediff(mi,ReigsterTime,a.ReigsterTime)<10))
    select 'exists'
else
    select 'not exists'/*
---------
exists
*/

Answer 3

declare @t table(IPAddress varchar(15),ReigsterTime datetime)
insert @t 
select '211.103.103.68','2006-01-20 00:31:00.000' union all
select '211.103.103.68','2006-01-20 00:33:00.000' union all
select '211.103.103.68','2006-01-20 00:34:00.000' union all
select '211.103.103.68','2006-01-20 00:35:00.000' union all
select '211.103.103.68','2006-01-20 00:36:00.000' union all
select '211.103.103.68','2006-01-20 00:39:00.000' union all
select '211.103.103.68','2006-01-20 00:40:00.000' union all
select '211.103.103.68','2006-01-20 00:42:00.000'
select rowid = identity(int,1,1),* into #tmp from @t
select a.IPAddress
       ,a.ReigsterTime
--       ,b.ReigsterTime 
       ,datediff(ss,a.ReigsterTime,b.ReigsterTime) as [count]
from #tmp a
,(select * from #tmp) b
where a.rowid = b.rowid - 1
drop table #tmp/*
IPAddress       ReigsterTime                                           count       
--------------- ------------------------------------------------------ ----------- 
211.103.103.68  2006-01-20 00:31:00.000                                120
211.103.103.68  2006-01-20 00:33:00.000                                60
211.103.103.68  2006-01-20 00:34:00.000                                60
211.103.103.68  2006-01-20 00:35:00.000                                60
211.103.103.68  2006-01-20 00:36:00.000                                180
211.103.103.68  2006-01-20 00:39:00.000                                60
211.103.103.68  2006-01-20 00:40:00.000                                120*/

Answer 4

declare @t table(IPAddress varchar(15),ReigsterTime datetime)
insert into @t select '211.103.103.68','2006-01-20 00:31:00.000'
insert into @t select '211.103.103.68','2006-01-20 00:33:00.000'
insert into @t select '211.103.103.68','2006-01-20 00:34:00.000'
insert into @t select '211.103.103.68','2006-01-20 00:35:00.000'
insert into @t select '211.103.103.68','2006-01-20 00:52:00.000'
insert into @t select '211.103.103.68','2006-01-20 00:36:00.000'
insert into @t select '211.103.103.68','2006-01-20 00:39:00.000'
insert into @t select '211.103.103.68','2006-01-20 00:40:00.000'
insert into @t select '211.103.103.68','2006-01-20 00:42:00.000'select id=identity(int,1,1),* into #t from @t order by ReigsterTimeselect distinct ipaddress
from #t a
where 
    exists(select 
               1 
           from 
               #t 
           where 
               IPAddress=a.IPAddress 
               and 
               id=a.id-1
               and 
               datediff(mi,ReigsterTime,a.ReigsterTime)<10)