mysql - 内部连接三个表导致相乘的值

Question

我正在尝试(比方说)收集有关客户的报告。在该报告中，我想包括每个客户的订单总和和票号。

表格:

Customer(id, name)
Order(id, customer_id, amount)
support_ticket(id, customer_id)

查询:

select 
  c.id as 'Customer', 
  count(distinct t.id) as "Ticket count", 
  count(distinct o.id) as "Order count", 
  sum(o.amount) as 'Order Amount' 

from customer as c
  inner join `order` as o on c.id = o.customer_id
  inner join support_ticket as t on c.id = t.customer_id

group by c.id

因为我在两个表上加入了 customer.id，所以我得到了所有行“重复”，因为我得到了所有可能的组合，所以如果客户是多张票，sum(o.amount) 我们会因为“重复行”而倍增吗

sqlFiddle (mysql): http://sqlfiddle.com/#!9/ba39ba/13

sqlFiddle (pg): http://sqlfiddle.com/#!17/bc32e/7

这似乎是一个简单的案例，但我一直在看太多我想，我找不到做那个报告的正确方法。

我做错了什么？

Answer 1

最好的办法是将订单表中的聚合重写为派生表；

如

select 
  c.id as 'Customer', 
  count(distinct t.id) as "Ticket count", 
  o.amount as 'Order Amount' ,
  o.[Order count]
from customer as c
  inner join 
  (SELECT 
    o.customer_id, 
    sum(amount) as amount , 
    count(distinct o.id) as "Order count" 
    from [order] 
    group by o.customer_id)
   as o on c.id = o.customer_id
  inner join support_ticket as t on c.id = t.customer_id

group by 
  c.id , 
  o.amount ,
  o.[Order count]

请注意，派生表列随后被添加到底部的 group by 子句中。

干杯!