如何修复按客户统计消费总额时重复显示总计的SQL问题

你的查询出现重复总金额的核心问题是没有关联customer表和payment表，导致这两个表之间产生了笛卡尔积——每个客户的信息会和所有支付记录匹配，最终SUM(amount)计算的是所有客户的总消费，而非单个客户的。

方案1：使用显式JOIN语法（推荐，可读性更强） #

显式指定各表之间的关联关系，彻底避免笛卡尔积：

SELECT 
    c.first_name, 
    c.last_name, 
    c.customer_id, 
    a.address, 
    ci.city, 
    a.postal_code, 
    SUM(p.amount) AS money_spent
FROM customer c
JOIN address a ON c.address_id = a.address_id
JOIN city ci ON a.city_id = ci.city_id
JOIN payment p ON c.customer_id = p.customer_id  -- 关键：关联客户与对应支付记录
GROUP BY 
    c.customer_id, 
    c.first_name, 
    c.last_name, 
    a.address, 
    ci.city, 
    a.postal_code
ORDER BY c.last_name ASC;

方案2：修正原有隐式JOIN的WHERE条件 #

在原有WHERE子句中补充客户与支付表的关联条件，同时完善GROUP BY以符合SQL标准：

SELECT 
    customer.first_name, 
    customer.last_name, 
    customer.customer_id, 
    address.address, 
    city.city, 
    address.postal_code, 
    SUM(amount) AS money_spent
FROM customer, address, city, payment
WHERE 
    customer.address_id = address.address_id
    AND address.city_id = city.city_id
    AND customer.customer_id = payment.customer_id  -- 新增：关联客户和支付记录
GROUP BY 
    customer.customer_id,
    customer.first_name,
    customer.last_name,
    address.address,
    city.city,
    address.postal_code
ORDER BY customer.last_name ASC;

补充说明 #

• 完善GROUP BY的原因：多数数据库（比如开启ONLY_FULL_GROUP_BY模式的MySQL）要求GROUP BY子句包含所有SELECT中未使用聚合函数的列，否则会报错。即使你的数据库允许仅按主键（customer_id）分组，显式列出所有非聚合列也更规范、可读性更强。
• 如果需要保留无支付记录的客户（显示money_spent为0），可以把JOIN payment改为LEFT JOIN payment，并将SUM(p.amount)替换为COALESCE(SUM(p.amount), 0)。

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