关于查询各客户银行卡与现金消费金额的SQL语句问题咨询

首先看你当前SQL存在的两个核心问题：

• 现金支付记录丢失客户信息：当交易是现金支付时，t.card_no为空，通过LEFT JOIN cards cd后cd.cust_id也会是空，再关联customers表自然拿不到客户信息，这就是卡号125对应的John Doe记录异常的原因。
• 缺少分组聚合的必要条件：你用了sum(t.Tran_sold_amt)聚合函数，但没有添加GROUP BY子句，这会导致结果无法按客户和支付类型正确拆分统计。

修正后的查询语句 #

我调整了关联逻辑，先为每个交易组（tran_id）绑定对应的客户ID（因为同一tran_id属于同一客户），再进行统计：

SELECT 
    c.Cust_id, 
    c.Cust_name, 
    t.Card_no, 
    IFNULL(t.card_type, 'Cash_Payment') AS card_type, 
    SUM(t.Tran_sold_amt) AS total_amount
FROM transactions t
-- 子查询：为每个交易组匹配对应的客户ID
JOIN (
    SELECT 
        tran_id, 
        MAX(cd.cust_id) AS cust_id
    FROM transactions t_sub
    LEFT JOIN cards cd ON t_sub.card_no = cd.card_no
    GROUP BY tran_id
) tran_cust ON t.tran_id = tran_cust.tran_id
-- 通过子查询得到的cust_id关联客户表
JOIN customers c ON tran_cust.cust_id = c.cust_id
-- 按客户、卡号、支付类型分组，确保聚合结果正确
GROUP BY 
    c.Cust_id, 
    c.Cust_name, 
    t.Card_no, 
    IFNULL(t.card_type, 'Cash_Payment')
ORDER BY c.Cust_id, card_type;

逻辑说明 #

1. 子查询tran_cust：针对每个tran_id（同一客户的交易组），通过关联cards表提取对应的客户ID。用MAX()是因为同一交易组里只要有一条银行卡支付的记录，就能拿到该客户的ID，确保现金支付的记录也能通过tran_id关联到客户。
2. 主查询关联：通过tran_id把原交易表和子查询绑定，再关联customers表拿到客户名称，这样不管是银行卡还是现金支付的记录，都能正确关联到客户。
3. 分组与聚合：添加GROUP BY子句，按客户ID、客户名称、卡号、支付类型分组，让SUM()能正确计算每个分组的消费金额。

内容的提问来源于stack exchange，提问作者Bharat