Oracle中decode函数与case when的使用

红太狼 2021-02-12 17:55 710阅读 0赞

DECODE()函数，它将输入数值与函数中的参数列表相比较，根据输入值返回一个对应值。函数的参数列表是由若干数值及其对应结果值组成的若干序偶形式。当然，如果未能与任何一个实参序偶匹配成功，则函数也有默认的返回值。

区别于SQL的其它函数，DECODE函数还能识别和操作空值。

语法：DECODE(control\_value,value1,result1\[,value2,result2…\]\[,default\_result\]);

语法解读：control \_value试图处理的数值。DECODE函数将该数值与后面的一系列的偶序相比较，以决定返回值。

value1是一组成序偶的数值。如果输入数值与之匹配成功，则相应的结果值将被返回。对应一个空的返回值，可以使用关键字NULL于之对应

result1 是一组成序偶的结果值。

default\_result 未能与任何一个值匹配时，函数返回的默认值。

例1：select decode(sign(to\_number(to\_char(sysdate,'MM'))-6),1,'下半年',-1,'上半年',0,'六月') as MM from dual;

sign()函数根据表达式的值是0、正数还是负数，分别返回0、1、-1；

dual是oracle的虚拟表，比如select sysdate from dual 查询系统当前时间；

此sql根据数据库查询出系统当前时间，取时间的月份，先转换成char型，再将char转换为number型，

当月份减6大于0时，显示下半年，小于0时显示上关年，等于0 时显示六月。

例2：一个员工表，employees和一个部门表，departments；

员工表有员工编号employee\_id，员工姓名employee\_name，员工工资salary和部门编码department\_id；

部门表有部门编号department\_id，部门名称department\_name；

部题描述：查出各部门的员工工资大于3000和小等于3000的人数。

sql语句：

````
  select d.department_id,  
          sum(decode(sign(e.salary - 3000),1,1,-1,0)) maxSal,  
          sum(decode(sign(3000 - e.salary),1,1,-1,0)) minSal  
          from employees e, departments d  
         where e.department_id = d.department_id  
         group by d.department_id;  
````
  
SQL解析：因为当工资小于等于3000时，都返回0，所以用-1代替小于和等于（即0和-1）的情况。

另一解决方法(用case when....then....end)：

````
  select d.department_id,  
          sum(case when e.salary - 3000 **>** 0 then 1 else 0 end ) maxSal ,  
          sum(case when 3000 - e.salary **>** 0 then 1 else 0 end ) minSal  
          from employees e, departments d  
          where e.department_id = d.department_id  
          group by d.department_id;   
````
  
case when 有多个条件时，用case when .... then .... when ... then .......end

````
  select m.guid as ID,  
          case   
            when m.superitemid is null then  
             '-1'  
            when m.superitemid = '0' then  
             '-1'  
            else  
             m.superitemid  
          end as PID,  
         m.code || '-' || m.name as TEXT,  
         m.code,  
         m.name  
    from T_PUBMOFDEP m  
````