如何使用分组依据和排序依据对多个表的金额求和?

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

Answer 2

您通常会联接这三个查询。假设所有月份都在三个表中可用，您可以执行以下操作：

SELECT
i.invoice_month my_month,
i.invoiced_total,
r.refund_total,
c.credit_total,
i.invoiced_total + r.refund_total + c.credit_total amount_total
FROM (
SELECT 
SUM(amount) AS invoiced_total, 
DATE_FORMAT(line_item_date, '%Y-%m-01') AS invoice_month
FROM invoices
WHERE line_item_date BETWEEN '2018-12-01' AND '2019-12-31'
GROUP BY invoice_month
) i
INNER JOIN (
SELECT 
SUM(amount) AS refund_total, 
DATE_FORMAT(refund_date, '%Y-%m-01') AS refund_month
FROM refunds
WHERE refund_date BETWEEN '2018-12-01' AND '2019-12-31'
GROUP BY refund_month
) r
INNER JOIN (
SELECT 
SUM(amount) AS credit_total, 
DATE_FORMAT(credit_memo_date, '%Y-%m-01') AS credit_month
FROM credit_memos
WHERE credit_memo_date BETWEEN '2018-12-01' AND '2019-12-31'
GROUP BY credit_memo_month
) c
ORDER BY i.invoice_month 

旁注：在MySQL中，您可以在GROUP BY子句(和ORDER BY子句(中使用列别名：这有助于缩短查询时间。