MySQL——case函数的简单用法

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

案例：sum和case函数相结合

假如数据量很大约1000万条，用一个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;