请问每五条取一条记录怎么写 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 .....
