case when oracle

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

1.1 SELECT CASE WHEN 用法

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;

 

1.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

 

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;  

1.4  简单 CASE WHEN 用法

select u.id,u.name,u.sex,

(case u.sex

when 1 then '男'

when 2 then '女'

else '空的'

end

)性别

from uaers

u;