case when和decode的区别

一，DECODE函数

其基本语法为：

Sql代码

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

表示如果value等于if1时，DECODE函数的结果返回then1,...,如果不等于任何一个if值，则返回else。亦即：decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)

延伸用法：

1. 与sign函数联用比较大小：

Sql代码

select decode(sign(arg1-arg2),-1, arg1, arg2) from dual--get arg1与arg2的较小值

Sql代码

select decode(sign(3-5),1 ,3, 5) from dual

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

2. 表、视图结构转化：

基本思路：使用substrb函数实现对字段的判断，然后用decode函数对数据进行重新计算，并生成新的数据和构成新的表(table or view)。

二，CASE WHEN

其语法如下：

Sql代码

SELECT <myColumnSpec>=

CASE WHEN <A>THEN <somethingA>

　WHEN <B>THEN <somethingB>

　ELSE <somethingE>END

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

Sql代码

SELECT CASE

WHEN price IS NULL THEN 'Unpriced'

WHEN price <10 THEN 'Bargain'

WHEN price BETWEEN 10 and 20 THEN 'Average'

ELSE 'Gift to impress relatives'

END AS "Range",

Title

FROM titles

where

CASE

WHEN price IS NULL THEN 'Unpriced'

WHEN price <10 THEN 'Bargain'

WHEN price BETWEEN 10 and 20 THEN 'Average'

ELSE 'Gift to impress relatives' END in('Average','Bargain')

GROUP BY CASE

WHEN price IS NULL THEN 'Unpriced'

WHEN price <10 THEN 'Bargain'

WHEN price BETWEEN 10 and 20 THEN 'Average'

ELSE 'Gift to impress relatives' END,

Title

ORDER BY CASE

WHEN price IS NULL THEN 'Unpriced'

WHEN price <10 THEN 'Bargain'

WHEN price BETWEEN 10 and 20 THEN 'Average'

ELSE 'Gift to impress relatives'

END,Title

rm_site_master 表结构：

rma_center | name

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

123 |qw

23|ASde

45|sssdf

55|e3fbg

55555 |adfv

22221 |sdfsfe

4 |sdfeg

579 |lojgdex

Sql代码

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 'Gift to impress relatives' END AS "RMA CENTER Type"

from rm_site_master

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 'Gift to impress relatives' END in('30~100','>1000')

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 'Gift to impress relatives' 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 'Gift to impress relatives' END,

name

三，DECODE 与CASE WHEN 的比较

　1，DECODE Oracle 特有

　2，CASE WHEN Oracle , SQL Server, MySQL 都可用

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

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

substr是按字符来计算，一个字母或汉字都按一个字符计算如：

substr('智能ABC',2,2)='能A'

如果想要按字节来计算则可以采用substrb函数，用法一样

substrb('智能ABC',3,4)='能AB'

当然还有另外几个按不同编码计算的函数

substrc:按Unicode编码,

substr2:按UCS2编码,

substr4:按UCS4编码。

---