如何获得大于一定数量的总和

Question

我想使用一个SQL，使接收者的接收数量> = 1024，转移数量<= 3。

约翰逊之所以被列出是因为列出了约翰逊帐户，因为它在以下三个转账中收到了1112美元：512美元+ 100美元+
500美元，泰勒进行了1次转账1024美元。威廉姆斯不在，因为他在四笔交易中收到1200美元。

我尝试

Select recipient as account_name from transfers group by recipient 
having sum(amount)>=1024  and count(amount)<=3

它不能正常工作。我正在使用PostgreSQL，SQLLites语法也很好。

随附表和行的创建，为您提供方便

 create table transfers (
      sender varchar(1000) not null,
      recipient varchar(1000) not null,
      date date not null,
      amount integer not null
  );

insert into transfers values('Smith','Taylor',convert(date,'2002-09-27'),'1024')
insert into transfers values('Smith','Johnson',convert(date,'2005-06-26'),'512')
insert into transfers values('Williams','Johnson',convert(date,'2010-12-17'),'100')
insert into transfers values('Williams','Johnson',convert(date,'2004-03-22'),'10')
insert into transfers values('Brown','Johnson',convert(date,'2013-03-20'),'500')
insert into transfers values('Johnson','Williams',convert(date,'2007-06-02'),'400')
insert into transfers values('Johnson','Williams',convert(date,'2005-06-26'),'400')
insert into transfers values('Johnson','Williams',convert(date,'2005-06-26'),'200')

Answer 1

使用row_number()和派生表将每笔recipient收入限制在前三位，然后通过recipient返回sum(amount)>=1024

select recipient as account_name
from (
  select * 
    , row_number() over (
        partition by recipient
        order by amount desc
        ) as rn
  from transfers
  ) as i
where rn < 4
group by recipient
having sum(amount)>=1024

返回：

+--------------+
| account_name |
+--------------+
| Johnson      |
| Taylor       |
+--------------+

rextester postgres演示：http
://rextester.com/PFR74297

对问题进行了编辑，从问题的第3修订版中删除了一些相关信息：已经尝试过什么。

我尝试

Select recipient as account_name from transfers group by recipient
having sum(amount)>=1024 and count(amount)<=3

它不能正常工作。

根据这些信息，我得出结论，OP希望从接收者的任何转移recipients中获得sum(amount)>=10243个或更少的收款人-
不限于转移次数小于或等于3的那些接收者sum(amount)>=1024。