Case表达式与decode()函数两者都是根据字段或者表达式作为判断条件,当条件满足
某个值或者范围时,对应有一个结果。多个结果由多个互补相同的条件限制。所以这两个的用法
很接近,大同小异,只是case的用法更灵活一些。
以下是通过例子讲述一下:(根据不同的职位进行工资调整)
查看emp信息:职位为SALESMAN,升工资为1.5倍,职位为MANAGER,升工资为2.0倍,职位为PRESIDENT,升工资为3.0倍,其他不升。
1、decode:
decode(column|ecpression,search1,result1,
[search2,result2,... ...]
[,default] );
字段或者表达式作为条件,当条件为search1,返回结果result1,...当不满足以上的条件,指定默认值为default。
SQL> select ename,job,deptno,sal,
2 decode(job,'PRESIDENT',3*sal,'MANAGER',2*sal,'SALESMAN',1.5*sal,sal) salary
3 from emp
4 order by deptno;
ENAME JOB DEPTNO SAL SALARY
---------- --------- ---------- ---------- ----------
CLARK MANAGER 10 2450 4900
KING PRESIDENT 10 5000 15000
MILLER CLERK 10 1300 1300
JONES MANAGER 20 2975 5950
FORD ANALYST 20 3000 3000
ADAMS CLERK 20 1100 1100
SMITH CLERK 20 800 800
SCOTT ANALYST 20 3000 3000
WARD SALESMAN 30 1250 1875
TURNER SALESMAN 30 1500 2250
ALLEN SALESMAN 30 1600 2400
ENAME JOB DEPTNO SAL SALARY
---------- --------- ---------- ---------- ----------
JAMES CLERK 30 950 950
BLAKE MANAGER 30 2850 5700
MARTIN SALESMAN 30 1250 1875
14 rows selected.
2、Case:(常搭配 when...then...)
SQL> select ename,job,deptno,sal,
2 Case job
3 when 'PRESIDENT'then 3*sal
4 when 'MANAGER' then 2*sal
5 when 'SALESMAN' then 1.5*sal
6 else sal
7 end salary
8 from emp
9 order by deptno;
ENAME JOB DEPTNO SAL SALARY
---------- --------- ---------- ---------- ----------
CLARK MANAGER 10 2450 4900
KING PRESIDENT 10 5000 15000
MILLER CLERK 10 1300 1300
JONES MANAGER 20 2975 5950
FORD ANALYST 20 3000 3000
ADAMS CLERK 20 1100 1100
SMITH CLERK 20 800 800
SCOTT ANALYST 20 3000 3000
WARD SALESMAN 30 1250 1875
TURNER SALESMAN 30 1500 2250
ALLEN SALESMAN 30 1600 2400
ENAME JOB DEPTNO SAL SALARY
---------- --------- ---------- ---------- ----------
JAMES CLERK 30 950 950
BLAKE MANAGER 30 2850 5700
MARTIN SALESMAN 30 1250 1875
14 rows selected.
以上查询出来的结果是一样的。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31392094/viewspace-2126003/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/31392094/viewspace-2126003/