SQL 累计求和,你想针对某一列进行累计求和。
SQL 累计求和 问题描述
你想针对某一列进行累计求和。
SQL 累计求和 解决方案
作为示例,下面的解决方案将介绍如何计算全体员工工资的累计额。为了便于理解,查询结果按照 SAL
列排序,这样很容易观察累计和的增长。
DB2 和 Oracle
使用 SUM
函数的窗口函数版本进行累计求和。
MySQL、PostgreSQL 和 SQL Server
使用标量子查询来进行累计求和(而不是使用如 SUM OVER
这样的窗口函数,因为不能像 DB2 和 Oracle 的解决方案那样方便地按照 SAL
列对结果集排序)。最终的累计和是正确的(最后一行的合计值与前面的 DB2 和 Oracle 解决方案的结果一致),但是中间结果因为缺少了排序操作而不尽相同。
SQL 累计求和 扩展知识
使用新的 ANSI 窗口函数很容易进行累计求和。对于尚未支持窗口函数的数据库管理系统而言,必须使用标量子查询(通过一个具有唯一值的字段做连接查询)。
DB2 和Oracle
使用窗口函数 SUM OVER
很容易进行累计求和。上述解决方案使用的 ORDER BY
子句的后面不仅有 SAL
列,还有 EMPNO
列(该列是主键),这是为了防止累计求和过程中出现重复值。以下示例中的 RUNNING_TOTAL2
列展示了重复值会导致什么样的问题。
员工 WARD、MARTIN、SCOTT 和 FORD 对应的 RUNNING_TOTAL2
是不正确的。这是因为他们的工资在 EMP
表里出现了不止一次,而这些重复值也被计算到累计和中。这就是为什么必须把 EMPNO
列(它是唯一的)加入排序项,才能得到正确的计算结果,即 RUNNING_TOTAL1
。考虑这样一种情况:对于 ADAMS,RUNNING_TOTAL1
和 RUNNING_TOTAL2
两列都是 2850。2850 加上 WARD 的工资 1250,应该得到 4100,然而 RUNNING_TOTAL2
返回的却是 5350。这是为什么呢?因为 WARD 和 MARTIN 的工资相同,这两个 1250 相加得到 2500,再加上 2850 就是 WARD 和 MARTIN 这两行对应的 5350。通过把若干列组合起来作为排序列,能够避免出现重复值(例如,SAL
和 EMPNO
的组合是唯一的),这样就能保证得到正确的累计和。
MySQL、PostgreSQL 和 SQL Server
MySQL、PostgreSQL 和 SQL Server 因为这些数据库管理系统尚未完全支持窗口函数,所以我们只能通过标量子查询来计算累计和。必须通过一个有唯一值的列做连接查询,否则,如果两个员工的工资相同,那么得到的累计和就是错误的。对于本实例而言,关键在于把 D.EMPNO
和 E.EMPNO
连接起来,并针对每个小于或者等于 E.EMPNO
的 D.EMPNO
计算出对应的 D.SAL
。如果把标量子查询改写成一个 EMP
表全体数据与部分数据之间的连接查询,会更加容易理解这一点。
EMPNO2
列中的每一个值都会和 EMPNO1
列中对应的值进行比较。如果 EMPNO2
列中的值小于或者等于 EMPNO1
列中对应的值,则其对应的 SAL2
列的值会被计入总和。对于上述查询而言,员工 SMITH、ALLEN、WARD 和 JONES 的 EMPNO
值会和 JONES 的 EMPNO
值相比较。由于四个员工的 EMPNO
值都满足小于或者等于 JONES 的 EMPNO
值这一条件,因此他们的工资都会被计入总和。反之,(在上述查询中)任何 EMPNO
值大于 JONES 的 EMPNO
值的员工,其工资都不会被计入总和。对于完整的查询而言,其工作方式也是同样的,如果 EMPNO
值小于或者等于全表的最大值 7934(MILLER 的 EMPNO
值),则其对应的工资就会被计入总和。