mysql - 在 MySQL 中将 SUM 与联接一起使用会返回意外结果

Question

我有这些表:customers、customer_invoices、customer_invoice_details，每个客户都有很多发票，每个发票都有很多细节。

ID 为 574413 的客户有这些发票:

select customer_invoices.customer_id,
       customer_invoices.id,
       customer_invoices.total_price
from customer_invoices
where customer_invoices.customer_id = 574413;

结果:

customer_id     invoice_id  total_price
574413          662146      700.00
574413          662147      250.00

此处的每张发票都有两个详细信息(或发票行):

第一张发票662146:

select customer_invoice_details.id as detail_id,
       customer_invoice_details.customer_invoice_id as invoice_id,
       customer_invoice_details.total_price as detail_total_price
from customer_invoice_details
where customer_invoice_details.customer_invoice_id = 662146;

结果:

detail_id   invoice_id  detail_total_price
722291      662146      500.00
722292      662146      200.00

第二张发票662147:

select customer_invoice_details.id as detail_id,
       customer_invoice_details.customer_invoice_id as invoice_id,
       customer_invoice_details.total_price as detail_total_price
from customer_invoice_details
where customer_invoice_details.customer_invoice_id = 662147;

结果:

detail_id   invoice_id  detail_total_price
722293      662147      100.00
722294      662147      150.00

我对这个查询有疑问:

select customers.id as customerID,
       customers.last_name,
       customers.first_name,
       SUM(customer_invoices.total_price) as invoice_total,
       SUM(customer_invoice_details.total_price) as details_total
from customers
       join customer_invoices
         on customer_invoices.customer_id = customers.id
       join customer_invoice_details
         on customer_invoice_details.customer_invoice_id = customer_invoices.id
where customer_id = 574413;

意外的结果:

customerID  last_name   first_name  invoice_total   details_total
574413      terry       amine       1900.00         950.00

我需要发票的total_price的SUM，以及total_price的SUM每个客户的详细信息。在这种情况下，我应该为两列(invoice_total 和 details_total)获取 950 作为 total_price 但它是不是这样的。请问我做错了什么以及我怎样才能得到正确的结果。类似主题的答案没有针对这种情况的解决方案。

Answer 1

当您将普通列与聚合函数(例如 SUM)混合使用时，您需要使用 GROUP BY，在其中列出 SELECT 中的普通列。

发票的 total_price 中金额过多的原因是 SUM 也是在每个明细行上计算的，因为它是连接的一部分。使用这个:

select c.id as customerID,
       c.last_name,
       c.first_name,
       SUM(ci.total_price) as invoice_total,
       SUM((select SUM(d.total_price)
        from customer_invoice_details d
        where d.customer_invoice_id = ci.id)) as 'detail_total_price'
from customers c
       join customer_invoices ci on ci.customer_id = c.id
where c.id = 574413
group by c.id, c.last_name, c.first_name

db-fiddle