如何在mysql中为所有客户选择客户的最终余额 [英] How to select customer's final balance for all customer in mysql

问题描述

我有 3 张桌子.

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

I need to get all customers, their total balance, and their last order date and order id. This is my query.

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

除了客户的总价值外，一切都变得正确.从结果来看，它似乎被多次添加.

Everything is coming correct except customer's total value. From result it seems its getting added multiple times.

查询有什么问题?有人可以帮我吗?

What is wrong in query? Can anyone help me on this?

推荐答案

这是在 table_customers 到 table_orders 上做多对多连接，这会搞乱你的总和.而是这样做:

This is doing a many-to-many join on table_customers to table_orders, which will mess with your sums. Rather do this:

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 因为我觉得它更简洁，但这是一个偏好.

This will return one row per customer, then subquery the fields you want. I've substituted IFNULL for COALESCE as I find it cleaner, but this is a preference thing.

这篇关于如何在mysql中为所有客户选择客户的最终余额的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！