mysql - 在 SQL 中连接一个表中的两个查询

Question

我有 2 个疑问

SELECT date_format(payments.paymentdate, '%m'), 
       SUM(payments.amount) 
FROM payments 
WHERE payments.`customerNumber` IN 
(
    SELECT customers.`customerNumber` 
    FROM customers 
    WHERE customers.`country` = 'USA' 
    and customers.`customerNumber` = payments.`customerNumber`
)
GROUP BY date_format(payments.paymentdate, '%m')

并且

SELECT date_format( payments.paymentdate, '%m') , 
       SUM( payments.amount )
FROM payments
GROUP BY date_format( payments.paymentdate, '%m')

他们返回结果，所以一切正常。但是，我需要获得包含 3 列的结果 - date_format( payments. paymentdate, '%m') 、 SUM( payments.amount ) (其中国家/地区为美国) 和 SUM( payments.amount) (包含所有国家/地区)。

我尝试了以下查询

SELECT date_format( payments.paymentdate, '%m') , 
       SUM(payments.amount ), 
       (
          SELECT SUM(payments.amount) 
          FROM payments 
          WHERE payments.`customerNumber` IN 
          (
             SELECT customers.`customerNumber` 
             FROM customers WHERE customers.`country` = 'USA' 
             and customers.`customerNumber` = payments.`customerNumber`
          )
       )
FROM payments
GROUP BY date_format( payments.paymentdate, '%m')

但我有来自美国的付款总额，而不是按月计算的总额。

我该如何修复它？

谢谢!

Answer 1

您需要所有付款的总和以及部分付款的总和。

这对于“if”语句来说是完美的。我仍然认为您迟早会想要“%Y-%m”，而不是混淆 2014 年 4 月和 2015 年 4 月。

SELECT date_format(payments.paymentdate, '%m') as month, SUM(payments.amount) as amount_total, sum(if(customers.country='USA', payments.amount, 0)) as amount_USA
FROM payments 
inner join customers on customers.customerNumber = payments.customerNumber
GROUP BY date_format(payments.paymentdate, '%m')