mysql - 用 join 对所有行求和

Question

如何使用连接对查询中的所有行求和？

SELECT SUM(accounting.amount) AS sum, SUM(balance_invoice.amount) AS sum_balance
FROM accounting
LEFT JOIN balance_invoice ON balance_invoice.accounting_id=accounting.id

我需要此查询仅返回一行，其中包含 accounting.amount 和 balance_invoice.amount 的总和

可以将多行连接到每个 accounting.id

更新

SELECT SUM(accounting.currency_amount*-1 + (
    SELECT SUM(balance_invoice_accounting.currency_amountoff)
    FROM balance_invoice_accounting
    WHERE balance_invoice_accounting.accounting_id=accounting.id
)) AS sum
FROM accounting

Answer 1

只需在 SUM() 函数中添加这两个值:

SELECT SUM(accounting.amount + balance_invoice.amount) AS sum_all
FROM accounting
LEFT JOIN balance_invoice ON balance_invoice.accounting_id=accounting.id

如果其中一列可以为 NULL，您应该添加一个额外的 COALESCE() :

SELECT
SUM(COALESCE(accounting.amount,0) + COALESCE(balance_invoice.amount,0) AS sum_all
FROM accounting
LEFT JOIN balance_invoice ON balance_invoice.accounting_id=accounting.id

编辑:
对不起，我错过了那个重要的部分。如果你只想计算每个 accounting.amount 一次，而可以有多个 balance_invoice.amount 加入它，我会使用这样的子选择:

SELECT
  a.id,
  (
    a.amount
    + 
    (SELECT SUM(b.amount) FROM balance_invoice b WHERE b.accounting_id = a.id)
  ) AS sum_all
FROM
  accounting a