mysql - 需要使用另一个表中的客户信息对一个表中的交易总额进行求和

Question

我花了最后一个小时寻找可以在这里实现的东西，但还没有找到我真正需要的东西。

我有 2 个表:TRANSACTIONS 和 CUSTOMERS

CUSTOMER
internal_id | name | email

TRANSACTIONS
internal_id | customer_id | transaction_date | total_amount

我想循环浏览所有客户，然后按月和年总结每个客户的总交易量。我认为这就像将 select 语句作为列添加到初始查询一样简单，但这显然不起作用:

不工作:

select customer.internal_id, 
(sum(total_amount) as 'total' from TRANSACTIONS where transactions.customer_id = customer.internal_id and transaction_date  >= DATE_SUB(NOW(),INTERVAL 1 month)), 
 (sum(total_amount) as 'total' from TRANSACTIONS where transactions.customer_id = customer.internal_id and transaction_date  >= DATE_SUB(NOW(),INTERVAL 1 year))
from CUSTOMER join TRANSACTIONS on CUSTOMER.internal_id = TRANSACTIONS.customer_id

基本上我希望输出如下所示:

CUSTOMER.name | TRANSACTIONS.total_amount_month | TRANSACTIONS.total_amount_year
ABC Company   | $335.00                         | $8900.34

这可以通过单个查询实现吗？我使用 PHP 通过多个查询来实现它，并且如果可能的话，出于性能考虑，我更喜欢单个查询。

谢谢!

Answer 1

SELECT c.name,
       SUM(IF(transaction_date >= DATE__SUB(NOW(), INTERVAL 1 MONTH), total_amount, 0) AS total_amount_month,
       SUM(total_amount) AS total_amount_year
FROM transactions AS t
JOIN customer AS c ON c.internal_id = t.customer_id
WHERE transaction_date >= DATE__SUB(NOW(), INTERVAL 1 YEAR
GROUP BY t.customer_id