如何根据一列的类别信息从另一列中提取热门记录

Question

这是一个我无法弄清楚的问题。如何找到在前三次转账中总共收到 1024 美元的收件人。

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

insert into receivers values ("TFC", "Apple", 2014);
insert into receivers values ("TFC", "Google", 100);
insert into receivers values ("TFC", "Google", 345);
insert into receivers values ("Facebook", "Facebook", 834);
insert into receivers values ("Facebook", "Google", 56);
insert into receivers values ("VMware", "Google", 12);
insert into receivers values ("VMware", "Google", 300);
insert into receivers values ("Google", "Facebook", 20);
insert into receivers values ("Google", "Facebook", 100);
insert into receivers values ("Google", "Facebook", 1000);

附言我能够找到解决方案。困难在于如何根据名称获得最高金额记录。而不是构造完成的查询，实际上有很好的函数。

• 在postgresql中，功能OVER（PARTITION BY）专门针对这个问题。
• 在mysql中，它有点难，但是您可以使用诸如group_concat+substr，substr合并记录需要排序，然后group_concat到内部排序。

Answer 1

首先，根据amount获取每个recipient的前 3 个transfers，然后检查 SUM>= 1024：

SELECT
    t1.recipient
FROM transfers AS t1
WHERE (
    SELECT COUNT(*)
    FROM transfers t2
    WHERE
        t2.recipient = t1.recipient
        AND t2.amount >= t1.amount
) <= 3
GROUP BY t1.recipient
HAVING SUM(t1.amount) >= 1024
ORDER BY t1.recipient;