oracle case when 用法

原文:Oracle CASE WHEN 用法介绍

1. CASE WHEN 表达式有两种形式

--简单Case函数  




CASE sex  


WHEN '1' THEN '男'  


WHEN '2' THEN '女'  


ELSE '其他' END  






--Case搜索函数  




CASE


WHEN sex = '1' THEN '男'  


WHEN sex = '2' THEN '女'  


ELSE '其他' END  


 


2. CASE WHEN 在语句中不同位置的用法


2.1 SELECT CASE WHEN 用法




SELECT   grade, COUNT (CASE WHEN sex = 1 THEN 1      


                       ELSE NULL


                       END) 男生数,


                COUNT (CASE WHEN sex = 2 THEN 1


                       ELSE NULL


                       END) 女生数


    FROM students GROUP BY grade;


 


2.3 WHERE CASE WHEN 用法




SELECT T2.*, T1.*


   FROM T1, T2


  WHERE (CASE WHEN T2.COMPARE_TYPE = 'A' AND


                   T1.SOME_TYPE LIKE 'NOTHING%'


                THEN 1


              WHEN T2.COMPARE_TYPE != 'A' AND


                   T1.SOME_TYPE NOT LIKE 'NOTHING%'


                THEN 1


              ELSE 0


           END) = 1


 


2.4 GROUP BY CASE WHEN 用法




SELECT  


CASE WHEN salary <= 500 THEN '1'  


WHEN salary > 500 AND salary <= 600  THEN '2'  


WHEN salary > 600 AND salary <= 800  THEN '3'  


WHEN salary > 800 AND salary <= 1000 THEN '4'  


ELSE NULL END salary_class, -- 别名命名
COUNT(*)  


FROM    Table_A  


GROUP BY  


CASE WHEN salary <= 500 THEN '1'  


WHEN salary > 500 AND salary <= 600  THEN '2'  


WHEN salary > 600 AND salary <= 800  THEN '3'  


WHEN salary > 800 AND salary <= 1000 THEN '4'  


ELSE NULL END;