欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页

oracle 学习笔记 - DECODE函数和CASE WHEN 比较

程序员文章站 2022-07-05 14:49:27
...

一，DECODE函数

 

其基本语法为：
Sql代码  oracle 学习笔记 - DECODE函数和CASE WHEN 比较oracle 学习笔记 - DECODE函数和CASE WHEN 比较oracle 学习笔记 - DECODE函数和CASE WHEN 比较oracle 学习笔记 - DECODE函数和CASE WHEN 比较
  1. DECODE(value, if1, then1, if2, then2, if3, then3,…ifn, thenn, else)  
oracle 学习笔记 - DECODE函数和CASE WHEN 比较
[sql] view plain copy print?
  1. DECODE(value, if1, then1, if2, then2, if3, then3,…ifn, thenn, else)  
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代码  oracle 学习笔记 - DECODE函数和CASE WHEN 比较oracle 学习笔记 - DECODE函数和CASE WHEN 比较oracle 学习笔记 - DECODE函数和CASE WHEN 比较oracle 学习笔记 - DECODE函数和CASE WHEN 比较
  1. select decode(sign(arg1-arg2),-1, arg1, arg2) from dual; –get arg1与arg2的较小值  
oracle 学习笔记 - DECODE函数和CASE WHEN 比较
[sql] view plain copy print?
  1. select decode(sign(arg1-arg2),-1, arg1, arg2) from dual; –get arg1与arg2的较小值  
select decode(sign(arg1-arg2),-1, arg1, arg2) from dual; --get arg1与arg2的较小值
Sql代码  oracle 学习笔记 - DECODE函数和CASE WHEN 比较oracle 学习笔记 - DECODE函数和CASE WHEN 比较oracle 学习笔记 - DECODE函数和CASE WHEN 比较oracle 学习笔记 - DECODE函数和CASE WHEN 比较
  1. select decode(sign(3-5),1 ,3, 5) from dual  
oracle 学习笔记 - DECODE函数和CASE WHEN 比较
[sql] view plain copy print?
  1. select decode(sign(3-5),1 ,3, 5) from dual  
select decode(sign(3-5),1 ,3, 5) from dual
 注：sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1
2. 表、视图结构转化：
基本思路：使用substrb函数实现对字段的判断，然后用decode函数对数据进行重新计算，并生成新的数据和构成新的表(table or view)。

二，CASE WHEN

其语法如下：

Sql代码  oracle 学习笔记 - DECODE函数和CASE WHEN 比较oracle 学习笔记 - DECODE函数和CASE WHEN 比较oracle 学习笔记 - DECODE函数和CASE WHEN 比较oracle 学习笔记 - DECODE函数和CASE WHEN 比较
  1. SELECT <myColumnSpec> =   
  2. 　　CASE WHEN <A> THEN <somethingA>   
  3. 　　　　　WHEN <B> THEN <somethingB>   
  4. 　　　　　ELSE <somethingE> END  
  5.    
oracle 学习笔记 - DECODE函数和CASE WHEN 比较
[sql] view plain copy print?
  1. SELECT <myColumnSpec> =   
  2. 　　CASE WHEN <A> THEN <somethingA>   
  3. 　　　　　WHEN <B> THEN <somethingB>   
  4. 　　　　　ELSE <somethingE> END  
  5.    
SELECT <myColumnSpec> = 
　　CASE WHEN <A> THEN <somethingA> 
　　　　　WHEN <B> THEN <somethingB> 
　　　　　ELSE <somethingE> END

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

Sql代码  oracle 学习笔记 - DECODE函数和CASE WHEN 比较oracle 学习笔记 - DECODE函数和CASE WHEN 比较oracle 学习笔记 - DECODE函数和CASE WHEN 比较oracle 学习笔记 - DECODE函数和CASE WHEN 比较
  1. SELECT     CASE           
  2.             WHEN price IS NULL THEN ‘Unpriced’           
  3.             WHEN price < 10 THEN ‘Bargain’           
  4.             WHEN price BETWEEN 10 and 20 THEN ‘Average’           
  5.             ELSE ‘Gift to impress relatives’       
  6.            END AS “Range”,       
  7.            Title   
  8. FROM titles   
  9. where   
  10.     CASE           
  11.                 WHEN price IS NULL THEN ‘Unpriced’           
  12.                 WHEN price < 10 THEN ‘Bargain’           
  13.                 WHEN price BETWEEN 10 and 20 THEN ‘Average’           
  14.                 ELSE ‘Gift to impress relatives’     END in(‘Average’,‘Bargain’)  
  15. GROUP BY     CASE           
  16.                 WHEN price IS NULL THEN ‘Unpriced’           
  17.                 WHEN price < 10 THEN ‘Bargain’           
  18.                 WHEN price BETWEEN 10 and 20 THEN ‘Average’           
  19.                 ELSE ‘Gift to impress relatives’     END,       
  20.                 Title   
  21. ORDER BY     CASE           
  22.                 WHEN price IS NULL THEN ‘Unpriced’           
  23.                 WHEN price < 10 THEN ‘Bargain’           
  24.                 WHEN price BETWEEN 10 and 20 THEN ‘Average’           
  25.                 ELSE ‘Gift to impress relatives’       
  26.                 END,Title   
oracle 学习笔记 - DECODE函数和CASE WHEN 比较
[sql] view plain copy print?
  1. SELECT     CASE           
  2.             WHEN price IS NULL THEN ‘Unpriced’           
  3.             WHEN price < 10 THEN ‘Bargain’           
  4.             WHEN price BETWEEN 10 and 20 THEN ‘Average’           
  5.             ELSE ‘Gift to impress relatives’       
  6.            END AS “Range”,       
  7.            Title   
  8. FROM titles   
  9. where   
  10.     CASE           
  11.                 WHEN price IS NULL THEN ‘Unpriced’           
  12.                 WHEN price < 10 THEN ‘Bargain’           
  13.                 WHEN price BETWEEN 10 and 20 THEN ‘Average’           
  14.                 ELSE ‘Gift to impress relatives’     END in(‘Average’,‘Bargain’)  
  15. GROUP BY     CASE           
  16.                 WHEN price IS NULL THEN ‘Unpriced’           
  17.                 WHEN price < 10 THEN ‘Bargain’           
  18.                 WHEN price BETWEEN 10 and 20 THEN ‘Average’           
  19.                 ELSE ‘Gift to impress relatives’     END,       
  20.                 Title   
  21. ORDER BY     CASE           
  22.                 WHEN price IS NULL THEN ‘Unpriced’           
  23.                 WHEN price < 10 THEN ‘Bargain’           
  24.                 WHEN price BETWEEN 10 and 20 THEN ‘Average’           
  25.                 ELSE ‘Gift to impress relatives’       
  26.                 END,Title   
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代码  oracle 学习笔记 - DECODE函数和CASE WHEN 比较oracle 学习笔记 - DECODE函数和CASE WHEN 比较oracle 学习笔记 - DECODE函数和CASE WHEN 比较oracle 学习笔记 - DECODE函数和CASE WHEN 比较
  1. select name,  
  2.        CASE           
  3.         WHEN rma_center IS NULL THEN ‘Null’           
  4.         WHEN rma_center > 1000 THEN ‘>1000’           
  5.         WHEN rma_center BETWEEN 30 and 100 THEN ‘30~100’           
  6.         ELSE ‘Gift to impress relatives’     END  AS “RMA CENTER Type”                      
  7. from rm_site_master   
  8. where  CASE           
  9.         WHEN rma_center IS NULL THEN ‘Null’           
  10.         WHEN rma_center > 1000 THEN ‘>1000’           
  11.         WHEN rma_center BETWEEN 30 and 100 THEN ‘30~100’           
  12.         ELSE ‘Gift to impress relatives’     END in(‘30~100’,‘>1000’)  
  13. group by CASE           
  14.         WHEN rma_center IS NULL THEN ‘Null’           
  15.         WHEN rma_center > 1000 THEN ‘>1000’           
  16.         WHEN rma_center BETWEEN 30 and 100 THEN ‘30~100’           
  17.         ELSE ‘Gift to impress relatives’     END,  
  18.         name  
  19. order by CASE           
  20.         WHEN rma_center IS NULL THEN ‘Null’           
  21.         WHEN rma_center > 1000 THEN ‘>1000’           
  22.         WHEN rma_center BETWEEN 30 and 100 THEN ‘30~100’           
  23.         ELSE ‘Gift to impress relatives’     END,  
  24.         name  
oracle 学习笔记 - DECODE函数和CASE WHEN 比较
[sql] view plain copy print?
  1. select name,  
  2.        CASE           
  3.         WHEN rma_center IS NULL THEN ‘Null’           
  4.         WHEN rma_center > 1000 THEN ‘>1000’           
  5.         WHEN rma_center BETWEEN 30 and 100 THEN ‘30~100’           
  6.         ELSE ‘Gift to impress relatives’     END  AS “RMA CENTER Type”                      
  7. from rm_site_master   
  8. where  CASE           
  9.         WHEN rma_center IS NULL THEN ‘Null’           
  10.         WHEN rma_center > 1000 THEN ‘>1000’           
  11.         WHEN rma_center BETWEEN 30 and 100 THEN ‘30~100’           
  12.         ELSE ‘Gift to impress relatives’     END in(‘30~100’,‘>1000’)  
  13. group by CASE           
  14.         WHEN rma_center IS NULL THEN ‘Null’           
  15.         WHEN rma_center > 1000 THEN ‘>1000’           
  16.         WHEN rma_center BETWEEN 30 and 100 THEN ‘30~100’           
  17.         ELSE ‘Gift to impress relatives’     END,  
  18.         name  
  19. order by CASE           
  20.         WHEN rma_center IS NULL THEN ‘Null’           
  21.         WHEN rma_center > 1000 THEN ‘>1000’           
  22.         WHEN rma_center BETWEEN 30 and 100 THEN ‘30~100’           
  23.         ELSE ‘Gift to impress relatives’     END,  
  24.         name  
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 的比较

　１，DECODE  Oracle 特有;
　２，CASE WHEN  oracle ,  SQL Server,  MySQL 都可用;
　３，DECODE 只能用做相等判断,但是可以配合sign函数进行大于，小于，等于的判断,CASE 　可用于=,>=,<,<=,<>,is null,is not null，between … and … 等的判断;

　４，CASE when 可以做等值判断，也可以带入表达式判断，更为灵活; 
SQL> select case instr(hiredate,'82')
  2  when 0 then 0
  3  else 1
  4  end as "year"
  5  from emp;

      year
----------
         0
         0
         0
         0
         0
         0
         0
         0
         0
         0
         0
         0
         0
         1
SQL> select count(*) total,
  2  sum(case
  3  when instr(hiredate,'80')=0 then 0
  4  else 1
  5  end
  6  ) as "1980",
  7  sum(case
  8  when instr(hiredate,'81')=0 then 0
  9  else 1
 10  end
 11  ) as "1981",
 12  sum(case
 13  when instr(hiredate,'82')=0 then 0
 14  else 1
 15  end
 16  ) as "1982",
 17  sum(case
 18  when instr(hiredate,'87')=0 then 0
 19  else 1
 20  end
 21  ) as "1987"
 22  from emp;

     TOTAL       1980       1981       1982       1987
---------- ---------- ---------- ---------- ----------
        14          1         10          1          2

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

转自：http://blog.csdn.net/nosodeep/article/details/21232023

相关标签： decode

上一篇： vi查找替换命令详解

下一篇： Java客户端API

推荐阅读