mysql - 在连接表之前使用聚合函数

Question

我有两个表并在 customer_id 上加入它们。

第一个表是交易，我将交易的数据存储在那里。每笔交易都有数量和休息、支付等。

第二个表是 handle，我很难解释这个表的用途，但 handle 表是同一个交易表，它有 volume_handle、rest_handle、pay_handle 等。

我必须使用左连接，因为我想要交易表中的所有记录和句柄表中的匹配记录

我想对 deal 的 volume 和 sum rest 求和，对 handle 的 volume_handle 求和，这些表之间的关系是 customer_id 和 buy_id。

例如交易表:

id = 1
volume = 1000
rest = 1000
customer_id = 1
---------------
id = 2
volume = 500
rest = 0
customer_id = 1
---------------
id = 3 
volume = 2000
rest = 0
customer_id = 2

句柄表是:

id = 1
volume_handle = 3000
buy_id = 1

我写的查询是:

select sum(deal.rest) as rest , sum(deal.volume) as volume , sum(handle.volume_handle) as handle  
from deal 
left join handle on deal.customer_id = handle.buy_id  
group by deal.customer_id;

这个查询的结果是:

//when customer_id is 1
volume = 1500
rest = 1000
handle = 6000

//when customer_id is 2
volume = 2000
rest = 0
handle = null

音量和其余部分是正确的，但第二个表的句柄是错误的，因为 sum(handle.volume_handle) 的结果是 3000 而不是 6000(当 customer_id 为 1 时)

而且我不知道如何在加入表之前使用聚合函数。

这里有人可以写出这个问题的查询吗？

Answer 1

因为对于每个 deal.customer_id 值，handle 中可以有多个行，所以您需要在之前在该表中执行聚合 JOIN 它以 deal。像这样:

SELECT d.customer_id,
       SUM(d.rest) AS rest,
       SUM(d.volume) AS volume,
       MAX(h.volume_handle) AS handle
FROM deal d
LEFT JOIN (SELECT buy_id, SUM(volume_handle) AS volume_handle
           FROM handle
           GROUP BY buy_id) h ON h.buy_id = d.customer_id
GROUP BY d.customer_id

输出:

customer_id rest    volume  handle
1           1000    1500    3000
2           0       2000    null

Demo on dbfiddle

请注意，我在 h.volume_handle 周围使用了 MAX，这不会改变结果(因为它将测试的所有值都相同)但会需要避免任何 only_full_group_by 错误。