我的查询返回了来自2个子表的2列的不正确总和,我在Google上进行了搜索,还查看了关于stackoverflow的建议,但它们从未起作用。
si_invoices
-----------------------------
id, date
1, 2014-05-07
si_invoice_items
-----------------------------
id, invoice_id, date , total
1, 100, 2014-05-07, 200
2, 100, 2014-05-07, 200
si_payment
-----------------------------
id, ac_inv_id, date , payment
1, 100, 2014-05-07, 100
2, 100, 2014-05-07, 200
SELECT SI.*,SUM(SII.total) as total,SUM(SIP.payment) as payment FROM
(SELECT * FROM si_invoices GROUP BY si_invoices.id) AS SI
LEFT JOIN si_invoice_items SII ON SII.invoice_id = SI.id
LEFT JOIN si_payment SIP ON SIP.ac_inv_id = SII.invoice_id
GROUP BY SI.id
它应在sql中的字段“总计”中返回400 sum,但它返回800且与“付款”相同。您能指出我查询中的错误是什么吗?请帮助,不胜感激。
谢谢MS
直接使用总计,因为您的联接可能会根据需要创建更多的行。
请尝试以下操作:
SELECT id, MAX(Total) as FinalTotal ,MAX(Payment) as FinalPayment
FROM si_invoices a
left join
(select invoice_id, sum(total) as Total from si_invoice_items group by invoice_id) b
on a.id = b.invoice_id
left join
(select ac_inv_id, sum(payment) as Payment from si_payment group by ac_inv_id) c
on c.ac_inv_id = a.id
group by id
或者,如果id是唯一的:
SELECT *
FROM si_invoices a
left join
(select invoice_id, sum(total) as Total from si_invoice_items group by invoice_id) b
on a.id = b.invoice_id
left join
(select ac_inv_id, sum(payment) as Payment from si_payment group by ac_inv_id) c
on c.ac_inv_id = a.id
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句