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
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
解决方案 »
- mysql的安装程序有100多兆,discuz里的mysql才10几兆,差别咋这么大?
- select * from sys.dm_tran_locks里面锁的种类有哪些,是靠哪个字段区分的
- asp中sqlserver数据库连接问题.
- SqlServer2008排序规则,哪个是UTF-8
- 更新一张表的列,却需要比较比较另一张表的3列取出这列的SQL语句。
- 请教一个获得所有上级部门的sql语句,希望尽可能优化
- 请教:如何进行模糊比较。
- 请问下case when 的问题
- 请教下大家,存储过程中怎么做判断处理?
- group by 问题急用,在线等
- 关于查询重复数据的效率问题
- sqlserver 中如何创建组合查询?求急
(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)
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()比较的,是相同ID的时间段时,不超过10分钟的数据我只是用來按時間先後順序排序