MySQL查询求和结果异常：收入与支出表关联查询错误

你的查询使用INNER JOIN时未指定月份关联条件，且仅过滤了收入表的年份，这会导致收入表与支出表产生笛卡尔积——每一条收入记录都会和所有支出记录配对。最终求和时，收入总额会被重复计算（重复次数等于对应月份支出记录的数量），支出总额同理，因此求和结果远大于实际值。

正确的思路是先分别对收入表、支出表按月份独立统计求和，再将两个统计结果按月份关联，确保每个月份的收入和支出是单独计算后再合并的。

方法1：子查询+全连接（推荐） #

如果你的MySQL版本支持FULL OUTER JOIN（MySQL 8.0及以上），可以用CTE先拆分统计：

WITH monthly_income AS (
    SELECT MONTH(date) AS month, SUM(total_amount) AS income
    FROM income
    WHERE YEAR(date) = YEAR(CURRENT_DATE)
    GROUP BY MONTH(date)
),
monthly_outcome AS (
    SELECT MONTH(date) AS month, SUM(total_amount) AS outcome
    FROM outcome
    WHERE YEAR(date) = YEAR(CURRENT_DATE)
    GROUP BY MONTH(date)
)
SELECT
    COALESCE(mi.month, mo.month) AS month,
    COALESCE(mi.income, 0) AS income,
    COALESCE(mo.outcome, 0) AS outcome
FROM monthly_income mi
FULL OUTER JOIN monthly_outcome mo ON mi.month = mo.month
ORDER BY month;

若MySQL版本不支持全连接，可通过LEFT JOIN + UNION RIGHT JOIN实现相同效果：

SELECT
    COALESCE(i.month, o.month) AS month,
    COALESCE(i.income, 0) AS income,
    COALESCE(o.outcome, 0) AS outcome
FROM
    (SELECT MONTH(date) AS month, SUM(total_amount) AS income FROM income WHERE YEAR(date) = YEAR(CURRENT_DATE) GROUP BY MONTH(date)) i
LEFT JOIN
    (SELECT MONTH(date) AS month, SUM(total_amount) AS outcome FROM outcome WHERE YEAR(date) = YEAR(CURRENT_DATE) GROUP BY MONTH(date)) o ON i.month = o.month
UNION
SELECT
    COALESCE(i.month, o.month) AS month,
    COALESCE(i.income, 0) AS income,
    COALESCE(o.outcome, 0) AS outcome
FROM
    (SELECT MONTH(date) AS month, SUM(total_amount) AS income FROM income WHERE YEAR(date) = YEAR(CURRENT_DATE) GROUP BY MONTH(date)) i
RIGHT JOIN
    (SELECT MONTH(date) AS month, SUM(total_amount) AS outcome FROM outcome WHERE YEAR(date) = YEAR(CURRENT_DATE) GROUP BY MONTH(date)) o ON i.month = o.month
ORDER BY month;

方法2：UNION ALL后聚合 #

将收入、支出数据合并为统一结构，再按月份和类型聚合后转成列：

SELECT
    month,
    SUM(CASE WHEN type = 'income' THEN amount ELSE 0 END) AS income,
    SUM(CASE WHEN type = 'outcome' THEN amount ELSE 0 END) AS outcome
FROM (
    SELECT
        MONTH(date) AS month,
        total_amount AS amount,
        'income' AS type
    FROM income
    WHERE YEAR(date) = YEAR(CURRENT_DATE)
    UNION ALL
    SELECT
        MONTH(date) AS month,
        total_amount AS amount,
        'outcome' AS type
    FROM outcome
    WHERE YEAR(date) = YEAR(CURRENT_DATE)
) AS combined
GROUP BY month
ORDER BY month;

• COALESCE函数用于处理“某月份只有收入/只有支出”的场景，避免结果出现NULL，统一显示为0。
• 必须先对单表聚合再关联，彻底避免笛卡尔积导致的重复计算。
• 两个表都要添加年份过滤条件，防止跨年度数据干扰统计结果。

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