如何根据每个组的两个最大值计算总和(组)

Question

假设，我有下面的表：

| Name     | Voters 
| George   | 10     
| Barack   | 20   
| Barack   | 50
| Barack   | 40
| Donald   | 30     
| Bill     | 20     
| George   | 10     
| Bill     | 30     
| Bill     | 15      
| Bill     | 10

我想获得每个名称(或更少(的2个选民的总和

| Name     | Sum2BiggestVoters 
| George   | 20 (10 + 10)
| Donald   | 30 (30)     
| Bill     | 20 (30 + 20; 10 and 15 are not considered not part of the 2 biggest numbers of voters for Bill)
| Barack   | 90 (50 + 40; 20 is not considered here for the same reason as above) 

看起来有点像这篇文章：如何以大于一定数量的数量获得总和，除了我使用的是缺少row_number() over功能以及partition by的sqlite。

有什么想法？

Answer 1

这是sqlite的痛苦，因为它不支持变量，也不支持窗口功能。假设您对给定的name没有联系，则可以做：

select t.name, sum(t.voters)
from t
where t.voters in (select t2.voters
                   from t t2
                   where t.name = t2.name
                   order by t2.voters desc
                   limit 2
                  )
group by t.name;