sum()的问题

Question

我有一个sql查询

SELECT accounting.id, enclosure.time, enclosure.enc_id_, enclosure.txt, accounting.type_id, accounting.amount, accounting.invoice_id, invoice.invoice_id_, accounting.subaccountoff_id
FROM $this->db.accounting accounting
INNER JOIN $this->db.enclosure enclosure ON enclosure.id=accounting.enc_id
LEFT JOIN $this->db.balance_accounting balance_accounting ON balance_accounting.accounting_id=accounting.id
LEFT JOIN $this->db.invoice invoice ON invoice.id=accounting.invoice_id
LEFT JOIN $this->db.book_enclosure book_enclosure ON book_enclosure.enc_id=enclosure.id
WHERE accounting.group_id='".$_SESSION['gid']."' && (accounting.subaccount_id='$id' || accounting.subaccountoff_id='$id') && accounting.type_id <= 4 && book_enclosure.enc_id IS NULL
ORDER BY enclosure.time DESC, enclosure.enc_id_ DESC

像这样..。

SELECT accounting.id, enclosure.time, enclosure.enc_id_, enclosure.txt, accounting.type_id, accounting.amount, accounting.invoice_id, invoice.invoice_id_, accounting.subaccountoff_id, SUM(balance_accounting.amount) AS amount_off
FROM $this->db.accounting accounting
INNER JOIN $this->db.enclosure enclosure ON enclosure.id=accounting.enc_id
LEFT JOIN $this->db.balance_accounting balance_accounting ON balance_accounting.accounting_id=accounting.id
LEFT JOIN $this->db.invoice invoice ON invoice.id=accounting.invoice_id
LEFT JOIN $this->db.book_enclosure book_enclosure ON book_enclosure.enc_id=enclosure.id
WHERE accounting.group_id='".$_SESSION['gid']."' && (accounting.subaccount_id='$id' || accounting.subaccountoff_id='$id') && accounting.type_id <= 4 && book_enclosure.enc_id IS NULL
ORDER BY enclosure.time DESC, enclosure.enc_id_ DESC

我还尝试添加group by，但仍然只返回一行

GROUP BY balance_accounting.accounting_id

编辑：

现在我得到一个错误:列'accounting_id‘不能为空

SELECT accounting.id, enclosure.time, enclosure.enc_id_, enclosure.txt, accounting.type_id, accounting.amount, accounting.invoice_id, invoice.invoice_id_, accounting.subaccountoff_id, balance_accounting.amount_off
FROM $this->db.accounting accounting
INNER JOIN $this->db.enclosure enclosure ON enclosure.id=accounting.enc_id
LEFT JOIN (
    SELECT accounting_id, SUM(amount) AS amount_off
    FROM $this->db.balance_accounting
) balance_accounting ON balance_accounting.accounting_id=accounting.id
LEFT JOIN $this->db.invoice invoice ON invoice.id=accounting.invoice_id
LEFT JOIN $this->db.book_enclosure book_enclosure ON book_enclosure.enc_id=enclosure.id
WHERE accounting.group_id='".$_SESSION['gid']."' && (accounting.subaccount_id='$id' || accounting.subaccountoff_id='$id') && accounting.type_id <= 4 && book_enclosure.enc_id IS NULL
ORDER BY enclosure.time DESC, enclosure.enc_id_ DESC

Answer 1

 ...
 LEFT JOIN (
   SELECT accounting_id, SUM(amount) AS  amount_off
   FROM $this->db.balance_accounting balance_accounting 
 ) ON balance_accounting.accounting_id=accounting.id
 ...

Answer 2

SELECT accounting.id, enclosure.time, enclosure.enc_id_, enclosure.txt, accounting.type_id, accounting.amount, accounting.invoice_id, invoice.invoice_id_, accounting.subaccountoff_id, IFNULL(SUM(balance_accounting.amount), 0) AS amount_off
            FROM $this->db.accounting accounting
            INNER JOIN $this->db.enclosure enclosure ON enclosure.id=accounting.enc_id
            LEFT JOIN $this->db.balance_accounting balance_accounting ON balance_accounting.accounting_id=accounting.id
            LEFT JOIN $this->db.invoice invoice ON invoice.id=accounting.invoice_id
            LEFT JOIN $this->db.book_enclosure book_enclosure ON book_enclosure.enc_id=enclosure.id
            WHERE accounting.group_id='".$_SESSION['gid']."' && (accounting.subaccount_id='$id' || accounting.subaccountoff_id='$id') && accounting.type_id <= 4 && book_enclosure.enc_id IS NULL
            GROUP BY accounting.id
            ORDER BY enclosure.time DESC, enclosure.enc_id_ DESC