MySQL分组查询时如何实现单行仅求和一次

嘿，这个坑我之前踩过！核心问题是订单和订单项的一对多关联会让运费被重复统计——如果直接把两个表JOIN后分组求和，每个订单项都会带一遍所属订单的运费，结果总运费就会被乘以该订单的商品数量，完全偏离预期。

先明确你的场景：

• 两个订单，每个运费10美元
• 订单A：2个商品，价格1美元+2美元
• 订单B：1个商品，价格3美元
• 正确的每日统计应该是：总运费20美元，总商品价格6美元

如果用常规的JOIN写法：

SELECT 
    o.created,
    SUM(o.shipping) AS total_shipping,
    SUM(i.price) AS total_item_price
FROM `Order` o
JOIN Item i ON o.id = i.order_id
GROUP BY o.created

你会得到total_shipping = 10*2 + 10*1 = 30，这明显错了，因为订单A的运费被算了两次（对应两个商品）。

正确的解决方案 #

有两种常用的写法，都能避免重复计算：

方法1：先分别统计，再关联结果

先单独统计每日的总运费，再单独统计每日的商品总价，最后按日期把两个结果合并：

SELECT 
    COALESCE(s.created, p.created) AS created_date,
    COALESCE(s.total_shipping, 0) AS total_shipping,
    COALESCE(p.total_item_price, 0) AS total_item_price
FROM
    -- 统计每日总运费
    (SELECT created, SUM(shipping) AS total_shipping
     FROM `Order`
     GROUP BY created) s
-- 全外连接，确保某天只有订单或只有商品时也能统计到
FULL OUTER JOIN
    -- 统计每日商品总价
    (SELECT o.created, SUM(i.price) AS total_item_price
     FROM Item i
     JOIN `Order` o ON i.order_id = o.id
     GROUP BY o.created) p
ON s.created = p.created

如果你的数据库不支持FULL OUTER JOIN（比如MySQL），可以换成LEFT JOIN + UNION来覆盖所有日期场景。

方法2：用UNION ALL拆分记录再聚合

把每个订单的运费、每个商品的价格拆成独立的行，再按日期分组求和：

SELECT 
    created_date,
    SUM(total_shipping) AS total_shipping,
    SUM(total_item_price) AS total_item_price
FROM
    (SELECT 
         -- 把订单日期作为分组键，运费单独一行
         created AS created_date,
         shipping AS total_shipping,
         0 AS total_item_price
     FROM `Order`
     UNION ALL
     SELECT 
         -- 商品关联订单日期，价格单独一行
         o.created AS created_date,
         0 AS total_shipping,
         i.price AS total_item_price
     FROM Item i
     JOIN `Order` o ON i.order_id = o.id) temp
GROUP BY created_date

这个方法更通用，几乎所有数据库都支持。代入你的场景，临时表会生成5行数据：

• (订单A日期, 10, 0)
• (订单A日期, 0, 1)
• (订单A日期, 0, 2)
• (订单B日期, 10, 0)
• (订单B日期, 0, 3)
  分组求和后正好得到总运费20，总商品价格6，完全正确。

注意事项 #

• Order是SQL的保留关键字，所以要用反引号（MySQL）或方括号（SQL Server）包裹表名，避免语法错误
• 如果created是 datetime 类型，记得用DATE(created)（或对应数据库的日期截取函数）来分组，确保按“日”统计，而不是精确到时分秒
• 用COALESCE处理NULL值，避免某天没有订单或没有商品时出现空值

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