Oracle 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  

SELECT   grade, COUNT (CASE WHEN sex = 1 THEN 1      /*sex 1为男生，2位女生*/ 
                       ELSE NULL 
                       END) 男生数, 
                COUNT (CASE WHEN sex = 2 THEN 1 
                       ELSE NULL 
                       END) 女生数 
    FROM students GROUP BY grade;

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

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;  

select decode(sex, 'M', 'Male', 'F', 'Female', 'Unknown') 
from   employees;

SELECT T2.*, T1.* 
   FROM T1, T2 
  WHERE (T2.COMPARE_TYPE = 'A' AND T1.SOME_TYPE LIKE 'NOTHING%') 
         OR 
        (T2.COMPARE_TYPE != 'A' AND T1.SOME_TYPE NOT LIKE 'NOTHING%')