使用多个连接时，Mysql sum 查询返回错误结果

本文介绍了使用多个连接时，Mysql sum 查询返回错误结果的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我的查询返回了 2 个子表中不正确的 2 列总和，我通过谷歌搜索并查看了有关 stackoverflow 的建议，但它们从未奏效.

My query is returning incorrect sum of 2 columns from 2 sub tables, i searched over google and also looked the suggestion on stackoverflow but they never worked.

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 中的字段 'total' 返回 400 sum 但它返回 800 并且与 'payment' 相同.您能否指出我的查询中的错误是什么.请帮忙，不胜感激.

It should return 400 sum for field 'total' in sql but it return 800 and same as for 'payment'. Can you please point out what is the error in my query. Please help, appreciated.

谢谢硕士

推荐答案

直接使用总计，因为您的联接可能会创建更多您想要的组合行.

Use the totals directly because your joins are probably creating more rows in combination that you want.

尝试以下操作:

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 是唯一的:

or if id is unique:

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