PostgreSQL中SUM子句计算订单商品总金额返回错误值如何解决

错误原因 #

你当前的SQL使用了隐式笛卡尔积连接，没有添加store_products和order_list两张表的关联条件，导致符合筛选条件的1条order_list记录和store_products表的全部4条记录做了关联，最终总和计算的是3*(5.97+5.97+21.9+1.5) = 106.02，和你得到的错误结果完全吻合。

正确查询写法 #

单条订单条目金额查询

如果要查询指定订单条目对应的合计金额，SQL如下：

SELECT sp.price * ol.quantity AS price_sum
FROM store_products AS sp
INNER JOIN order_list AS ol
ON sp.spid = ol.store_products_spid -- 核心关联条件，匹配商品ID
WHERE ol.order_date = '2021-09-03 20:22:33' 
AND ol.store_products_spid = 6 
AND ol.customers_cid = 3;

执行后返回的结果就是预期的4.5。

整笔订单总金额查询

如果需要统计同一笔订单（同一个客户、同一下单时间）的所有商品总金额，可以用聚合函数SUM：

SELECT SUM(sp.price * ol.quantity) AS order_total
FROM store_products AS sp
INNER JOIN order_list AS ol
ON sp.spid = ol.store_products_spid
WHERE ol.order_date = '2021-09-03 20:22:33' 
AND ol.customers_cid = 3;

批量更新order_list的price_sum字段

如果需要回填order_list表中所有空的price_sum字段，可以用以下更新语句：

UPDATE order_list ol
SET price_sum = (
    SELECT sp.price * ol.quantity
    FROM store_products sp
    WHERE sp.spid = ol.store_products_spid
);

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