Mysql left join 没有按预期工作

Question

3张 table 。

table_customers - customer_id, name
table_orders - order_id, customer_id, order_datetime
table_wallet - customer_id, amount, type  // type 1- credit, type 2- debit

需要获取所有客户、他们的总余额以及他们的最后订单日期和订单 ID。如果客户没有下任何返回订单，日期为 0000-00-00，订单 ID 为 0。

这是我的查询。

SELECT 
C.customer_id, 
C.name, 
COALESCE( SUM(CASE WHEN type = 2 THEN -W.amount ELSE W.amount END), 0) AS value,
COALESCE( max( O.order_id  ) , '0' ) AS last_order_id, 
COALESCE( max( date( O.order_datetime ) ) , '0000-00-00' ) AS last_order_date
FROM 
table_customers as C 
LEFT JOIN 
table_wallet as W 
ON C.customer_id = W.customer_id 
LEFT JOIN
table_orders AS O
ON W.customer_id = O.customer_id
group by C.customer_id
ORDER BY C.customer_id

除了客户的值(value)，一切都在走向正确。从结果来看，它似乎被添加了多次。

我在这里创建了 fiddle 。 http://sqlfiddle.com/#!9/560f2/1

查询有什么问题？谁能帮我解决这个问题？

编辑:预期结果

customer_id name    value   last_order_id     last_order_date
  1         abc     20        3             2016-06-22
  2         def     112.55    0             0000-00-00
  3         pqrs      0       4             2016-06-15
  4         wxyz      0       0             0000-00-00

Answer 1

问题是订单和钱包之间的连接将产生与每个钱包的订单一样多的行，而实际上您只需要订单表中每个钱包的一行(因为您只使用最大值)。在您的测试用例中，客户 1 有 3 行，总和为 60 (3*20)。

解决此问题的一种方法是更改​​为:

SELECT 
  C.customer_id, 
  C.name, 
  COALESCE( SUM(CASE WHEN type = 2 THEN -W.amount ELSE W.amount END), 0) AS value,
  COALESCE( O.order_id , '0' ) AS last_order_id, 
  COALESCE( DATE( O.order_datetime ) , '0000-00-00' ) AS last_order_date
FROM table_customers AS C 
LEFT JOIN table_wallet AS W ON C.customer_id = W.customer_id 
LEFT JOIN (
  SELECT 
    customer_id, 
    MAX(order_id) AS order_id, 
    MAX(order_datetime) AS order_datetime
  FROM table_orders 
  GROUP BY customer_id
) AS O ON c.customer_id = O.customer_id
GROUP BY C.customer_id
ORDER BY C.customer_id

如您所见，订单表已替换为派生表，每个客户一行。

运行 query above得到以下结果:

| customer_id | name |  value | last_order_id | last_order_date |
|-------------|------|--------|---------------|-----------------|
|           1 |  abc |     20 |             3 |      2016-06-22 |
|           2 |  def | 112.55 |             0 |      0000-00-00 |
|           3 | pqrs |      0 |             4 |      2016-06-15 |
|           4 | wxyz |      0 |             0 |      0000-00-00 |