请问每五条取一条记录怎么写
devID f1 f2 time
1 aa bb ....
1 bb cx ....
1 cc xx .....
1 aa bb ....
1 bb cx ....
1 cc xx .....
2 aa bb ....
2 bb cx ....
2 cc xx .....
2 aa bb ....
2 bb cx ....
2 cc xx .....

按照devID相同，每5条取一条记录
[解决办法]
select devID,f1,f2,time from(select *,row=row_number()over(partition by devid order by getdate()) from 表)t where t.row%5=1
[解决办法]
select devID,f1,f2,time
from (select devID,f1,f2,time,ROW_NUMBER() over(partition by devid order by getdate()) as rowid from tb ) as a
where rowid%5=1
[解决办法]

select devid,f1,f2,time from (select row_number() over(partition by devid order by devid)a,* from 表名)b where a=1 or a%5=0

[解决办法]
--> 测试数据：[TB]
if object_id('[TB]') is not null drop table [TB]
GO
create table [TB]([devID] int,[f1] varchar(2),[f2] varchar(2),[time] varchar(5))
insert [TB]
select 1,'aa','bb','....' union all
select 1,'bb','cx','....' union all
select 1,'cc','xx','.....' union all
select 1,'aa','bb','....' union all
select 1,'bb','cx','....' union all
select 1,'cc','xx','.....' union all
select 2,'aa','bb','....' union all
select 2,'bb','cx','....' union all
select 2,'cc','xx','.....' union all
select 2,'aa','bb','....' union all
select 2,'bb','cx','....' union all
select 2,'cc','xx','.....'

SELECT [devID],[f1],[f2],[time] FROM(
select *,flag=((ROW_NUMBER() OVER ( PARTITION BY devID ORDER BY f1))-1)%5 from [TB]
)g
WHERE g.flag=0

/*
devID f1 f2 time
----------- ---- ---- -----
1 aa bb ....
1 cc xx .....
2 aa bb ....
2 cc xx .....

(4 行受影响)

*/

drop table [TB]

------解决方案--------------------

引用:

SQL code
?



12

select devid,f1,f2,time from (select row_number() over(partition by devid order by devid)a,* from 表名)b where a=1 or a%5=0

这就是个排序，无所谓的...