DECODE函数跟CASE WHEN 比较-数据库教程-爱易网页

DECODE函数跟CASE WHEN 比较

日期：2014-05-16　浏览次数：20384 次

DECODE函数和CASE WHEN 比较

一，DECODE函数

其基本语法为：

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函数联用比较大小：

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

select decode(sign(3-5),1 ,3, 5) from dual
?注：sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1

2. 表、视图结构转化：

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

二，CASE WHEN

其语法如下：

SELECT <myColumnSpec> = 
　　CASE WHEN <A> THEN <somethingA> 
　　　　　WHEN <B> THEN <somethingB> 
　　　　　ELSE <somethingE> END

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

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

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'

免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。