mysql - 如何在mysql中为所有客户选择客户的最终余额

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。这是我的查询。

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)外，一切都很顺利。从结果来看，它似乎被添加了多次。

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

Answer 1

这是在 table_customers 和 table_orders 上进行多对多连接，这会扰乱您的总和。而是这样做:

SELECT C.customer_id
, C.name
, IFNULL((SELECT SUM(IF(W.type=2, -1*W.amount, W.amount))
    FROM table_wallet W 
    WHERE C.customer_id = W.customer_id),0) AS value
, IFNULL((SELECT MAX(DATE(O.order_id))
    FROM table_orders O 
    WHERE C.customer_id = O.customer_id),'0') AS last_order_id
, IFNULL((SELECT MAX(DATE(O.order_datetime))
    FROM table_orders O 
    WHERE C.customer_id = O.customer_id),'0000-00-00') AS last_order_date
FROM table_customers as C 
ORDER BY C.customer_id

这将为每个客户返回一行，然后子查询您想要的字段。我已将 IFNULL 替换为 COALESCE，因为我发现它更干净，但这是一个偏好问题。