Oracle之Case的用法

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

CASE WHEN在语句中不同位置的用法
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;


select 姓名, 年份, 
sum(case when 科目 = '语文' then 成绩 else 0 end) 语文,
sum(case when 科目 = '数学' then 成绩 else 0 end) 数学,
sum(成绩) 总分
from t
group by 姓名, 年份

2：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

3：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;