34,588
社区成员
发帖
与我相关
我的任务
分享
这方法比较好用的方法NTILE()--按百分比删除
--> --> (Roy)生成測試數據
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([date] Datetime)
Insert #T
select '2011-01-01' union all
select '2011-01-01' union all
select '2011-01-01' union all
select '2011-01-01' union all
select '2011-01-01' union all
select '2011-01-01' union all
select '2011-01-01' union all
select '2011-01-01' union all
select '2011-01-01' union all
select '2011-01-01' union all
select '2011-01-02' union all
select '2011-01-02'
Go
delete t
from
(Select *,NTILE(10)over(partition by [date] order by [date]) as row from #T)t
where row<=8---删除80%
select * from #T
delete from tb where id in( --id列为唯一性列
select id from(
select id,row_number()over(partition by date order by newid())rn,(select count(*) from tb where date=a.date)ct from tb a
)t where rn*100/ct<=80 --删除80%