MySQL 实现累积求和的几种方法

场景

mysql> desc cum_demo;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | NULL    |       |
| money | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.09 sec)

mysql> SELECT * FROM cum_demo ORDER BY id ASC;
+----+-------+
| id | money |
+----+-------+
|  1 |    10 |
|  2 |    20 |
|  3 |    30 |
|  4 |    40 |
|  5 |    20 |
+----+-------+
5 rows in set (0.04 sec)

期望

通过 SQL 得到 cum 列，cum 为 money 的累积递增

+----+-------+-----+
| id | money | cum |
+----+-------+-----+
|  1 |    10 |  10 |
|  2 |    20 |  30 |
|  3 |    30 |  60 |
|  4 |    40 | 100 |
|  5 |    20 | 120 |
+----+-------+-----+

方法

临时变量法

SELECT id, money, @cum := money + @cum AS cum
FROM cum_demo, (SELECT @cum := 0) AS t
ORDER BY id ASC;
+----+-------+-----+
| id | money | cum |
+----+-------+-----+
|  1 |    10 |  10 |
|  2 |    20 |  30 |
|  3 |    30 |  60 |
|  4 |    40 | 100 |
|  5 |    20 | 120 |
+----+-------+-----+

join 方法

SELECT a.id,a.money,SUM(lt.money)  as cum
FROM cum_demo a JOIN cum_demo lt ON a.id >= lt.id
WHERE a.id >= lt.id
GROUP BY a.id
ORDER BY id ASC;
+----+-------+-----+
| id | money | cum |
+----+-------+-----+
|  1 |    10 | 10  |
|  2 |    20 | 30  |
|  3 |    30 | 60  |
|  4 |    40 | 100 |
|  5 |    20 | 120 |
+----+-------+-----+

窗口函数法

MySQL 8 可通过窗口函数实现

SELECT id, money, SUM(money) OVER(ORDER BY id ASC) AS cum
FROM cum_demo
ORDER BY id ASC;
+----+-------+-----+
| id | money | cum |
+----+-------+-----+
|  1 |    10 | 10  |
|  2 |    20 | 30  |
|  3 |    30 | 60  |
|  4 |    40 | 100 |
|  5 |    20 | 120 |
+----+-------+-----+

• https://blog.csdn.net/qcyfred/article/details/78045857
• https://www.cnblogs.com/bourneli/p/3248908.html