SQL Server流水时间统计2解决思路

流水表AIP                       时间192.168.1.1    2012-02-01 08:00:00192.168.1.3    2012-02-01 08:00:01192.168.1.3    2012-02-01 08:01:00192.168.1.1    2012-02-01 08:01:00192.168.1.1    2012-02-01 08:06:00192.168.1.3    2012-02-01 08:07:00192.168.1.3    2012-02-01 09:01:00192.168.1.4    2012-02-01 09:07:00192.168.1.4    2012-02-01 09:08:00基础表BIP                开始时间1  结束时间1 开始时间2 结束时间2192.168.1.1    9:00      11:30      14:00      17:30192.168.1.2    9:00      11:30      14:00      17:30192.168.1.3    9:00      11:30      14:00      17:30192.168.1.4    9:00      11:30      14:00      17:30192.168.1.5    9:00      11:30      14:00      17:30结果是IP                  开始时间                         结束时间            差距192.168.1.1    2012-02-01 09:00:00.000    2012-02-01 10:00:00.000    60192.168.1.2    2012-02-01 09:00:00.000    2012-02-01 10:00:00.000    60192.168.1.3    2012-02-01 09:01:00.000    2012-02-01 10:00:00.000    59192.168.1.4    2012-02-01 09:00:00.000    2012-02-01 09:07:00.000    7192.168.1.4    2012-02-01 09:00:00.000    2012-02-01 09:08:00.000    8192.168.1.4    2012-02-01 09:08:00.000    2012-02-01 10:00:00.000    52192.168.1.4    2012-02-01 09:07:00.000    2012-02-01 10:00:00.000    53192.168.1.5    2012-02-01 09:00:00.000    2012-02-01 10:00:00.000    60并没有解决到实际问题所需要的结果是，请注意192.168.1.4的记录，也就是说如果【两个相近时间】差小于5分钟就不在结果集中IP                  开始时间                         结束时间            差距192.168.1.1    2012-02-01 09:00:00.000    2012-02-01 10:00:00.000    60192.168.1.2    2012-02-01 09:00:00.000    2012-02-01 10:00:00.000    60192.168.1.3    2012-02-01 09:01:00.000    2012-02-01 10:00:00.000    59192.168.1.4    2012-02-01 09:00:00.000    2012-02-01 09:07:00.000    7192.168.1.4    2012-02-01 09:08:00.000    2012-02-01 10:00:00.000    52192.168.1.5    2012-02-01 09:00:00.000    2012-02-01 10:00:00.000    60

create table ta(IP varchar(15), 时间 datetime)insert into taselect '192.168.1.1', '2012-02-01 08:00:00' union allselect '192.168.1.3', '2012-02-01 08:00:01' union allselect '192.168.1.3', '2012-02-01 08:01:00' union allselect '192.168.1.1', '2012-02-01 08:01:00' union allselect '192.168.1.1', '2012-02-01 08:06:00' union allselect '192.168.1.3', '2012-02-01 08:07:00' union allselect '192.168.1.3', '2012-02-01 09:01:00' union allselect '192.168.1.4', '2012-02-01 09:07:00' union allselect '192.168.1.4', '2012-02-01 09:08:00'create table tb(IP varchar(15), 开始时间1 varchar(6), 结束时间1 varchar(6), 开始时间2 varchar(6), 结束时间2 varchar(6))insert into tbselect '192.168.1.1', '9:00', '11:30', '2:00', '5:30' union allselect '192.168.1.2', '9:00', '11:30', '2:00', '5:30' union allselect '192.168.1.3', '9:00', '11:30', '2:00', '5:30' union allselect '192.168.1.4', '9:00', '11:30', '2:00', '5:30' union allselect '192.168.1.5', '9:00', '11:30', '2:00', '5:30'declare @c datetimeselect @c='2012-02-01 10:00:00';with t1 as(select IP,cast(convert(varchar,@c,23)+' '+tb.开始时间1 as datetime) dt from tb union all select IP,cast(convert(varchar,@c,23)+' '+tb.结束时间1 as datetime) dt from tb),t2 as(select ta.IP, ta.时间 dtfrom tainner join tb on ta.IP=tb.IPwhere ta.时间 between convert(varchar,@c,23)+' '+tb.开始时间1 and convert(varchar,@c,23)+' '+tb.结束时间1),t4 as(select t3.IP,t3.dt,row_number() over(partition by t3.IP order by t3.dt) rn from (select * from t1 union all select * from t2) t3),t5 as(select t41.IP,t41.dt dt1,t42.dt dt2,@c currenttimefrom (select * from t4 where dt<[email protected]) t41left join (select * from t4 where dt<[email protected]) t42 on t41.IP=t42.IP and t41.rn=t42.rn-1)select IP,dt1 '开始时间',isnull(dt2,currenttime) '结束时间',datediff(mi,dt1,isnull(dt2,currenttime)) '差距'from t5where datediff(mi,dt1,isnull(dt2,currenttime))>5IP              开始时间                    结束时间                    差距--------------- ----------------------- ----------------------- -----------192.168.1.1     2012-02-01 09:00:00.000 2012-02-01 10:00:00.000    60192.168.1.2     2012-02-01 09:00:00.000 2012-02-01 10:00:00.000    60192.168.1.3     2012-02-01 09:01:00.000 2012-02-01 10:00:00.000    59192.168.1.4     2012-02-01 09:00:00.000 2012-02-01 09:07:00.000     7192.168.1.4     2012-02-01 09:08:00.000 2012-02-01 10:00:00.000    52192.168.1.5     2012-02-01 09:00:00.000 2012-02-01 10:00:00.000    60