Subquery返回值重复 SQL查询客户信息重复但聚合计算值不同问题排查

问题根因 #

• 表关联未加条件：customer表和订单子查询c没有关联条件，直接生成笛卡尔积，导致客户信息和订单记录随机匹配，出现大量重复的客户数据。
• GROUP BY用法错误：当前按TotalPrice分组，而SELECT子句中FirstName、LastName、CellPhone、Email均为非聚合字段，未出现在GROUP BY中，仅MySQL非严格模式下可执行，返回的非聚合字段值为随机匹配结果，完全不可靠；同时按订单价格分组的逻辑也不符合按客户统计的业务需求。
• 差值计算逻辑错误：TotalPrice - 'Average Total Price'中'Average Total Price'被识别为字符串常量，并非你定义的平均价格别名的计算结果，因此差值计算完全错误。
• 不必要的子查询：内层查询仅从orders表取CID和TotalPrice，完全可以直接关联orders表省略这层嵌套。

修正方案 #

方案1：返回每笔订单和对应客户的统计值（支持查看单笔订单和该客户平均价的差值，需数据库支持窗口函数） #

SELECT 
    c.FirstName,
    c.LastName,
    c.CellPhone,
    c.Email,
    o.TotalPrice,
    AVG(o.TotalPrice) OVER (PARTITION BY c.ID) AS `Average Total Price`,
    o.TotalPrice - AVG(o.TotalPrice) OVER (PARTITION BY c.ID) AS `Difference Total Price`
FROM
    customer c
INNER JOIN orders o ON c.ID = o.CID
ORDER BY c.FirstName;

方案2：返回每个客户的汇总统计值（每个客户仅返回一条记录） #

SELECT 
    c.FirstName,
    c.LastName,
    c.CellPhone,
    c.Email,
    SUM(o.TotalPrice) AS `Total Price`,
    AVG(o.TotalPrice) AS `Average Total Price`,
    SUM(o.TotalPrice) - AVG(o.TotalPrice) AS `Difference Total Price`
FROM
    customer c
INNER JOIN orders o ON c.ID = o.CID
GROUP BY c.ID, c.FirstName, c.LastName, c.CellPhone, c.Email
ORDER BY c.FirstName;

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