MySQL三表联查结果数值倍增求助：按日期合并统计异常

问题原因 #

你的查询出现数值倍增的核心原因是直接关联tbl_amount和tbl_expense时产生了笛卡尔积。以你的示例数据为例：

• tbl_amount中有2条2022-08-12的记录
• tbl_expense中有2条2022-08-12的记录
  关联后会生成2*2=4条组合记录，SUM()计算时会把金额重复累加2次（(1000+2000)*2=6000），支出重复累加2次（(250+350)*2=1200），最终导致结果翻倍。

解决方案 #

正确的做法是先分别对金额表和支出表按日期聚合统计，再将统计结果按日期关联，避免笛卡尔积的产生。以下提供两种可行的SQL写法：

方法1：分别聚合后关联（支持FULL JOIN的MySQL版本）

SELECT
    COALESCE(a.daily_total, 0) AS daily_total,
    COALESCE(e.daily_expenses, 0) AS daily_expenses,
    COALESCE(a.date_added, e.date_added) AS date_added
FROM
    (SELECT 
         SUM(amount) AS daily_total, 
         date_added 
     FROM tbl_amount 
     GROUP BY date_added) a
FULL JOIN
    (SELECT 
         SUM(amount) AS daily_expenses, 
         date_added 
     FROM tbl_expense 
     GROUP BY date_added) e ON a.date_added = e.date_added
LEFT JOIN tbl_customer c ON COALESCE(a.date_added, e.date_added) = c.date_added
GROUP BY COALESCE(a.date_added, e.date_added);

方法2：用CTE获取所有日期后关联（兼容所有MySQL版本）

如果你的MySQL版本不支持FULL JOIN，可以先通过UNION获取三个表中所有的日期，再左关联聚合后的金额和支出数据：

WITH all_dates AS (
    SELECT date_added FROM tbl_customer
    UNION
    SELECT date_added FROM tbl_amount
    UNION
    SELECT date_added FROM tbl_expense
)
SELECT
    COALESCE(a.daily_total, 0) AS daily_total,
    COALESCE(e.daily_expenses, 0) AS daily_expenses,
    ad.date_added
FROM all_dates ad
LEFT JOIN (
    SELECT SUM(amount) AS daily_total, date_added 
    FROM tbl_amount 
    GROUP BY date_added
) a ON ad.date_added = a.date_added
LEFT JOIN (
    SELECT SUM(amount) AS daily_expenses, date_added 
    FROM tbl_expense 
    GROUP BY date_added
) e ON ad.date_added = e.date_added;

说明 #

• COALESCE()函数用于处理某一天只有金额、只有支出或两者都没有的情况，确保返回0而不是NULL。
• 先聚合再关联的方式从根本上避免了笛卡尔积，保证统计结果准确。
• 针对你的示例数据，执行上述任意SQL都会得到预期结果：

  daily_total | daily_expenses | date_added
  3000        | 600            | 2022-08-12
  

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