mysql - SQL 查询返回按月分组的列总和，包括所有先前日期

Question

我正在尝试返回按月汇总、按账户和月份分组的账户余额。例如，给定一个如下所示的交易表:

| date       | account_id | trans_amount |
| 2012-01-01 |          1 |        -2500 |
| 2012-01-01 |          2 |        -2500 |
| 2016-01-01 |          1 |         5000 |
| 2016-01-01 |          2 |         5000 |
| 2016-02-01 |          1 |         7500 |
| 2016-02-01 |          2 |         7500 |

我希望查询返回:

| month | year | account_id | balance |
| 01    | 2012 |          1 |   -2500 |
| 01    | 2012 |          2 |   -2500 |
| 01    | 2016 |          1 |    2500 |
| 01    | 2016 |          2 |    2500 |
| 02    | 2016 |          1 |   10000 |
| 02    | 2016 |          2 |   10000 |

我从这个开始:

SELECT MONTH(date), YEAR(date), account_id, SUM(trans_amount)
FROM transactions
GROUP BY account_id, MONTH(date), YEAR(date);

当然，这给出了分组期间的变化，而不是包括该期间之前的交易在内的余额。

Answer 1

最简单的方法是嵌套查询。像这样:

SELECT MONTH(date) as month, YEAR(date) as year, account_id, (select SUM(trans_amount) from transactions t2 where t1.date>=t2.date and t1.account_id = t2.account_id) as balance
FROM transactions t1
GROUP BY account_id, MONTH(date), YEAR(date);

您很可能需要稍微修正一下语法。我也不完全确定日期与分组的匹配程度，因此您可能需要为内部选择构建一个合成日期。