一、MySQL case when的三种用法:


 

1.case 字段 when, 字段的具体值;

 

select a.*,
    case sex
        when '1' then '男'
        else '女'
    end as '性别'
FROM table_sex a;

 

2.case when 字段,可对字段进行取值范围设置;

SELECT a.*,
    CASE
        WHEN a.age BETWEEN 0 and 20 THEN '青年'
        WHEN a.age BETWEEN 20 and 40 THEN '中年'
        ELSE '非人类'
    END AS '描述'
FROM table_c a;

3.case when 字段1,字段2,可对多个字段进行取值映射;

SELECT a.*,
    CASE
        WHEN a.age BETWEEN 20 and 80 THEN '青年'
        WHEN a.name ='流浪' THEN '帅气'
    END AS '描述'
FROM table_cc a

注:如果两个条件都针对一个字段的话,会显示第一个when的值。 


 

二、具体解释:

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',则返回值'女' 否则返回'其他’

 三、例子

有员工表如下:

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 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",
    SUM(CASE WHEN fsalary < 9999 AND fage < 35 THEN 1 ELSE 0 END) 
        AS "fsalary<9999_fage<35"
FROM empinfo;

 


END 2018-05-16 14:30:48