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

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

SQL code

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*/

SQL code

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 
 
[解决办法]
想到两种方法：
SQL code
--方法1：递归;WITH t AS(    SELECT ID=ROW_NUMBER() OVER(PARTITION BY name ORDER BY dtime),* FROM TEST ),t1 AS(    SELECT ID,name,dtime,1 AS IsValid FROM t WHERE ID = 1    UNION ALL    SELECT b.ID,b.name        ,CASE WHEN DATEADD(MI,5,a.dtime) <= b.dtime THEN b.dtime ELSE a.dtime END        ,CASE WHEN DATEADD(MI,5,a.dtime) <= b.dtime THEN 1 ELSE 0 END    FROM t1 a JOIN t b ON a.name = b.name AND a.ID = b.ID - 1)SELECT * FROM t1 WHERE IsValid = 1 ORDER BY name,ID OPTION(MAXRECURSION 0)
[解决办法]
SQL code
--方法2：循环SELECT ID=ROW_NUMBER() OVER(ORDER BY name,dtime),*,0 AS IsValid INTO #t FROM TEST CREATE CLUSTERED INDEX CIX_#t ON #t(ID)DECLARE @id INT = 1, @maxid INT    , @oname char(3)='',@nname char(3)    ,@otime datetime='1753-1-1',@ntime datetimeSELECT @maxid = MAX(ID) FROM #tWHILE @id <= @maxidBEGIN    SELECT @nname=name,@ntime=dtime FROM #t WHERE ID = @id    IF @id = 1 OR @nname <> @oname OR DATEDIFF(mi,@otime,@ntime) >= 5    BEGIN        UPDATE #t SET IsValid = 1 WHERE ID = @id        SELECT @otime = @ntime, @oname = @nname    END        SET @id += 1ENDSELECT * FROM #t WHERE IsValid = 1 order by ID