[Database] takes the sum of a field in a specified table and does not exceed the total of 80% rows

There are table table_1, field Value int, P float. 5

To remove the P field in the reverse order of the Value field and the row that does not exceed 80% of the sum of the P fields in the entire table. and add the field SUM to the return list to hold the current row and all the preceding rows.

Tossing for a half day, wrote the following query sql:

Declare @e floatSelect @e = sumP from [Table_1]Set @e = @e * 0.8; withT as (        Select [ID] =Row_number () Over(Order  by [Value] desc),[Value],[P],            (Select sumP from [Table_1]AwhereA.[Value] >=B.[Value])'SUM'         from [Table_1]b)Select *  fromTwhere [SUM] <= @e

The symbiosis became 11,000 rows of test data, the test was tested for 12 seconds, and the original data was run as shown.

Someone wrote a million-line SQL for as long as 14 seconds:

SelectCollectdata asValue0.00000  asP0.00000  as_p into#data from(Select Top 1000000 *  fromEMS_HISDATA18) XXOrder  byCollectdatadescDeclare @sum decimal( -,8)Declare @p decimal( -,8)=0.80Select @sum=sum(value) from#dataUpdate#dataSet @p=@p-(Value/@sum), _p=@pSelect *  from#datawhere_p>=0Drop Table#data

[Database] takes the sum of a field in a specified table and does not exceed the total of 80% rows