mysql - 如何使用 GROUP BY 和 ORDER BY 对多个表的金额求和？

Question

我试图将这些查询减少为一个，这样就不是三个单独的查询了。除了我所拥有的之外，还有更好的方法吗？我还试图将所有总计汇总在一起 IE (invoiced_total + refund_total + credit_memo_total)。这些表除了客户之外没有任何关系，但我不希望按客户分组，只是按日期分组。任何帮助将不胜感激。

SELECT 
    SUM(qbi.amount) AS invoiced_total,
    DATE_FORMAT(qbi.line_item_date, '%Y-%m-01') AS date
        FROM
            invoices as qbi
        WHERE
            qbi.`line_item_date` BETWEEN '2018-12-01' AND '2019-12-31'
        GROUP BY
            DATE_FORMAT(qbi.`line_item_date`, '%Y-%m-01')
         ORDER BY
            DATE_FORMAT(qbi.`line_item_date`, '%Y-%m-01');
SELECT 
    SUM(amount) AS refund_total,
    DATE_FORMAT(refund_date, '%Y-%m-01') AS refund_date
        FROM
            refunds
        WHERE
            refund_date BETWEEN '2018-12-01' AND '2019-12-31'
        GROUP BY
            DATE_FORMAT(refund_date, '%Y-%m-01')
         ORDER BY
            DATE_FORMAT(refund_date, '%Y-%m-01');
SELECT 
    SUM(amount) AS credit_memo_total,
    DATE_FORMAT(credit_memo_date, '%Y-%m-01') AS credit_memo_date
        FROM
            credit_memos
        WHERE
            credit_memo_date BETWEEN '2018-12-01' AND '2019-12-31'
        GROUP BY
            DATE_FORMAT(credit_memo_date, '%Y-%m-01')
         ORDER BY
            DATE_FORMAT(credit_memo_date, '%Y-%m-01');

Answer 1

如果您想加入这些查询，您还必须使用第 4 个查询，它将返回 3 个表中所有不同的月份，以防万一其中任何一个缺少月份。
这就是为什么您应该对表使用左连接:

SELECT d.date, i.invoiced_total, r.refund_total, c.credit_memo_total,
  COALESCE(invoiced_total, 0) + COALESCE(refund_total, 0) + COALESCE(credit_memo_total, 0) AS total
FROM (
  SELECT DATE_FORMAT(qbi.`line_item_date`, '%Y-%m-01') AS date FROM invoices UNION
  SELECT DATE_FORMAT(refund_date, '%Y-%m-01') FROM refunds UNION
  SELECT DATE_FORMAT(credit_memo_date, '%Y-%m-01') FROM credit_memos
) AS d
LEFT JOIN ( 
  SELECT 
    SUM(qbi.amount) AS invoiced_total,
    DATE_FORMAT(qbi.line_item_date, '%Y-%m-01') AS date
        FROM
            invoices as qbi
        WHERE
            qbi.`line_item_date` BETWEEN '2018-12-01' AND '2019-12-31'
        GROUP BY
            DATE_FORMAT(qbi.`line_item_date`, '%Y-%m-01')
) i ON i.date = d.date
LEFT JOIN (
  SELECT 
    SUM(amount) AS refund_total,
    DATE_FORMAT(refund_date, '%Y-%m-01') AS refund_date
        FROM
            refunds
        WHERE
            refund_date BETWEEN '2018-12-01' AND '2019-12-31'
        GROUP BY
            DATE_FORMAT(refund_date, '%Y-%m-01')
) r ON r.date = d.date
LEFT JOIN (
  SELECT 
    SUM(amount) AS credit_memo_total,
    DATE_FORMAT(credit_memo_date, '%Y-%m-01') AS credit_memo_date
        FROM
            credit_memos
        WHERE
            credit_memo_date BETWEEN '2018-12-01' AND '2019-12-31'
        GROUP BY
            DATE_FORMAT(credit_memo_date, '%Y-%m-01')
) c ON c.date = d.date
WHERE d.date BETWEEN '2018-12-01' AND '2019-12-31'
ORDER BY d.date