多重LEFT JOIN导致SUM结果翻倍的SQL问题求助

问题根源 #

你遇到的SUM结果翻倍问题，本质是多表连接产生的笛卡尔积导致的：

• 当同时左连接tbl_orders和tbl_revenue到日历表时，如果某一天有N条订单记录、M条收入记录，连接后会生成N×M条重复的收入行
• 最终SUM计算时，每条收入数据会被重复统计N次（等于当天订单的数量），结果自然被放大。

解决方案 #

针对这个问题，有几种可行的处理方式：

1. 先聚合收入数据再连接

先对tbl_revenue按日期完成聚合，得到每天唯一的收入统计结果，再和日历表、订单表连接，避免重复行：

SELECT 
    c.dates,
    COALESCE(r.extra_in, 0) AS extra_in,
    COALESCE(r.extra_out, 0) AS extra_out
FROM tbl_calendar as c 
LEFT JOIN `tbl_orders` AS o ON c.dates = DATE(o.order_complete_time) 
LEFT JOIN (
    SELECT 
        r_date,
        SUM(CASE WHEN r_type = 0 THEN r_amount ELSE 0 END) AS extra_in,
        SUM(CASE WHEN r_type = 1 THEN r_amount ELSE 0 END) AS extra_out
    FROM tbl_revenue
    WHERE MONTH(r_date) = 10 AND YEAR(r_date) = 2023 AND r_date <= '2023-10-18'
    GROUP BY r_date
) AS r ON r.r_date = c.dates 
WHERE MONTH(c.dates) = 10 AND YEAR(c.dates) = 2023 AND c.dates <= '2023-10-18' 
GROUP BY c.dates, r.extra_in, r.extra_out
ORDER BY c.dates DESC;

2. 移除不必要的订单表连接

如果你的查询不需要使用tbl_orders中的任何字段或统计数据，直接去掉这个LEFT JOIN即可——这也是你测试后确认有效的方法。

3. 同时聚合订单与收入数据（如果需要订单统计）

如果需要同时展示当天的订单相关统计（比如订单数量），建议同时对tbl_orders按日期聚合，再和日历表、聚合后的收入表连接：

SELECT 
    c.dates,
    COALESCE(r.extra_in, 0) AS extra_in,
    COALESCE(r.extra_out, 0) AS extra_out,
    COALESCE(o.order_count, 0) AS order_count
FROM tbl_calendar as c 
LEFT JOIN (
    SELECT 
        DATE(order_complete_time) AS order_date,
        COUNT(*) AS order_count
    FROM `tbl_orders`
    WHERE MONTH(order_complete_time) = 10 AND YEAR(order_complete_time) = 2023 AND DATE(order_complete_time) <= '2023-10-18'
    GROUP BY DATE(order_complete_time)
) AS o ON c.dates = o.order_date
LEFT JOIN (
    SELECT 
        r_date,
        SUM(CASE WHEN r_type = 0 THEN r_amount ELSE 0 END) AS extra_in,
        SUM(CASE WHEN r_type = 1 THEN r_amount ELSE 0 END) AS extra_out
    FROM tbl_revenue
    WHERE MONTH(r_date) = 10 AND YEAR(r_date) = 2023 AND r_date <= '2023-10-18'
    GROUP BY r_date
) AS r ON r.r_date = c.dates 
WHERE MONTH(c.dates) = 10 AND YEAR(c.dates) = 2023 AND c.dates <= '2023-10-18' 
ORDER BY c.dates DESC;

核心逻辑 #

先对需要统计的表按日期做聚合，确保每个日期只有一条统计记录，再进行多表连接，就能彻底避免笛卡尔积导致的重复计算问题。

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