如何选择总计不超过60％的值 [英] how to select values that sum up to 60% of the total

问题描述

假设我有一个这样的表:

assume I have a table of this kind:

A B 3
C D 1
E F 2
G H 4

A B 3
C D 1
E F 2
G H 4

最后一列的总和为10，我希望最大的总和至少为总价值的60％.因此，在这种情况下，它将返回

The sum of the last column is 10, and I want the biggest values that sum up to at least 60% of the total value. So, in this case, it will return

G H 4
A B 3

G H 4
A B 3

它上升到70％，但如果仅选择第一个值，它将上升到40％.即使可能会有组合返回正好60％的组合，我们也要取最大的数字.

It goes up to 70% but if only the 1st value was selected, it will only go up to 40%. Even though there could be a combination that will return exactly 60%, we want to take the largest numbers.

因此，我想我知道如何从最大到最小对值进行排序以及如何对所有值求和，但是我不知道如何只取总和为60％的行.

So, I think I know how to sort the values from biggest to smallest and how to sum up all the values, but I don't know how to then take only lines that sum up to 60%.

推荐答案

--save the whole sum into a variable
summa = select sum(val) from sometable;

select * 
  from sometable o 
 where (
        select sum(val) 
          from sometable i 
         where i.val <= o.val
       ) >= 0.6*summa;

这篇关于如何选择总计不超过60％的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！