MySQL SUM()函数计算结果异常问题求助

问题根源 #

多表JOIN操作时，单个mco_order订单会对应多条mco_order_line订单行或mco_ticket_instance票据实例，导致同一订单的grand_total字段在连接结果中重复出现。SUM()函数会累加这些重复值，最终结果等于实际销售额乘以重复次数，比如Josh的实际总额1130.90被重复计算后得到了107598.90。

可行解决方案 #

方案1：先聚合订单数据，再关联其他表（推荐） #

先对每个客户的订单金额、订单数做独立聚合，得到正确的销售额后，再关联其他表统计票据实例数量，从根源避免订单行重复。

SELECT 
    c.creation_date,
    c.id,
    o_stats.order_count,
    COUNT(DISTINCT ti.id) AS ticket_instance_count,
    c.first_name,
    o_stats.total_grand_total AS GrandTotal
FROM mco_customer c
INNER JOIN (
    -- 先按客户聚合订单数据，确保销售额计算准确
    SELECT 
        customer_id,
        COUNT(id) AS order_count,
        SUM(grand_total) AS total_grand_total
    FROM mco_order
    WHERE status = 'order_paid'
    GROUP BY customer_id
) o_stats ON o_stats.customer_id = c.id
-- 关联其他表仅用于统计票据实例数量
INNER JOIN mco_order o ON o.customer_id = c.id AND o.status = 'order_paid'
INNER JOIN mco_order_line ol ON o.id = ol.order_id
INNER JOIN mco_ticket_instance ti ON ti.order_line_id = ol.id
GROUP BY c.id, c.creation_date, c.first_name, o_stats.order_count, o_stats.total_grand_total
ORDER BY ticket_instance_count DESC;

方案2：子查询统计票据数量 #

如果不需要同时关联所有表，可通过子查询单独获取每个客户的票据实例数，避免多表连接带来的重复行问题：

SELECT 
    c.creation_date,
    c.id,
    COUNT(o.id) AS order_count,
    (
        SELECT COUNT(ti.id)
        FROM mco_order o_sub
        INNER JOIN mco_order_line ol_sub ON o_sub.id = ol_sub.order_id
        INNER JOIN mco_ticket_instance ti ON ti.order_line_id = ol_sub.id
        WHERE o_sub.customer_id = c.id AND o_sub.status = 'order_paid'
    ) AS ticket_instance_count,
    c.first_name,
    SUM(o.grand_total) AS GrandTotal
FROM mco_customer c 
INNER JOIN mco_order o ON o.customer_id = c.id AND o.status = 'order_paid'
GROUP BY c.id, c.creation_date, c.first_name
ORDER BY ticket_instance_count DESC;

方案3：SUM(DISTINCT)（仅特殊场景可用） #

如果能确保每个客户的所有订单grand_total金额完全唯一，可使用SUM(DISTINCT o.grand_total)临时解决，但如果存在金额相同的订单，会被错误去重导致结果偏小，因此不推荐作为通用方案：

-- 仅适用于无重复订单金额的场景，谨慎使用
SELECT 
    c.creation_date,
    c.id,
    COUNT(DISTINCT o.id),
    COUNT(DISTINCT ti.id),
    c.first_name,
    SUM(DISTINCT o.grand_total) AS GrandTotal
FROM mco_customer c 
INNER JOIN mco_order o ON o.customer_id = c.id AND o.status = 'order_paid'
INNER JOIN mco_order_line ol ON o.id = ol.order_id 
INNER JOIN mco_ticket_type tt ON tt.id = ol.ticket_type_id
INNER JOIN mco_ticket_instance ti ON ti.order_line_id = ol.id
GROUP BY c.id
ORDER BY COUNT(DISTINCT ti.id) DESC;

结果验证 #

使用方案1或方案2后，Josh的GrandTotal会正确显示为1130.90，其他客户的销售额也会回归实际值，同时保留订单数、票据实例数的统计准确性。

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