nvl nvl2 case 分组函数

nvl(p1,p2): 如果p1为空,就转换成p2;

SQL> select ename,sal + nvl(comm,0) "工资" from emp;

nvl2(p1,p2,p3):如是p1不为null,返回p2,如果为null,返回p3;

SQL> select ename, nvl2(comm,sal+comm,sal) "工资" from emp;

case

when p1 then q1

[when p2 then q2

else q3]

end

select ename,hiredate,
case
when to_char(hiredate,'yyyy-mm-dd')<'1981-01-01' then '老一辈'
when to_char(hiredate,'yyyy-mm-dd')<'1985-01-01' then '中层干部'
else '新人'
end
from emp

分组函数:avg,  count,max ,min,sum

avg:  select avg(nvl2(comm,comm,0)) from emp;

注意：

１．在select 列表中的字段必须是组函数(avg,count,max,min,sum)  中的参数或者group by　子句．

２．在group by　p1 ,p1可以不出现在查询列表中.

３．where语句中不允许出现组函数(avg,count,max,min,sum) ,用having代替．

select deptno,max(sal) from emp group by deptno having max(sal)>2000;

语句执行顺序，where->group by->having->order by

转载于:https://www.cnblogs.com/god25/archive/2007/06/05/772290.html