mysql - 计算每个类别组的余额

Question

我有一个包含类别组的表 my_table，每个类别都有初始预算(original_budget):

我正在尝试添加一个新列 balance，以便它包含从每个类别组中的 original_budget 中减少 expense 后的余额。像这样的东西:

我的尝试:

SELECT category, expense, original_budget, (original_budget-expense) AS balance
FROM my_table GROUP BY category order by `trans_date`

MySQL version: innodb_version 5.7.25 10.2.23-MariaDB

Answer 1

如果您使用的是 MySQL 8+，那么在这里使用 SUM 作为窗口函数会相当简单:

SELECT
    trans_date,
    category,
    expense,
    original_budget,
    original_budget - SUM(expense) OVER
        (PARTITION BY category
         ORDER BY trans_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) balance
FROM my_table
ORDER BY
    category,
    trans_date;

Demo

在早期版本的 MySQL 上，我们可以尝试使用相关子查询来计算滚动余额:

SELECT
    trans_date,
    category,
    expense,
    original_budget,
    original_budget - (SELECT SUM(t2.expense) FROM my_table t2
                       WHERE t1.category = t2.category AND
                             t2.trans_date <= t1.trans_date) balance
FROM my_table t1
ORDER BY
    category,
    trans_date;

Demo