求一条取时间的SQL语句

[lsp69]

有以下数据表：
ID DateTime
1 2014-05-03 09:17:23
1 2014-05-03 09:17:23
1 2014-05-03 09:16:23
2 2014-05-03 09:17:23
2 2014-05-03 09:27:23

要得出：同一个ID号的，10分钟之内的数据，只保留最新的一条

即可以得到下列结果:
ID DateTime
1 2014-05-03 09:16:23
2 2014-05-03 09:17:23
2 2014-05-03 09:27:23

[我是小数位]

drop table #test
create table #test(id int,dt datetime)
insert into #test(id,dt)
select '1','2014-05-03 09:17:23' union all
select '1','2014-05-03 09:17:23' union all
select '1','2014-05-03 09:16:23' union all
select '2','2014-05-03 09:17:23' union all
select '2','2014-05-03 09:27:23' union all
select '2','2014-05-03 09:37:23' union all
select '1','2014-05-03 09:25:23' union all
select '1','2014-05-03 09:26:23' union all
select '1','2014-05-03 09:36:23'

select x.id,x.dt from
(select a.id,a.dt,row_number() over(order by id,dt) as uid
from #test a) x
where not exists(select 1 from #test z where z.id=x.id and z.dt>x.dt)
or exists(select 1 from (select a.id,a.dt,row_number() over(order by id,dt) as uid
from #test a) y where y.id=x.id and y.uid=x.uid+1 and datediff(mi,x.dt,y.dt)>9 )
group by x.id,x.dt

[我是小数位]

结果
1	2014-05-03 09:26:23.000
1	2014-05-03 09:36:23.000
2	2014-05-03 09:17:23.000
2	2014-05-03 09:27:23.000
2	2014-05-03 09:37:23.000

[myue]

WITH t (ID,DateTime) AS 
(SELECT 1 , '2014-05-03 09:17:23' UNION ALL 
SELECT 1 ,'2014-05-03 09:17:23'UNION ALL 
SELECT 1, '2014-05-03 09:16:23'UNION ALL 
SELECT 2,'2014-05-03 09:17:23'UNION ALL 
SELECT 2,'2014-05-03 09:27:23' 
)
, t2 AS (SELECT ROW_NUMBER() OVER (PARTITION BY id ORDER BY id ,DateTime  )AS id1,* FROM t  )
SELECT * FROM t2 AS aa WHERE NOT  exists (SELECT * FROM t2 AS bb WHERE aa.id1+1=bb.id1  AND aa.id =bb.id   AND  DATEADD(minute,10,aa.datetime) > bb.datetime) 

[浩方软件HFWMS]

都写得太复杂了，而且使用getdate()是不行的

[浩方软件HFWMS]

引用 4 楼 t101lian 的回复:

 drop table #test
create table #test(id int,dt datetime)
insert into #test(id,dt)
select '1','2014-05-03 09:17:23' union all
select '1','2014-05-03 09:17:23' union all
select '1','2014-05-03 09:16:23' union all
select '2','2014-05-03 09:17:23' union all
select '2','2014-05-03 09:27:23'  

;with cte as 
(
  select *,row_number() over (order by id,getdate()) m from #test
)
select id,dt  from cte a   where 
 not exists (select 1 from cte where id=a.id and m=a.m+1 and 
abs(datediff(mi,dt,a.dt))<10)

/*
id          dt
----------- -----------------------
1           2014-05-03 09:16:23.000
2           2014-05-03 09:17:23.000
2           2014-05-03 09:27:23.000
*/

不是用getdate()比较的，是相同ID的时间段时，不超过10分钟的数据

[發糞塗牆]

他的getdate是用来排序而已，不是用来做比较。。。

[我是小数位]

引用 6 楼 lsp69 的回复:

[quote=引用 4 楼 t101lian 的回复:]

 drop table #test
create table #test(id int,dt datetime)
insert into #test(id,dt)
select '1','2014-05-03 09:17:23' union all
select '1','2014-05-03 09:17:23' union all
select '1','2014-05-03 09:16:23' union all
select '2','2014-05-03 09:17:23' union all
select '2','2014-05-03 09:27:23'  

;with cte as 
(
  select *,row_number() over (order by id,getdate()) m from #test
)
select id,dt  from cte a   where 
 not exists (select 1 from cte where id=a.id and m=a.m+1 and 
abs(datediff(mi,dt,a.dt))<10)

/*
id          dt
----------- -----------------------
1           2014-05-03 09:16:23.000
2           2014-05-03 09:17:23.000
2           2014-05-03 09:27:23.000
*/

不是用getdate()比较的，是相同ID的时间段时，不超过10分钟的数据[/quote]

引用 7 楼 DBA_Huangzj 的回复:

他的getdate是用来排序而已，不是用来做比较。。。

我只是用來按時間先後順序排序

[t101lian]

 drop table #test
create table #test(id int,dt datetime)
insert into #test(id,dt)
select '1','2014-05-03 09:17:23' union all
select '1','2014-05-03 09:17:23' union all
select '1','2014-05-03 09:16:23' union all
select '2','2014-05-03 09:17:23' union all
select '2','2014-05-03 09:27:23'  

;with cte as 
(
  select *,row_number() over (order by id,getdate()) m from #test
)
select id,dt  from cte a   where 
 not exists (select 1 from cte where id=a.id and m=a.m+1 and 
abs(datediff(mi,dt,a.dt))<10)

/*
id          dt
----------- -----------------------
1           2014-05-03 09:16:23.000
2           2014-05-03 09:17:23.000
2           2014-05-03 09:27:23.000
*/