备注:测试数据库版本为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)