备注:测试数据库版本为MySQL 8.0

如需要scott用户下建表及录入数据语句，可参考: scott建表及录入数据sql脚本

一.需求

计算某个列中所有值的累计和

比如，经常遇到的业务需求就是领导需要看每个月的销售额，已经累计到当月的销售额。

解决方案: 下面给出了一种解决方案，它展示了如何计算所有职员工资的累计和。

代码:

select e.ename, e.sal, ( select sum(d.sal) from emp d where d.empno <= e.empno) as running_total from emp e order by 3;

测试记录:

mysql> select e.ename, e.sal, -> ( select sum(d.sal) from emp d -> where d.empno <= e.empno) as running_total -> from emp e -> order by 3; +--------+---------+---------------+ | ename | sal | running_total | +--------+---------+---------------+ | SMITH | 800.00 | 800.00 | | ALLEN | 1600.00 | 2400.00 | | WARD | 1250.00 | 3650.00 | | JONES | 2975.00 | 6625.00 | | MARTIN | 1250.00 | 7875.00 | | BLAKE | 2850.00 | 10725.00 | | CLARK | 4000.00 | 14725.00 | | SCOTT | 3000.00 | 17725.00 | | KING | 4000.00 | 21725.00 | | TURNER | 1500.00 | 23225.00 | | ADAMS | 1100.00 | 24325.00 | | JAMES | 950.00 | 25275.00 | | FORD | 3000.00 | 28275.00 | | MILLER | 4000.00 | 32275.00 | +--------+---------+---------------+ 14 rows in set (0.00 sec)