计算累加乘
需求描述
需求:计算EMP表里SAL字段的累计乘积值,这里按照SAL、EMPNO排序.
解决方法:通过SUM OVER() EXP LOG LN函数来完成.
注: 数据库数据集SQL脚本详见如下链接地址
SQL代码
-- Sql Server:
SELECT sal,empno,LOG(sal) AS Log_Sal,EXP(SUM(LOG(sal))OVER(ORDER BY sal,empno)) Accu_Prod
FROM emp;
执行结果
-- Oracle:
SELECT sal,empno,LN(sal) AS Log_Sal,EXP(SUM(LN(sal))OVER(ORDER BY sal,empno)) Accu_Prod
FROM emp;
-- Mysql:
SELECT B.sal,B.empno,(SELECT exp(SUM(ln(A.sal))) FROM emp A WHERE A.empno<=B.empno) AS Accu_Cnt
FROM emp B
ORDER BY Accu_Cnt
SELECT B.sal,B.empno,exp(SUM(ln(A.sal))) AS Accu_Cnt
FROM emp B
JOIN emp A
ON A.empno <=B.empno
GROUP BY B.sal,B.empno
ORDER BY Accu_Cnt