在MySQL中使用和连接返回意外结果

Question

我有这样的表：customers，每个客户都有许多发票，而且每个发票都有很多细节。

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

我需要发票的SUM的total_price，以及每个客户的详细信息的total_price的SUM。在本例中，我应该将950作为total_price为两列(invoice_total& details_total)，但事实并非如此。我做错了什么&我怎样才能得到正确的结果？类似主题中的答案对于这种情况没有解决办法。

Answer 1

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

total_price中发票数量过多的原因是，每个细节行的总和也是计算出来的，因为它是连接的一部分。用这个：

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

大小提琴

Answer 2

你正在沿着多个维度聚集。这很有挑战性。我建议在每个维度上独立地进行聚合：

select c.id as customerID, c.last_name, c.first_name,
       ci.invoice_total,
       cid.details_total
from customers c join
     (select ci.sum(ci.total_price) as invoice_total
      from customer_invoices ci
      group by ci.customer_id
     ) ci
     on ci.customer_id = c.id join
     (select ci.sum(cid.total_price) as details_total
      from customer_invoices ci join
           customer_invoice_details cid
           on cid.customer_invoice_id = ci.id
      group by ci.customer_id
     ) cid
     on cid.customer_id = c.id
where c.id = 574413;

更快的版本(针对一个客户)使用相关的子查询：

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

Answer 3

我对sub查询使用了联接，然后对这些和进行了一次求和。

SELECT c.id as customerID,
   c.last_name,
   c.first_name
   SUM(i.sum) as invoice_total,
   SUM(d.sum) AS details_total
FROM customers c
JOIN (SELECT id, customer_id, SUM(total_price) AS sum
      FROM customer_invoices
      GROUP BY id, customer_id) AS i ON i.customer_id = c.id
JOIN (SELECT customer_invoice_id as id, SUM(total_price) AS sum
      FROM customer_invoice_details 
      GROUP BY customer_invoice_id) AS d ON d.id = i.id
WHERE c.id = 574413
GROUP BY c.id, c.name