请问如何每5分钟取一条数据

[ahking]

数据类似下面：
usrid t1 其他字段............
1 2012-11-11 11:11
1 2012-11-11 11:12
1 2012-11-11 11:13
1 2012-11-11 11:14
1 2012-11-11 11:15
1 2012-11-11 11:16
1 2012-11-11 11:17
1 2012-11-11 11:18
1 2012-11-11 11:19
1 2012-11-11 11:20
1 2012-11-11 11:21
1 2012-11-11 11:22
1 2012-11-11 11:23
1 2012-11-11 11:24
1 2012-11-11 11:25
1 2012-11-11 11:26
1 2012-11-11 11:27
1 2012-11-11 11:28
1 2012-11-11 11:29
2 2012-11-11 11:11
2 2012-11-11 11:12
2 2012-11-11 11:13
2 2012-11-11 11:14
2 2012-11-11 11:15
2 2012-11-11 11:16
2 2012-11-11 11:17
2 2012-11-11 11:18
2 2012-11-11 11:19
2 2012-11-11 11:20
2 2012-11-11 11:21
2 2012-11-11 11:22
2 2012-11-11 11:23
2 2012-11-11 11:24
2 2012-11-11 11:25
2 2012-11-11 11:26
2 2012-11-11 11:27
2 2012-11-11 11:28
2 2012-11-11 11:29

得到结果为

usrid t1 其他字段.....
1 2012-11-11 11:11
1 2012-11-11 11:16
1 2012-11-11 11:21
1 2012-11-11 11:26
2 2012-11-11 11:11
2 2012-11-11 11:16
2 2012-11-11 11:21
2 2012-11-11 11:26

[坚_持]

declare @tb table (usrid int,t1 datetime)
insert into @tb
select 1,'2012-11-11 11:11' union all
select 1,'2012-11-11 11:12' union all
select 1,'2012-11-11 11:13' union all
select 1,'2012-11-11 11:14' union all
select 1,'2012-11-11 11:15' union all
select 1,'2012-11-11 11:16' union all
select 1,'2012-11-11 11:17' union all
select 1,'2012-11-11 11:18' union all
select 1,'2012-11-11 11:19' union all
select 1,'2012-11-11 11:20' union all
select 1,'2012-11-11 11:21' union all
select 1,'2012-11-11 11:22' union all
select 1,'2012-11-11 11:23' union all
select 1,'2012-11-11 11:24' union all
select 1,'2012-11-11 11:25' union all
select 1,'2012-11-11 11:26' union all
select 1,'2012-11-11 11:27' union all
select 1,'2012-11-11 11:28' union all
select 1,'2012-11-11 11:29' union all
select 2,'2012-11-11 11:11' union all
select 2,'2012-11-11 11:12' union all
select 2,'2012-11-11 11:13' union all
select 2,'2012-11-11 11:14' union all
select 2,'2012-11-11 11:15' union all
select 2,'2012-11-11 11:16' union all
select 2,'2012-11-11 11:17' union all
select 2,'2012-11-11 11:18' union all
select 2,'2012-11-11 11:19' union all
select 2,'2012-11-11 11:20' union all
select 2,'2012-11-11 11:21' union all
select 2,'2012-11-11 11:22' union all
select 2,'2012-11-11 11:23' union all
select 2,'2012-11-11 11:24' union all
select 2,'2012-11-11 11:25' union all
select 2,'2012-11-11 11:26' union all
select 2,'2012-11-11 11:27' union all
select 2,'2012-11-11 11:28' union all
select 2,'2012-11-11 11:29'
if OBJECT_ID('tb') is not null
drop table tb
;with tb as
(
select * from @tb a where a.usrid not in(select usrid from @tb where usrid=a.usrid and t1<a.t1)
union all
select a.* from @tb a,tb b where a.usrid=b.usrid and datediff(mi,b.t1,a.t1)=5
)
select * from tb order by usrid,t1

[-晴天]

create table tb(usrid int,t1 datetime) insert into tb select 1,'2012-11-11 11:11' union all select 1,'2012-11-11 11:12' union all select 1,'2012-11-11 11:13' union all select 1,'2012-11-11 11:14' union all select 1,'2012-11-11 11:15' union all select 1,'2012-11-11 11:16' union all select 1,'2012-11-11 11:17' union all select 1,'2012-11-11 11:18' union all select 1,'2012-11-11 11:19' union all select 1,'2012-11-11 11:20' union all select 1,'2012-11-11 11:21' union all select 1,'2012-11-11 11:22' union all select 1,'2012-11-11 11:23' union all select 1,'2012-11-11 11:24' union all select 1,'2012-11-11 11:25' union all select 1,'2012-11-11 11:26' union all select 1,'2012-11-11 11:27' union all select 1,'2012-11-11 11:28' union all select 1,'2012-11-11 11:29' union all select 2,'2012-11-11 11:11' union all select 2,'2012-11-11 11:12' union all select 2,'2012-11-11 11:13' union all select 2,'2012-11-11 11:14' union all select 2,'2012-11-11 11:15' union all select 2,'2012-11-11 11:16' union all select 2,'2012-11-11 11:17' union all select 2,'2012-11-11 11:18' union all select 2,'2012-11-11 11:19' union all select 2,'2012-11-11 11:20' union all select 2,'2012-11-11 11:21' union all select 2,'2012-11-11 11:22' union all select 2,'2012-11-11 11:23' union all select 2,'2012-11-11 11:24' union all select 2,'2012-11-11 11:25' union all select 2,'2012-11-11 11:26' union all select 2,'2012-11-11 11:27' union all select 2,'2012-11-11 11:28' union all select 2,'2012-11-11 11:29' go ;with cte as( select * from tb a where not exists(select 1 from tb where usrid=a.usrid and t1<a.t1) union all select a.* from tb a inner join cte b on a.usrid=b.usrid and datediff(mi,b.t1,a.t1)=5 ) select * from cte order by usrid,t1 /* usrid t1 ----------- ----------------------- 1 2012-11-11 11:11:00.000 1 2012-11-11 11:16:00.000 1 2012-11-11 11:21:00.000 1 2012-11-11 11:26:00.000 2 2012-11-11 11:11:00.000 2 2012-11-11 11:16:00.000 2 2012-11-11 11:21:00.000 2 2012-11-11 11:26:00.000 (8 行受影响) */ go drop table tb

[qq576420473]

引用 2 楼 zhoulong911217 的回复:

declare @tb table (usrid int,t1 datetime) insert into @tb select 1,'2012-11-11 11:11' union all select 1,'2012-11-11 11:12' union all select 1,'2012-11-11 11:13' union all select 1,'2012-11-11……

大侠！求指点迷津呀，想了很久，不太明白。。。@zhoulong911217

[clisdodo]

引用 4 楼 galenkeny 的回复:

引用 3 楼 qq576420473 的回复: 引用 2 楼 zhoulong911217 的回复: declare @tb table (usrid int,t1 datetime) insert into @tb select 1,'2012-11-11 11:11' union all select 1,'2012-11-11 11:12' union all select ……

你好，想请教下，既然这条语句select a.* from tb a inner join cte b on a.usrid=b.usrid and datediff(mi,b.t1,a.t1)=5查询的是时间跨度为5分钟的数据，那么第一句select * from tb a where not exists(select 1 from tb where usrid=a.usrid and t1<a.t1)这条语句查询的是id相同的一组数据中最大的t1,取值为'2012-11-11 11:29' 和'2012-11-11 11:29'，这两个值有什么意义呢？即使用union all和第二句联合起来查询，还是想不通是个什么意思。请各位大虾指点一下，感激……

[dd_2012]

引用 5 楼 HenryPointT 的回复:

引用 4 楼 galenkeny 的回复:引用 3 楼 qq576420473 的回复: 引用 2 楼 zhoulong911217 的回复: declare @tb table (usrid int,t1 datetime) insert into @tb select 1,'2012-11-11 11:11' union all select 1,'201……

先取每个不同usrid的最初的那条记录，再以此为标准找它们后面对应的记录

[习惯性蹭分]

使用公用表表达式（CTE）简化嵌套SQL 你可以看看这个.

[以学习为目的]

引用 3 楼 qq576420473 的回复:

引用 2 楼 zhoulong911217 的回复: declare @tb table (usrid int,t1 datetime) insert into @tb select 1,'2012-11-11 11:11' union all select 1,'2012-11-11 11:12' union all select 1,'2012-11-11 11:13' unio……

select * from tb a where not exists(select 1 from tb where usrid=a.usrid and t1<a.t1) 这个是取id相同的一组数据中最大的t1 select a.* from tb a inner join cte b on a.usrid=b.usrid and datediff(mi,b.t1,a.t1)=5 这个查询时间跨度为5分钟的数据，注意是要内联上面一个查询的 然后使用union all 连合起来

[ChangeMyself2012]

说实话，CTE这种用法自己也没怎么用过，查了下资料结合上面的代码说说我的理解，说的不对的还望包涵。

;with cte as(--这里的cte 相当于一个临时表
select * from tb a where not exists(select 1 from tb where usrid=a.usrid and t1<a.t1)
   --这一句取的是分别同一usrid的最小时间的记录
   --对应下面的记录：
   -- 1  2012-11-11 11:11:00.000
   -- 2  2012-11-11 11:11:00.000
union all 
   --用于联合上面和下面的结果集
select a.* from tb a inner join cte b on a.usrid=b.usrid and datediff(mi,b.t1,a.t1)=5
   --这条语句基本就是这个查询语句的点睛之笔
   --inner join 内联取交集 注意这里内联的对象不是"tb"而是"cte "(上面的有说到)这里其实就好比
一般我们在程序中写的递归以.这里就是以上一次的结果记录作为这一次的查询条件（datediff(mi,b.t1,a.t1)=5）做查询。
   --第一次得到结果集
   1  2012-11-11 11:16:00.000
   2  2012-11-11 11:16:00.000
   --第二次得到结果集
   1  2012-11-11 11:21:00.000
   2  2012-11-11 11:21:00.000
   --第三次得到结果集
   1  2012-11-11 11:26:00.000
   2  2012-11-11 11:26:00.000
   --第四次
   --.........
)
select * from cte order by usrid,t1

[asdf544265772]

我感觉 如果你数据库里存的是每个一分钟一条数据 有规律的话 直接截取t1这个时间字符串不就好了 具体：截取最后两位 转化为int 减1 然后除以5 取余数 如果余数是0 说明就是符合条件的数据 能出来，可能很不主流，你要是时间不够的话 可以考虑这个方法

[ahking]

引用 1 楼 qianjin036a 的回复:

create table tb(usrid int,t1 datetime) insert into tb select 1,'2012-11-11 11:11' union all select 1,'2012-11-11 11:12' union all select 1,'2012-11-11 11:13' union all select 1,'2012……

select a.* from tb a inner join cte b on a.usrid=b.usrid and datediff(mi,b.t1,a.t1)=5 这里的cte是什么意思，不能直接用tb么？

[Sylaro0]

你可以把cte理解为运行 select * from tb a where not exists(select 1 from tb where usrid=a.usrid and t1<a.t1) 这段查询语句得到的表，这个表的名字叫cte select a.* from tb a inner join cte b on a.usrid=b.usrid and datediff(mi,b.t1,a.t1)=5 这里把tb这张表和cte这张表做了个连接查询，然后得到你想要的结果

[ahking]

如果不用with方式的写法，该如何写该语句呢？ select * from tb a where not exists(select 1 from tb where usrid=a.usrid and t1<a.t1 union all select a.* from tb a inner join (select * from tb a where not exists(select 1 from tb where usrid=a.usrid and t1<a.t1) b on a.usrid=b.usrid and datediff(mi,b.t1,a.t1)=5 这样行么？

[ChangeMyself2012]

不会，坐等大牛。但是如果要是真用with的话还有另外一种写法

--数据直接用一楼的(此处略)
--查询
;with T as(
select ROW_NUMBER() over(partition by usrid order by getdate()) id,* from tb
)
select usrid,t1 from T where (id-1)%5=0

/*查询结果
usrid       t1
----------- -----------------------
1           2012-11-11 11:11:00.000
1           2012-11-11 11:16:00.000
1           2012-11-11 11:21:00.000
1           2012-11-11 11:26:00.000
2           2012-11-11 11:11:00.000
2           2012-11-11 11:16:00.000
2           2012-11-11 11:21:00.000
2           2012-11-11 11:26:00.000

(8 行受影响)
*/

[ahking]

with是不是主要用在测试？

[ahking]

up?????????????????

[ahking]

如果需要查询某个时间段的数据，在cte中哪里加条件速度最快