mysql - 按 id 将两列求和到一个字段

Question

我需要将 id 的两列求和到具有相同 id 的一个字段

例如:我想要的余额是收入 - 支出=余额

表事务:

=========================================================
|  id  | idBudget  |  expenses      ||      income      |
=====+========+===============+=====|=====+========+====|
|   1  |      2    |     10         ||        0         |  
|----+--------+---------------+-----||----+--------+----|
|   2  |      3    |     200        ||        0         |              
|----+--------+---------------+-----||----+--------+----|
|   3  |      2    |     1          ||     100          |  
|----+--------+---------------+-----||----+--------+----|
|   4  |      2    |     0          ||     1000         |              
|----+--------+---------------+-----||----+--------+----|

表预算:

=====================================
|  idBudget        |  Balance       |
=====+========+===============+=====|
|     2            |     1090       |
|----+--------+---------------+-----|
|     3            |     -200       |
|----+--------+---------------+-----|

我厌倦了使用触发器，但我想我不知道如何实现它

Answer 1

CREATE TABLE starting_balance AS (SELECT * FROM budget);

DROP TABLE budget;

CREATE VIEW budget AS (
    SELECT
        sb.idBudget,
        sb.balance + SUM(t.income - t.expenses) AS balance
    FROM starting_balance sb
    LEFT JOIN transactions t ON (t.idBudget = sb.idBudget)
    GROUP BY
        sb.idBudget,
        sb.balance
);

换句话说，使用 View 而不是表。您可以不时更新起始余额(并删除或标记您不再需要的交易!)，并且您可以使用物化 View 。