使用多个联接时，mysql sum查询返回不正确的结果

小编典典

使用多个联接时，mysql sum查询返回不正确的结果

sql

我的查询返回了来自2个子表的2列的不正确总和，我在Google上进行了搜索，还查看了建议，但它们从未起作用。

   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

阅读 138

2021-04-14

共1个答案

小编典典

直接使用总计，因为您的联接可能会根据需要创建更多的行。

请尝试以下方法：

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

2021-04-14