时间戳

首页 > 解决方案 > SQL和多组聚合

mysql - SQL和多组聚合

问题描述

我需要编写一个 MySQL 查询，该查询返回在最多三笔交易中至少收到 1000 美元的收件人列表。

这是一个表格示例（第一列是收件人（文本列），第二列对应于他/她的交易金额（int））：

recipient  amount 
1          200
1          10000
2          100
2          10
3          500
4          400
4          400
4          200
3          200
3          100

我了解如何做到这一点：

select user
from t
group by user
having count(*) <= 3 and
       sum(amount) >= 1000;

但在下一个注释给出的任务中：“请注意，该收件人可以有超过三笔交易，只要三笔或更少的交易金额至少为 1000 美元”

我完全被卡住了，无法在网上找到答案。

标签： mysqlsql

解决方案

假设性能不是您主要关心的问题，这应该可以工作，并且还显示有超过 3 笔交易但他们的前 3 笔交易超过 1000 的收件人：

select distinct t1.recipient
from 
  (select recipient,amount from t order by amount desc) t1 
left join
  (select recipient,amount from t order by amount desc) t2
on t1.recipient = t2.recipient and t2.amount < t1.amount 

left join
  (select recipient,amount from t order by amount desc) t3
     on t1.recipient = t3.recipient and t3.amount < t2.amount

where 
  t1.amount + coalesce(t2.amount, 0) + coalesce(t3.amount, 0) >= 1000;

在对此事进行了更多思考之后，您实际上需要一个单独的唯一列来使这项工作 100% 的时间。您可以清楚地检查，此处未显示收件人“4”。我们可以通过使用另一个 id 列并将金额值与 <= 而不是 < 进行比较来弥补这一事实：

select distinct t1.recipient
from 
  (select id,recipient,amount from t order by amount desc) t1 
left join
  (select id,recipient,amount from t order by amount desc) t2
on t1.recipient = t2.recipient and t1.id <> t2.id and t2.amount <= t1.amount 

left join
  (select id,recipient,amount from t order by amount desc) t3
     on t1.recipient = t3.recipient and t1.id<>t3.id and t3.id<>t2.id and t3.amount <= t2.amount

where 
  t1.amount + coalesce(t2.amount, 0) + coalesce(t3.amount, 0) >= 1000;

推荐阅读