sql 语句系列(加减乘除与平均)[八百章之第十四章]

avg的注意事项

一张t2表:
select * from t2

select AVG(sal) from t2

得到的结果是:

本来我们得到的结果应该是10的。但是得到的结果确实15.

这是因为忽略了null值。

解决方案:
当为空的时候转换为0

select AVG(coalesce(sal,0)) from t2

max 与 min的注意事项

max与min会忽略null,但是如果全是null的情况,会返回null值。

select DEPTNO,max(COMM)from EMPwhere DEPTNO in (10,30)group by DEPTNO 

例如:

解决方法:
如上文

select DEPTNO,max(coalesce(COMM,0))from EMPwhere DEPTNO in (10,30)group by DEPTNO 

sum

sum 计算的时候会忽略null值。
如上:
解决:

select DEPTNO,sum(coalesce(COMM,0))from EMPwhere DEPTNO in (10,30)group by DEPTNO 

计算行数

通常我们计算行数,通常是使用count。

同样是会忽略null值:

select COUNT(*),count(COMM)from EMP

累计和

要实现下面这种效果:


比如2600,是红框部分的和。

假设前两列的效果是:

select e.EMPNO, e.SALfrom EMP eorder by 1

要求写出第三列的效果。

答案:

select e.EMPNO, e.SAL,(select SUM(d.SAL) from EMP d where d.EMPNO<=e.EMPNO) as totalfrom EMP eorder by 3

累计乘

如何实现累计乘法?
有一个数学概念:

select e.EMPNO, e.SAL,(select exp(sum(log(d.SAL))) from EMP d where d.EMPNO<=e.EMPNO and d.DEPTNO=e.DEPTNO ) as totalfrom EMP ewhere DEPTNO=10order by 3

累计差

分情况讨论,当是第一个直接输出第一个。后面的就是全部是负值相加然后加两个EMPNO最小值。

select e.EMPNO, e.SAL,(select case when e.EMPNO=MIN(d.EMPNO) then sum(d.SAL) else sum(-d.SAL)+(select f.SAL from emp f where f.EMPNO=MIN(d.EMPNO))*2 end from EMP d where d.EMPNO<=e.EMPNO and d.DEPTNO=e.DEPTNO ) as totalfrom EMP ewhere DEPTNO=10order by EMPNO

相关文章