日期:2014-05-18  浏览次数:20504 次

抽样的问题····
现在需要写一个抽样的语句,需要在表中每隔 1000 条抽出一条数据,一共要抽出1000条。

  请问下有大神们有什么意见和思路没,给点,先谢谢。

  在线等。

------解决方案--------------------
SQL code

;with t as 
(
   select row_number() over (order by id) as Row ,* from [Table]
)

select * from t where r % 1000 = 0
select * from t where r % 1000 = 1
select * from t where r % 1000 = 2
select * from t where r % 1000 = 3
......
select * from t where r % 1000 = 999

------解决方案--------------------
SQL code

;with ach as
(
    select *,rid=row_number() over (order by getdate())
    from tb
),cte as
(
    select *,nid=row_number() over (partition by (rid-1)/1000 order by rid)
    from ach
)

select *
from cte
where nid = convert(int,rand()*1000)

------解决方案--------------------
select identity(int,1,1) id,* into #t from t

select * from #t where id%1000 = 1 order by id