MySql 中 case when then else end 的用法

解释：

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

---用法一：
SELECT 
            CASE WHEN STATE = '1' THEN '成功' 
                 WHEN STATE = '2' THEN '失败'
            ELSE '其他' END  
            FROM  SYS_SCHEDULER
---用法二：    
SELECT STATE
            CASE WHEN '1' THEN '成功' 
                 WHEN '2' THEN '失败'
            ELSE '其他' END  

            FROM  SYS_SCHEDULER

实例：

统计计算各种组合的累计数量

SELECT 
SUM(case when usscombotype='0' and signedoptiontype = '1' then 1 else 0  end ) as sicTypeSbaign,
SUM(case when usscombotype='0' and signedoptiontype = '-1' then 1 else 0  end ) as basicTypeSurrender,
SUM(case when usscombotype='0' and focusgroupstype = '' then 1 else 0  end ) as basicTypePopulation,
SUM(case when usscombotype='0' and focusgroupstype != '' then 1 else 0  end ) as basicTypeFocus
from report_doctor_sign_detail

例子2：

有员工表empinfo 
( 
Fempno varchar2(10) not null pk, 
Fempname varchar2(20) not null, 
Fage number not null, 
Fsalary number not null 
); 
假如数据量很大约1000万条；写一个你认为最高效的SQL，用一个SQL计算以下四种人： 
fsalary>9999 and fage > 35 
fsalary>9999 and fage < 35 
fsalary <9999 and fage > 35 
fsalary <9999 and fage < 35 
每种员工的数量； 
select sum(case when fsalary > 9999 and fage > 35
then 1
else 0end) as "fsalary>9999_fage>35",
sum(case when fsalary > 9999 and fage < 35
then 1
else 0
end) as "fsalary>9999_fage<35",
sum(case when fsalary < 9999 and fage > 35
then 1
else 0
end) as "fsalary<9999_fage>35",
sum(case when fsalary < 9999 and fage < 35
then 1
else 0
end) as "fsalary<9999_fage<35"
from empinfo;