求SQL,请高手指点，要去除比上一个间时间隔小于10分钟的记录

Question

请大家给个思路，，总感到CSDN有高人，一试，实在不行就只好读到时数组一个一个比较长ScanTime
------------------------
2010-06-02 12:02:00.000
2010-06-02 12:04:00.000
2010-06-02 13:19:00.000
2010-06-02 17:33:00.000
2010-06-02 17:39:00.000
2010-06-02 17:43:00.000
2010-06-02 17:58:00.000
2010-06-02 20:25:00.000
要去除比上一个间时间隔小于10分钟的记录
2010-06-02 12:04:00.000
2010-06-02 17:39:00.000
如上要去掉注意：
2010-06-02 17:43:00.000
不能去掉
因为比2010-06-02 17:33:00.000　这个有效值多了10分钟

Answer 1

ScanTime
------------------------
2010-06-02 12:02:00.000
2010-06-02 12:04:00.000
2010-06-02 13:19:00.000
2010-06-02 17:33:00.000
2010-06-02 17:39:00.000
2010-06-02 17:43:00.000
2010-06-02 17:58:00.000
2010-06-02 20:25:00.000结果是什么?

Answer 2

result need :
----------------------------
2010-06-02 12:02:00.0002010-06-02 13:19:00.000
2010-06-02 17:33:00.0002010-06-02 17:43:00.000
2010-06-02 17:58:00.000
2010-06-02 20:25:00.000

Answer 3

create table tb(ScanTime datetime)
insert into tb values('2010-06-02 12:02:00.000')
insert into tb values('2010-06-02 12:04:00.000')
insert into tb values('2010-06-02 13:19:00.000')
insert into tb values('2010-06-02 17:33:00.000')
insert into tb values('2010-06-02 17:39:00.000')
insert into tb values('2010-06-02 17:43:00.000')
insert into tb values('2010-06-02 17:58:00.000')
insert into tb values('2010-06-02 20:25:00.000')
goselect scantime from
(
  select * , scantime2 = isnull((select top 1 scantime from tb where scantime > t.scantime order by scantime),scantime+1) from tb t
) m
where datediff(mi , scantime , scantime2) > 10drop table tb/*
scantime                                               
------------------------------------------------------ 
2010-06-02 12:04:00.000
2010-06-02 13:19:00.000
2010-06-02 17:43:00.000
2010-06-02 17:58:00.000
2010-06-02 20:25:00.000（所影响的行数为 5 行）
*/

Answer 4

ScanTime                                               
------------------------------------------------------ 
2010-06-02 12:04:00.000
2010-06-02 13:19:00.000
2010-06-02 17:43:00.000
2010-06-02 17:58:00.000
2010-06-02 20:25:00.000（所影响的行数为 5 行）

Answer 5

create table tb(ScanTime datetime)
insert into tb values('2010-06-02 12:02:00.000')
insert into tb values('2010-06-02 12:04:00.000')
insert into tb values('2010-06-02 13:19:00.000')
insert into tb values('2010-06-02 17:33:00.000')
insert into tb values('2010-06-02 17:39:00.000')
insert into tb values('2010-06-02 17:43:00.000')
insert into tb values('2010-06-02 17:58:00.000')
insert into tb values('2010-06-02 20:25:00.000')
goselect * from tb t where datediff(mi,scantime,isnull((select top 1 scantime from tb where scantime > t.scantime order by scantime),scantime+1)) >= 10drop table tb/*
ScanTime                                               
------------------------------------------------------ 
2010-06-02 12:04:00.000
2010-06-02 13:19:00.000
2010-06-02 17:43:00.000
2010-06-02 17:58:00.000
2010-06-02 20:25:00.000（所影响的行数为 5 行）*/

Answer 6

才知道你们都是老师呀，为什么不早CSDN呢

Answer 7

if object_id('tb') is not null drop table tb
go
create table tb(ScanTime datetime)
insert into tb values('2010-06-02 12:02:00.000')
insert into tb values('2010-06-02 12:04:00.000')
insert into tb values('2010-06-02 13:19:00.000')
insert into tb values('2010-06-02 17:33:00.000')
insert into tb values('2010-06-02 17:39:00.000')
insert into tb values('2010-06-02 17:43:00.000')
insert into tb values('2010-06-02 17:58:00.000')
insert into tb values('2010-06-02 20:25:00.000')
goselect scantime from tb t
where not exists( select 1 from tb where ScanTime<t.ScanTime and datediff(mi,ScanTime,t.ScanTime)<10  )--这个才是正确结果
/*
scantime                                               
------------------------------------------------------ 
2010-06-02 12:02:00.000
2010-06-02 13:19:00.000
2010-06-02 17:33:00.000
2010-06-02 17:58:00.000
2010-06-02 20:25:00.000（所影响的行数为 5 行）
*/
drop table tb

Answer 8

那就这样再来if object_id('tb') is not null drop table tb
go
create table tb(ScanTime datetime)
insert into tb values('2010-06-02 12:02:00.000')
insert into tb values('2010-06-02 12:04:00.000')
insert into tb values('2010-06-02 13:19:00.000')
insert into tb values('2010-06-02 17:33:00.000')
insert into tb values('2010-06-02 17:39:00.000')
insert into tb values('2010-06-02 17:43:00.000')
insert into tb values('2010-06-02 17:58:00.000')
insert into tb values('2010-06-02 20:25:00.000')
goselect scantime from tb a
where 
not exists(select 1 from 
(select scantime from tb t
where not exists( select 1 from tb where ScanTime<t.ScanTime and datediff(mi,ScanTime,t.ScanTime)<10  ))b
where a.ScanTime>b.ScanTime and datediff(mi,b.ScanTime,a.ScanTime)<10)
--这个结果可以了把
/*
scantime                                               
------------------------------------------------------ 
2010-06-02 12:02:00.000
2010-06-02 13:19:00.000
2010-06-02 17:33:00.000
2010-06-02 17:43:00.000
2010-06-02 17:58:00.000
2010-06-02 20:25:00.000（所影响的行数为 6 行）*/
drop table tb

Answer 9

着实太有才了，谢谢，马上结贴，也多谢
dawugui
(爱新觉罗.毓华)好好学习，我还没有消化这个SQL
THANKS