[数据库] 取指定表中某字段的累加和不超过总和80%的行

共生成了 1.1 万行测试数据， 测试用试 12 秒， 运行效果图和原数据如上图所示。

有人写出了百万行只要14秒的sql：

select CollectData as value,0.00000 as P,0.00000 as _P into #data
from (select top 1000000 * from EMS_HisData18) xx order by CollectData desc

declare @sum decimal(18,8)
declare @p decimal(18,8)=0.80

select @sum=sum(value) from #data

update #data set @p=@p-(value/@sum),_P=@p

select * from #data where _P>=0

drop table #data

[数据库] 取指定表中某字段的累加和不超过总和80%的行

标签：