解释：

1 SELECT 2 case -------------如果3 when sex=‘1‘ then ‘男‘ -------------sex=‘1‘，则返回值‘男‘4 when sex=‘2‘ then ‘女‘ -------------sex=‘2‘，则返回值‘女‘ 5 else ‘其他‘ -------------其他的返回‘其他’6 end -------------结束7 from sys_user --------整体理解： 在sys_user表中如果sex=‘1‘，则返回值‘男‘如果sex=‘2‘，则返回值‘女‘ 否则返回‘其他’

--- 用法一：

1 SELECT 2 CASE WHEN STATE = ‘1‘ THEN ‘成功‘ 3 WHEN STATE = ‘2‘ THEN ‘失败‘4 ELSE ‘其他‘ END 5 FROM SYS_SCHEDULER

---用法二：

1 SELECT STATE2 CASE WHEN ‘1‘ THEN ‘成功‘ 3 WHEN ‘2‘ THEN ‘失败‘4 ELSE ‘其他‘ END 5 FROM SYS_SCHEDULER

例子：

 1 有员工表empinfo  2 (  3 Fempno varchar2(10) not null pk,  4 Fempname varchar2(20) not null,  5 Fage number not null,  6 Fsalary number not null  7 );  8 假如数据量很大约1000万条；写一个你认为最高效的SQL，用一个SQL计算以下四种人：  9 fsalary>9999 and fage > 35 10 fsalary>9999 and fage < 35 11 fsalary <9999 and fage > 35 12 fsalary <9999 and fage < 35 13 每种员工的数量； 14 select sum(case when fsalary > 9999 and fage > 3515 then 116 else 0end) as "fsalary>9999_fage>35",17 sum(case when fsalary > 9999 and fage < 3518 then 119 else 020 end) as "fsalary>9999_fage<35",21 sum(case when fsalary < 9999 and fage > 3522 then 123 else 024 end) as "fsalary<9999_fage>35",25 sum(case when fsalary < 9999 and fage < 3526 then 127 else 028 end) as "fsalary<9999_fage<35"29 from empinfo;

View Code