case表达式与decode函数_case decode-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_33856123/article/details/53954671

1、语法如下:

DECODE(value, if1, then1, if2, then2, if3, then3,...ifn, thenn, default)

decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)

表示如果value等于if1时，DECODE函数的结果返回then1,...,如果不等于任何一个if值，则返回else。亦即：

CASE expr WHEN expr1 THEN return_expr1
         [WHEN expr2 THEN return_expr2
          ...
          WHEN exprn THEN return_exprn
          ELSE else_expr]
END

CASE
         WHEN expr1 THEN return_expr1
         [WHEN expr2 THEN return_expr2
          ....
          WHEN exprn THEN return_exprn
          ELSE else_expr]
END

2、改写示例：（此处是case when 的等值用法，不等值用法稍后示例）

select ename,sal,case deptno when 10 then 'dept10'
                             when 20 then 'dept20'
                             when 30 then 'dept30'
                 else 'other' end department
from emp ;

select ename,sal,case when deptno=10 then 'dept10'
                      when deptno =20 then 'dept20'
                      when deptno =30 then 'dept30'
                 else 'other' end department

用decode改写

select ename,sal,
decode(deptno,10,'dept10',20,'dept20',30,'dept30','other') department
from emp;

3、区别：

1、DECODE 只能用做相等判断,但是可以配合sign函数进行大于，小于，等于的判断,CASE可用于=,>=,<,<=,<>,is null,is not null 等的判断;

2、在decode中，null和null是相等的，但在case when中，只能用is null来判断。

emp表中有一列comm，如果这列为null,则显示为0，否则，显示为原值：

--decode可以显示我们要求的结果
SQL> select ename,decode(comm,null,0,comm) comma from emp;

ENAME                     COMMA
-------------------- ----------
SMITH                         0
ALLEN                       300
WARD                        500
JONES                         0
MARTIN                     1400
BLAKE                         0
CLARK                         0
SCOTT                         0
KING                          0
TURNER                        0
ADAMS                         0
JAMES                         0
FORD                          0
MILLER                        0

-- null没有转成0，仍然是null,不是我们要求的结果
SQL> select ename,(case comm when null then 0 else comm end) comm from emp;

ENAME                      COMM
-------------------- ----------
SMITH
ALLEN                       300
WARD                        500
JONES
MARTIN                     1400
BLAKE
CLARK
SCOTT
KING
TURNER                        0
ADAMS
JAMES
FORD
MILLER

--这样才可以成功将null显示为0
SQL> select ename,(case when comm is null then 0 else comm end) comm from emp;

ENAME                      COMM
-------------------- ----------
SMITH                         0
ALLEN                       300
WARD                        500
JONES                         0
MARTIN                     1400
BLAKE                         0
CLARK                         0
SCOTT                         0
KING                          0
TURNER                        0
ADAMS                         0
JAMES                         0
FORD                          0
MILLER                        0

4、case when 用法追加

select sal,case
when sal between '800' and '1600' then
'低工资'
when sal between '1601' and '14000.00' then
'正常工资'
end sal
from emp

select decode(sign(sal-1601) ,'-1','低工资','正常工资'),sum(sal)
from emp
group by decode(sign(sal-1601) ,'-1','低工资','正常工资')

可以在select 中使用CASE 外，where 子句,group by 子句,order by 子句都可以使用

-----------
create table titles
(rma_center number,name varchar2(44))
-----------
select * from titles for update
4 |sdfeg
23 |ASde
45 |sssdf
55 |e3fbg
123 qw
222 fsfe
555 fv
579 lojgdex
1111 a
-----------
select name,
CASE
WHEN rma_center IS NULL THEN 'Null'
WHEN rma_center > 1000 THEN '>1000'
WHEN rma_center BETWEEN 30 and 100 THEN '30~100'
ELSE 'others' END AS "RMA CENTER Type"
from titles
where CASE
WHEN rma_center IS NULL THEN 'Null'
WHEN rma_center > 1000 THEN '>1000'
WHEN rma_center BETWEEN 30 and 100 THEN '30~100'
ELSE 'others' END in('30~100','>1000') ------此处in 过滤条件
group by CASE
WHEN rma_center IS NULL THEN 'Null'
WHEN rma_center > 1000 THEN '>1000'
WHEN rma_center BETWEEN 30 and 100 THEN '30~100'
ELSE 'others' END,
name
order by CASE
WHEN rma_center IS NULL THEN 'Null'
WHEN rma_center > 1000 THEN '>1000'
WHEN rma_center BETWEEN 30 and 100 THEN '30~100'
ELSE 'others' END,
name

假设我们想给百度职员加工资，其标准是：工资在8000元以下的将加20％；工资在8000元以上的加15％

则：

select decode(sign(salary - 8000),1,salary1.15,-1,salary1.2,salary) "NEW_SALARY" from employee

-- 例子：
select greatest(0,10) , greatest(-10,0) from dual

5、与其他数据库联系：

1.DECODE 只有Oracle 才有，其它数据库不支持;
2.CASE WHEN的用法， Oracle、SQL Server、 MySQL 都支持;

6、Oracle 行转列 Case When,Decode两种方式

成绩表（CHENGJI）如下：
create table CHENGJI （XINGMING varchar2(30),KEMU varchar2(30),CHENGJI number)
XINGMING KEMU CHENGJI
1 张三语文 89.00
2 张三数学 98.00
3 张三英语 88.00
4 李四语文 90.00
5 李四数学 89.00
6 李四英语 60.00
7 王五数学 66.00
8 王五英语 99.00

方法一、DECODE
SELECT CJ.XINGMING,
SUM(DECODE(CJ.KEMU, '语文', CJ.CHENGJI, 0)) 语文,
SUM(DECODE(CJ.KEMU, '数学', CJ.CHENGJI, 0)) 数学,
SUM(DECODE(CJ.KEMU, '英语', CJ.CHENGJI, 0)) 英语
FROM CHENGJI CJ
GROUP BY CJ.XINGMING
XINGMING 语文数学英语
1 张三 89 98 88
2 王五 0 66 99
3 李四 90 89 60

方法二、CASE WHEN

SELECT CJ.XINGMING,
SUM( CASE WHEN CJ.KEMU = '语文' THEN CJ.CHENGJI ELSE 0 END ) 语文,
SUM( CASE WHEN CJ.KEMU = '数学' THEN CJ.CHENGJI ELSE 0 END ) 数学,
SUM( CASE WHEN CJ.KEMU = '英语' THEN CJ.CHENGJI ELSE 0 END ) 英语
FROM CHENGJI CJ
GROUP BY CJ.XINGMING
XINGMING 语文数学英语
1 张三 89 98 88
2 王五 0 66 99
3 李四 90 89 60