求小于5分钟的数据

Question

有一张表，有几万条数据，格式是这样子的
a time
7496 2011-01-20 09:28:01.430
6927 2011-01-20 04:39:38.360
6927 2011-01-20 04:39:39.580
5041 2011-01-20 05:10:09.880
5041 2011-01-20 05:10:09.240
5041 2011-01-20 20:34:52.760
5041 2011-01-20 20:34:52.050
7139 2011-01-20 19:42:23.440
7139 2011-01-20 19:42:24.620
7139 2011-01-20 08:41:00.780
7139 2011-01-20 08:41:02.020
7139 2011-01-20 19:38:48.730
7139 2011-01-20 19:38:49.900
7729 2011-01-20 07:55:51.610
7729 2011-01-20 07:55:52.320
7729 2011-01-20 08:05:23.630
7729 2011-01-20 08:05:22.940
7729 2011-01-20 11:27:09.260
7729 2011-01-20 11:27:09.970
7729 2011-01-20 12:00:43.190
7729 2011-01-20 12:00:42.490
7729 2011-01-20 14:37:05.330
7729 2011-01-20 14:37:04.640
7729 2011-01-20 11:37:31.790
7729 2011-01-20 11:37:31.100我想找到所有相差不到5分钟的数据，a字段有重复，但是时间不相同。数据格式如上图，我想找到和7496,6927等相差5分钟的时间，

Answer 1

select a from tb t 
where datediff(mi,[time],(select top 1 [time] from tb where [time]<t.[time])<5

Answer 2

create table #temp
(
a int,
[time] datetime
)
insert #temp
select 7496, '2011-01-20 09:28:01.430' union all
select 6927, '2011-01-20 04:39:38.360' union all
select 6927, '2011-01-20 04:39:39.580' union all
select 5041, '2011-01-20 05:10:09.880' union all
select 5041, '2011-01-20 05:10:09.240' union all
select 5041, '2011-01-20 20:34:52.760' union all
select 5041, '2011-01-20 20:34:52.050' union all
select 7139, '2011-01-20 19:42:23.440' union all
select 7139, '2011-01-20 19:42:24.620' union all
select 7139, '2011-01-20 08:41:00.780' union all
select 7139, '2011-01-20 08:41:02.020' union all
select 7139, '2011-01-20 19:38:48.730' union all
select 7139, '2011-01-20 19:38:49.900' union all
select 7729, '2011-01-20 07:55:51.610' union all
select 7729, '2011-01-20 07:55:52.320' union all
select 7729, '2011-01-20 08:05:23.630' union all
select 7729, '2011-01-20 08:05:22.940' union all
select 7729, '2011-01-20 11:27:09.260' union all
select 7729, '2011-01-20 11:27:09.970' union all
select 7729, '2011-01-20 12:00:43.190' union all
select 7729, '2011-01-20 12:00:42.490' union all
select 7729, '2011-01-20 14:37:05.330' union all
select 7729, '2011-01-20 14:37:04.640' union all
select 7729, '2011-01-20 11:37:31.790' union all
select 7729, '2011-01-20 11:37:31.100'
go
--SQL:
--#1.
SELECT *, flag=null into # FROM #temp ORDER BY [time]
--#2.
declare @time datetime
set @time = (select MIN([time]) from #)update #
set flag = case when @time = [time] then 1 else 0 end,
@time = case when DATEDIFF(second, @time, [time]) >= 300 then [time] else @time end
--#3.1
select * from # where flag = 1
--#3.2
select n.a, n.[time] from
(select distinct a from # where flag = 1) m
cross apply
(select top(1) * from # where flag = 1 and a = m.a) n--RESULT
/*
a time
5041 2011-01-20 05:10:09.240
6927 2011-01-20 04:39:38.360
7139 2011-01-20 08:41:00.780
7496 2011-01-20 09:28:01.430
7729 2011-01-20 07:55:51.610
*/

Answer 3

select distinct * from tb t 
where exists (select 1 from tb where a=t.a and abs(datediff(ss,[time],t.[time])=300)) 

Answer 4

--建表
create table #Date(a int, time datetime)
--数据
insert into #Date
select '7496','2011-01-20 09:28:01.430'
union all select '6927','2011-01-20 04:39:38.360'
union all select '6927','2011-01-20 04:39:39.580'
union all select '5041','2011-01-20 05:10:09.880'
union all select '5041','2011-01-20 05:10:09.240'
union all select '5041','2011-01-20 20:34:52.760'
union all select '5041','2011-01-20 20:34:52.050'
union all select '7139','2011-01-20 19:42:23.440'
union all select '7139','2011-01-20 19:42:24.620'
union all select '7139','2011-01-20 08:41:00.780'
union all select '7139','2011-01-20 08:41:02.020'
union all select '7139','2011-01-20 19:38:48.730'
union all select '7139','2011-01-20 19:38:49.900'
union all select '7729','2011-01-20 07:55:51.610'
union all select '7729','2011-01-20 07:55:52.320'
union all select '7729','2011-01-20 08:05:23.630'
union all select '7729','2011-01-20 08:05:22.940'
union all select '7729','2011-01-20 11:27:09.260'
union all select '7729','2011-01-20 11:27:09.970'
union all select '7729','2011-01-20 12:00:43.190'
union all select '7729','2011-01-20 12:00:42.490'
union all select '7729','2011-01-20 14:37:05.330'
union all select '7729','2011-01-20 14:37:04.640'
union all select '7729','2011-01-20 11:37:31.790'
union all select '7729','2011-01-20 11:37:31.100'
--结果
select distinct m.a,m.time,n.a,n.time from #Date as m, #Date as n
where abs(datediff(mm,m.time,n.time))<=5 and m.time<>n.time --
5041        2011-01-20 05:10:09.240                                7139        2011-01-20 19:38:48.730
5041        2011-01-20 05:10:09.240                                7139        2011-01-20 19:38:49.900
5041        2011-01-20 05:10:09.240                                7139        2011-01-20 19:42:23.440
5041        2011-01-20 05:10:09.240                                7139        2011-01-20 19:42:24.620
5041        2011-01-20 05:10:09.240                                7496        2011-01-20 09:28:01.430
5041        2011-01-20 05:10:09.240                                7729        2011-01-20 07:55:51.610
5041        2011-01-20 05:10:09.240                                7729        2011-01-20 07:55:52.320
5041        2011-01-20 05:10:09.240                                7729        2011-01-20 08:05:22.940
5041        2011-01-20 05:10:09.240                                7729        2011-01-20 08:05:23.630
5041        2011-01-20 05:10:09.240                                7729        2011-01-20 11:27:09.260
5041        2011-01-20 05:10:09.240                                7729        2011-01-20 11:27:09.970
....