多表存在相同列名时MySQL关联查询数据累加异常问题咨询
你遇到的数据重复累加问题,核心原因是直接同时左关联order表和cancel表会产生笛卡尔积:假设某客户有N条符合日期条件的订单、M条符合日期条件的取消单,关联后会生成N*M行中间数据,最终聚合统计时计数、求和的结果都会被重复放大。
正确的写法是先分别对order和cancel表按客户维度、在指定日期范围内做预聚合,再和profile表关联,就能避免笛卡尔积问题,修改后的SQL如下:
SELECT c.Customer, FORMAT(COALESCE(o.qty, 0), 0) as Qty, FORMAT(COALESCE(o.Total_Amount, 0), 2) as Total, FORMAT(COALESCE(o.charges, 0), 2) as Charges, FORMAT(COALESCE(cc.qty_cancel, 0), 0) as Qty_cancel, FORMAT(COALESCE(cc.Total_cancel, 0), 2) as Total_cancel, FORMAT(COALESCE(cc.charges_cancel, 0), 2) as Charges_cancel FROM profile c LEFT JOIN ( SELECT Customer, COUNT(OrderID) as qty, SUM(Amount) as Total_Amount, SUM(Charges) as charges FROM `order` WHERE Date BETWEEN '2021-01-01' AND '2021-01-31' GROUP BY Customer ) o ON c.Customer = o.Customer LEFT JOIN ( SELECT Customer, COUNT(cancelID) as qty_cancel, -- 如果需要取消金额、手续费显示为负值,可在SUM结果前加负号:SUM(Amount) * -1 SUM(Amount) as Total_cancel, SUM(Charges) as charges_cancel FROM cancel WHERE Date BETWEEN '2021-01-01' AND '2021-01-31' GROUP BY Customer ) cc ON c.Customer = cc.Customer
上述写法将日期过滤提前到子查询中执行,还能减少不必要的数据处理量,提升SQL执行效率。
内容的提问来源于stack exchange,提问作者Ask Warvin
相关产品推荐
相关产品推荐

