为何SQL中SUM(amount)*SUM(unit_price)与SUM(amount*unit_price)不相等？

现有以下数据表结构：

• orders表：amount, unit_price, child_amount, child_price, service_id, issue_date, is_success
• pools表：name, for_view
• services表：id, pool_id

执行如下SQL语句：

SELECT SUM(orders.amount) AS totalAmount, 
SUM(orders.unit_price) AS totalPrice,  
SUM(orders.amount*orders.unit_price) AS total,  
SUM(orders.child_amount) AS totalAmountC,  
SUM(orders.child_price) AS totalPriceC,  
SUM(orders.child_amount*orders.child_price) AS totalC,  
pools.name, 
pools.for_view 
FROM orders INNER 
JOIN services ON services.id = orders.service_id  
INNER JOIN pools ON pools.id = services.pool_id 
WHERE issue_date = '2024-03-15' 
AND pools.id = '35' 
AND is_success = 1  
GROUP BY pools.name, pools.for_view; 

执行后发现计算结果中totalAmount * totalPrice不等于total，预期total应为5000000，但实际结果不符，需明确差异原因及修正方法。

• 计算逻辑本质差异：SUM(amount) * SUM(unit_price) 与 SUM(amount * unit_price) 是两种完全不同的计算逻辑：
  • SUM(amount) * SUM(unit_price) 是先对所有行的amount求和、unit_price求和，再将两个总和相乘；
  • SUM(amount * unit_price) 是先逐行计算amount与unit_price的乘积，再将所有行的乘积结果求和。
    只有当所有订单的unit_price完全相同，或所有订单的amount完全相同时，两者结果才会相等。你的场景中订单的amount和unit_price组合多样，因此必然出现差异。
• JOIN导致数据重复（潜在原因）：如果services或pools表的关联关系导致同一条orders记录被多次匹配（比如一个service对应多个pool，虽WHERE限定了pools.id=35仍需排查），会让SUM计算的基数变大，最终结果偏离预期。

1. 确认业务需求并调整计算逻辑：

   • 如果业务需求是计算所有订单的总金额（逐行单价乘数量后累加），当前SQL中的SUM(orders.amount*orders.unit_price) AS total是正确的，无需修改。此时totalAmount * totalPrice的结果无业务意义，不应作为预期值参考。
   • 如果业务逻辑确实需要SUM(amount) * SUM(unit_price)的结果，直接将total的计算式改为SUM(orders.amount) * SUM(orders.unit_price)即可，但需确认该逻辑符合实际业务场景。

2. 排查并处理数据重复问题：
   先执行以下SQL检查JOIN后是否存在重复订单记录：

   SELECT orders.*, COUNT(*) AS duplicate_count
   FROM orders 
   INNER JOIN services ON services.id = orders.service_id  
   INNER JOIN pools ON pools.id = services.pool_id 
   WHERE issue_date = '2024-03-15' 
     AND pools.id = '35' 
     AND is_success = 1
   GROUP BY orders.amount, orders.unit_price, orders.child_amount, orders.child_price, orders.service_id, orders.issue_date, orders.is_success
   HAVING COUNT(*) > 1;
   

   若存在重复记录，可通过DISTINCT去重后再计算（需确认去重逻辑符合业务要求）：

   SELECT SUM(orders.amount) AS totalAmount, 
   SUM(orders.unit_price) AS totalPrice,  
   SUM(orders.amount*orders.unit_price) AS total,  
   SUM(orders.child_amount) AS totalAmountC,  
   SUM(orders.child_price) AS totalPriceC,  
   SUM(orders.child_amount*orders.child_price) AS totalC,  
   pools.name, 
   pools.for_view 
   FROM (SELECT DISTINCT * FROM orders) AS orders
   INNER JOIN services ON services.id = orders.service_id  
   INNER JOIN pools ON pools.id = services.pool_id 
   WHERE issue_date = '2024-03-15' 
     AND pools.id = '35' 
     AND is_success = 1  
   GROUP BY pools.name, pools.for_view; 
   

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