使用多个联接时，MySQL SUM查询返回错误结果

m3eecexj 于 2022-12-10 发布在 Mysql

关注(0)|答案(3)|浏览(111)

我的查询从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中，它应该为字段‘TOTAL’返回400 SUM，但它返回800，与‘Payment’相同。你能指出我的问题中有什么错误吗？请帮帮忙，谢谢。
谢谢M.S.

mysql

来源：https://stackoverflow.com/questions/43625799/mysql-sum-query-returning-incorrect-result-when-using-multiple-joins

3条答案

按热度按时间

xmd2e60i1#

直接使用总计，因为您的联接可能会以您想要的组合方式创建更多行。
尝试以下操作：

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

赞(0）回复(0）举报 2022-12-10

zzwlnbp82#

详细结果：

select a.invoice,ac_inv_id,sum(a.total),sum(b.payment) from
(select 1 as id, 100 as invoice, '2014-05-07' as date, 200 as total union all
select  2, 100, '2014-05-07', 200) as a
left join
(select 1 as id, 100 as ac_inv_id, '2014-05-07' as date, 100 as payment union all
select 2, 100, 2014-05-07, 200) as b
on a.id = b.id

最终查询：

select a.invoice,ac_inv_id,sum(a.total),sum(b.payment) from
(select * from si_invoice_items) as a
left join
(select * from si_payment) as b
on a.id = b.id

结果：

100 100 400 300

赞(0）回复(0）举报 2022-12-10

iyr7buue3#

选择projects.*，sum(project_tasks.ts_mount)作为Cost_Expend，sum(work_entries.w_mount)作为Actual_Amount from projects左侧加入project_costs上的project_costs。proj_id=projects。proj_id左加入project_costs上的project_tasks。cost_id=project_tasks。cost_id左加入project_costs上的work_entries。cost_id=work_entries。cost_id和work_entries。w_status=projects的‘已批准’组。proj_id；

赞(0）回复(0）举报 2022-12-10

我来回答

使用多个联接时，MySQL SUM查询返回错误结果

3条答案

相关问题

热门标签

最新问答