SQL:- 我正在尝试获取表中的行，其中最多 3 行的数量应该 <=1024

Question

这是我的 table

   sender     | recipient  | date       | amount
  ------------+------------+------------+--------
   Smith      | Williams   | 2000-01-01 | 200
   Smith      | Taylor     | 2002-09-27 | 1024
   Smith      | Johnson    | 2005-06-26 | 512
   Williams   | Johnson    | 2010-12-17 | 100
   Williams   | Johnson    | 2004-03-22 | 10
   Brown      | Johnson    | 2013-03-20 | 500
   Johnson    | Williams   | 2007-06-02 | 400
   Johnson    | Williams   | 2005-06-26 | 400
   Johnson    | Williams   | 2005-06-26 | 200

查询应该只返回 taylor 和 Johnson

因为 taylor 在一行中有 1024，而 johnson 在 3 行中得到它们 (512, 100, 500 = 1112) 但 williams 没有，因为需要四行才能超过 1024

我试过这个查询:

select   
    q1.r, q1.sum1, q1.c 
from
    (select  
         recipient as r, count(*) as c, sum(amount) as sum1
     from 
         transfers 
     group by 
         recipient) as q1
where 
    c <= 3 AND sum1 <= 1024

Answer 1

您可以获得每个recipient 的TOP 3 金额，然后使用SUM 和HAVING:

WITH Cte AS(
    SELECT *,
        ROW_NUMBER() OVER(PARTITION BY recipient ORDER BY amount DESC) AS rn
    FROM transfers
)
SELECT recipient
FROM Cte
WHERE rn <= 3
GROUP BY recipient
HAVING SUM(amount) >= 1024