我们将发票保存在一个名为invoices_v3的表中，将付款保存在一个名为payments_v3的表中。每张发票完全有可能有多笔付款。
SELECT *,
SUM(invoices_v3.invoice_amount - payments_v3.pago_in_cash + payments_v3.pago_in_ccrd + payments_v3.pago_in_bnkd + payments_v3.pago_in_coup + payments_v3.pago_in_chek) AS end_balance
FROM invoices_v3, payments_v3
WHERE payments_v3.invoice_no=invoices_v3.invoice_id AND invoices_v3.client_id = '$clean_id'
ORDER BY invoices_v3.invoice_id DESC
我试图让它从invoices_v3返回所有结果，其中invoices_v3.client_id = '$clean_id'和在相同的结果中，end_balance有所有支付的总和，其中payments_v3.invoice_no=invoices_v3.invoice_id。它现在正在做的是只返回1个结果(仅最后一个发票)，end_balance具有与$clean_id(客户id)匹配的所有付款的总和。
如果可能的话，我尝试在没有join的情况下这样做，因为这个脚本将被经常访问，并且一些客户端id可能有数百个发票，每个发票都有多个付款。
编辑:这是我尝试的JOIN:
SELECT invoices_v3.*,
SUM(payments_v3.pago_in_cash + payments_v3.pago_in_ccrd + payments_v3.pago_in_bnkd + payments_v3.pago_in_coup + payments_v3.pago_in_chek) AS total_payments
FROM invoices_v3
LEFT JOIN payments_v3 ON invoices_v3.invoice_id=payments_v3.invoice_no
WHERE invoices_v3.client_id = '$clean_id'
AND payments_v3.old_invoice_no=invoices_v3.invoice_id <-- DELETE THIS LINE
GROUP BY invoices_v3.invoice_id
ORDER BY invoices_v3.invoice_id DESC
编辑2:
+----+-------------+-------------+------+----------------+----------------+---------+-------+--------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+------+----------------+----------------+---------+-------+--------+----------------------------------------------+
| 1 | SIMPLE | invoices_v3 | ref | inv_v1_cliente | inv_v1_cliente | 5 | const | 31 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | payments_v3 | ALL | NULL | NULL | NULL | NULL | 147706 | |
+----+-------------+-------------+------+----------------+----------------+---------+-------+--------+----------------------------------------------+
2 rows in set (0.00 sec)
你应该添加一个GROUP BY子句到你的SQL
在您的SUM周围添加COALESCE声明，以说明发票没有付款
SELECT *,
COALESCE(SUM(invoices_v3.invoice_amount - payments_v3.pago_in_cash + payments_v3.pago_in_ccrd + payments_v3.pago_in_bnkd + payments_v3.pago_in_coup + payments_v3.pago_in_chek),invoices_v3.invoice_amount) AS end_balance
FROM invoices_v3, payments_v3
WHERE payments_v3.invoice_no=invoices_v3.invoice_id AND invoices_v3.client_id = '$clean_id'
GROUP BY invoices_v3.invoice_id
ORDER BY invoices_v3.invoice_id DESC
如果发票没有付款，则显示发票金额。