MySQL计算收支差额结果异常，求正确实现方案

兄弟，你这个问题我之前踩过一模一样的坑！问题根源出在你写的FROM income, expenses上——这是隐式交叉连接，会让两个表做笛卡尔积：每一条收入记录都会和每一条支出记录配对组合。

举个实际的例子，你的收入表有7条数据，支出表有195条，交叉之后就会生成7×195=1365行数据。这时候你用SUM(rent)得到的其实是10150×195，SUM(cost)是4576.51×7，算出来的差额自然完全不对了。

给你几个靠谱的解决方法，随便选一个都能得到你想要的5573.49：

方法1：用独立子查询计算总和后相减 #

这种方法最直观，直接分别算出收入和支出的总金额，再做减法：

SELECT 
    (SELECT SUM(rent) FROM income) AS total_income,
    (SELECT SUM(cost) FROM expenses) AS total_expenses,
    (SELECT SUM(rent) FROM income) - (SELECT SUM(cost) FROM expenses) AS grand_total;

方法2：将总和作为临时表后计算 #

把两个表的总和分别作为单行列的临时表，再交叉连接（因为都是单行，交叉后还是一行，不会有笛卡尔积问题）：

SELECT 
    inc.total_income,
    exp.total_expenses,
    inc.total_income - exp.total_expenses AS grand_total
FROM
    (SELECT SUM(rent) AS total_income FROM income) inc,
    (SELECT SUM(cost) AS total_expenses FROM expenses) exp;

方法3：用UNION ALL合并后聚合 #

先把收支的总金额合并到一个数据集里，再统一计算差额：

SELECT
    SUM(income_amount) AS total_income,
    SUM(expense_amount) AS total_expenses,
    SUM(income_amount) - SUM(expense_amount) AS grand_total
FROM (
    SELECT SUM(rent) AS income_amount, 0 AS expense_amount FROM income
    UNION ALL
    SELECT 0 AS income_amount, SUM(cost) AS expense_amount FROM expenses
) combined;

核心思路就是：一定要确保你是在两个独立的总金额数值之间做减法，而不是在交叉连接后的大数据集上做sum操作。试试上面的方法，肯定能得到你想要的结果！

内容来源于stack exchange