MySQL 实现累积求和的几种方法
场景mysql> desc cum_demo;+-------+---------+------+-----+---------+-------+| Field | Type| Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| id| int(11) | NO| PRI | NU
·
场景
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
更多推荐
已为社区贡献2条内容
所有评论(0)