oracle中case和decode的用法

最新推荐文章于 2024-04-26 19:52:36 发布

ciweibian2792

最新推荐文章于 2024-04-26 19:52:36 发布

阅读量194

点赞数

文章标签：数据库

总结：

DECODE 与CASE WHEN 的比较

1.DECODE 只有Oracle 才有，其它数据库不支持;

2.CASE WHEN的用法， Oracle、SQL Server、 MySQL 都支持;

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

4.DECODE 使用其来比较简洁，CASE 虽然复杂但更为灵活;

5.另外，在decode中，null和null是相等的，但在case when中，只能用is null来判断；decode函数还能用于行转列。

一、case表达式用法：
case when

case when 类似我们的if ...then..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
-- 如果部门编号为10的，显示为sal*1.1

-- 如果部门编号为20的，显示为sal*1.2

-- 如果部门编号为30的，显示为sal*1.3

-- 否则显示为sal

-- 这一列查询的结果，列名显示为 isal
SQL> select ename,deptno,sal,case deptno when 10 then sal*1.1

2 when 20 then sal*1.2

3 when 30 then sal*1.3

4 else sal

5 end isal from test_emp;

ENAME DEPTNO SAL ISAL
---------- ---------- ---------- ----------

AAAABC

SMITH 20 800 960

ALLEN 30 1600 2080

WARD 30 1250 1625

JONES 20 2975 3570

MARTIN 30 1250 1625

BLAKE 30 2850 3705

CLARK 10 2450 2695

SCOTT 20 3002 3602.4

KING 10 5000 5500

TURNER 30 1500 1950

ADAMS 20 1100 1320

JAMES 30 950 1235

FORD 20 3000 3600

MILLER 10 1300 1430

15 rows selected.

第二种写法：
SQL> select ename,deptno,sal,case when deptno=10 then sal*1.1

2 when deptno=20 then sal*1.2

3 when deptno=30 then sal*1.3

4 else sal

5 end isal from test_emp;

ENAME DEPTNO SAL ISAL
---------- ---------- ---------- ----------

AAAABC

SMITH 20 800 960

ALLEN 30 1600 2080

WARD 30 1250 1625

JONES 20 2975 3570

MARTIN 30 1250 1625

BLAKE 30 2850 3705

CLARK 10 2450 2695

SCOTT 20 3002 3602.4

KING 10 5000 5500

TURNER 30 1500 1950

ADAMS 20 1100 1320

JAMES 30 950 1235

FORD 20 3000 3600

MILLER 10 1300 1430

15 rows selected.

case不等值表达式的用法： SQL> select ename,deptno,sal,case when sal<2000 then sal+200

2 when sal between 2000 and 3000 then sal+100

3 else sal

4 end isal from test_emp;

ENAME DEPTNO SAL ISAL
---------- ---------- ---------- ----------

AAAABC

SMITH 20 800 1000

ALLEN 30 1600 1800

WARD 30 1250 1450

JONES 20 2975 3075

MARTIN 30 1250 1450

BLAKE 30 2850 2950

CLARK 10 2450 2550

SCOTT 20 3002 3002

KING 10 5000 5000

TURNER 30 1500 1700

ADAMS 20 1100 1300

JAMES 30 950 1150

FORD 20 3000 3100

MILLER 10 1300 1500

15 rows selected.

case用于判断空值，避免使用空值函数
SQL> select ename,sal,comm,case when comm is null then sal else sal+comm end isal from emp;

ENAME DEPTNO SAL ISAL
---------- ---------- ---------- ----------

AAAABC

SMITH 800 800

ALLEN 1600 300 1900

WARD 1250 500 1750

JONES 2975 2975

MARTIN 1250 1400 2650

BLAKE 2850 2850

CLARK 2450 2450

SCOTT 3002 3002

KING 5000 5000

TURNER 1500 0 1500

ADAMS 1100 1100

JAMES 950 950

FORD 3000 3000

MILLER 1300 1300

15 rows selected.

二、decode函数用法
语法 :

DECODE(col|expression, search1, result1

[, search2, result2,...,]

...

[, searchn, resultn,...,]

[, default])

如果条件=值1,那么显示结果1

如果条件=值2,那么显示结果2

....

如果条件=值n,那么显示结果n

都不符合，则显示缺省值
decode函数等值表达式的用法，与上面case表达式用法相比简便一些：
SQL> select ename,deptno,sal,decode(deptno,10,1.1*sal,20,1.2*sal,30,1.3*sal)isal from emp;
ENAME DEPTNO SAL       ISAL
----------    ----------   ---------- ----------
AAAABC
SMITH 20    800 960
ALLEN 30 1600 2080
WARD 30    1250 1625
JONES 20 2975 3570
MARTIN    30   1250 1625
BLAKE 30 2850 3705
CLARK 10 2450 2695
SCOTT 20 3002 3602.4
KING 10 5000 5500
TURNER 30 1500 1950
ADAMS 20 1100 1320
JAMES 30 950 1235
FORD 20 3000 3600
MILLER 10 1300 1430
15 rows selected.
注：decode不能用于非等值用法。

延伸用法：与sign函数联用比较大小

注：sign()函数根据参数1的值是0、正数还是负数，分别返回0、1、-1

select ename,sal,decode(sign(sal-2000),1, '>2000 ',-1, '<2000 ', '=2000 ') sal_level

from emp;

ENAME SAL SAL_LEVEL

-------------------- ---------- -----------

SMITH 800 <2000

ALLEN 1600 <2000

WARD 1250 <2000

JONES 2975 >2000

MARTIN 1250 <2000

BLAKE 2850 >2000

CLARK 2450 >2000

SCOTT 3000 >2000

KING 5000 >2000

TURNER 1500 <2000

ADAMS 1100 <2000

JAMES 950 <2000

FORD 3000 >2000

MILLER 1300 <2000

decode函数用于行转列的转换

SQL> select to_char(hiredate,'yyyy') from emp;

TO_C

----
1980

1981

1987

1981

TO_C

----

1987

1981

1982

15 rows selected.

SQL> select to_char(hiredate,'yyyy'),count(*) from emp group by to_char(hiredate,'yyyy');

TO_C COUNT(*)

---- ----------

1987 2

1980 1

1982 1

1981 10

SQL> select sum(decode(to_char(hiredate,'yyyy'),'1980',1))"1980",sum(decode(to_char(hiredate,'yyyy'),'1981',1))"1981",
sum(decode(to_char(hiredate,'yyyy'),'1987',1))"1987",sum(decode(to_char(hiredate,'yyyy'),'1982',1))"1982" from emp;

1980 1981 1987 1982

---------- ---------- ---------- ----------

1 10 2 1