查询时间间隔大于五分钟的数据

USE tempdb IF OBJECT_ID('TEST') IS NOT NULL  DROP TABLE TEST;      --> 如果表TEST不为空,删除表GO  CREATE TABLE TEST       --> 创建表,字段及类型如下(  dtime  datetime,  name   char(3));GOINSERT INTO TEST VALUES --> 向表中插入数据('2012-9-9 12:00','aaa'),('2012-9-9 12:03','aaa'),('2012-9-9 12:06','aaa'),('2012-9-9 12:09','aaa'),('2012-9-9 12:12','aaa'),('2012-9-9 12:00','bbb'),('2012-9-9 12:04','bbb'),('2012-9-9 12:10','bbb');GO SELECT * FROM TEST;      --> 表的结构如下GO/*dtime                   name----------------------- ----2012-09-09 12:00:00.000 aaa2012-09-09 12:03:00.000 aaa2012-09-09 12:06:00.000 aaa2012-09-09 12:09:00.000 aaa2012-09-09 12:12:00.000 aaa2012-09-09 12:00:00.000 bbb2012-09-09 12:04:00.000 bbb2012-09-09 12:10:00.000 bbb*/--------------------开始查询-------------------- --------------------期待结果--------------------/*dtime                   name----------------------- ----2012-09-09 12:00:00.000 aaa2012-09-09 12:06:00.000 aaa2012-09-09 12:12:00.000 aaa2012-09-09 12:00:00.000 bbb2012-09-09 12:10:00.000 bbb*/

select distinct a.name,b.dtime into #tafrom test a cross apply (select top(1) dtime from test where name = a.name order by dtime )bwhile 1 = 1begininsert into #taselect b.name,b.dtime from(select name,max(dtime)dtime from #ta group by name)a cross apply (select top(1) name, dtime from test where name = a.name and datediff(mi,a.dtime,dtime)> 5 order by dtime )b IF @@rowcount < 1 BREAKendselect * from #ta order by name,dtime/*name dtime---- -----------------------aaa  2012-09-09 12:00:00.000aaa  2012-09-09 12:06:00.000aaa  2012-09-09 12:12:00.000bbb  2012-09-09 12:00:00.000bbb  2012-09-09 12:10:00.000(5 行受影响)*/
------解决方案--------------------
测了一下37楼的，数据有误
SQL code
select distinct name,dtimefrom(select *,datepart(minute,dtime-(select min(dtime) from test where t.name=name))minute from test t)Awhere minute=0 or minute>5order by name,dtime/*name dtime---- -----------------------aaa  2010-06-01 00:05:00.000aaa  2010-06-01 00:28:00.000aaa  2010-06-01 00:34:00.000---aaa  2010-06-01 00:38:00.000---这里不符合题意aaa  2010-06-01 00:42:00.000---aaa  2010-06-01 00:53:00.000aaa  2010-06-01 00:57:00.000aaa  2010-06-01 00:59:00.000aaa  2010-06-01 01:04:00.000aaa  2010-06-01 01:05:00.000aaa  2010-06-01 01:15:00.000aaa  2010-06-01 01:16:00.000aaa  2010-06-01 01:17:00.000aaa  2010-06-01 01:25:00.000aaa  2010-06-01 01:31:00.000*/--用于生成示例数据if OBJECT_ID('Test') is not null drop table Testcreate table Test(id int identity(1,1), dtime  datetime,  name   char(3))INSERT INTO dbo.Test SELECT  dateadd(MI,1+abs(checksum(newid()))%(900), '2010-06-01'),                 case when abs(checksum(newid()))%(5) = 0 then 'aaa'         when abs(checksum(newid()))%(4) = 0 then 'bbb'        when abs(checksum(newid()))%(3) = 0 then 'ccc'        when abs(checksum(newid()))%(2) = 0 then 'ddd'         else 'eee' endDECLARE @i AS INT, @rc AS INT,@max int;SET @rc = 1;SET @max = 1000;set @i = ceiling(log10(@max)/LOG10(2))-1;WHILE @rc  <= @iBEGIN    INSERT INTO dbo.Test SELECT  dateadd(MI,1+abs(checksum(newid()))%(900), '2010-06-01'),                 case when abs(checksum(newid()))%(5) = 0 then 'aaa'         when abs(checksum(newid()))%(4) = 0 then 'bbb'        when abs(checksum(newid()))%(3) = 0 then 'ccc'        when abs(checksum(newid()))%(2) = 0 then 'ddd'         else 'eee' end  FROM dbo.Test;  SET @rc = @rc +1 ;ENDselect @max = @max - @@IDENTITYINSERT INTO dbo.Test   SELECT top(@max) dateadd(MI,1+abs(checksum(newid()))%(900), '2010-06-01'),                 case when abs(checksum(newid()))%(5) = 0 then 'aaa'         when abs(checksum(newid()))%(4) = 0 then 'bbb'        when abs(checksum(newid()))%(3) = 0 then 'ccc'        when abs(checksum(newid()))%(2) = 0 then 'ddd'         else 'eee' end  FROM dbo.Test                 SELECT * FROM TEST  order by name,dtime    --> 表的结构如下GO