MySQL知识汇总：MySQL函数CASE WHEN用法详解

CASE 
   season
   WHEN  'Spring'  THEN '春天' 
   WHEN  'Summer'  THEN '夏天' 
   WHEN  'autumn'  THEN '秋天' 
   else  '冬天'
end

CASE WHEN season= 'Spring' THEN '春天'
     WHEN season= 'Summer' THEN '夏天'
     WHEN season= 'autumn' THEN '秋天' 
     else '冬天' 
     END

SELECT
    age,
    (CASE WHEN age >=0 AND age< 60 THEN '上班'
          WHEN age >= 60 AND age < 80 THEN '退休'
          WHEN age >= 80 THEN '死亡'
          ELSE '非人类' 
          END) AS target
FROM
  person

SELECT
    max(age),
    (CASE WHEN age >=0 AND age< 60 THEN '上班'
          WHEN age >= 60 AND age < 80 THEN '退休'
          WHEN age >= 80 THEN '死亡'
          ELSE '非人类' 
          END) AS target
FROM
  person group by 
      CASE WHEN age >=0 AND age< 60 THEN '上班'
          WHEN age >= 60 AND age < 80 THEN '退休'
          WHEN age >= 80 THEN '死亡'
          ELSE '非人类' 
          END

SELECT 
  E_CODE,
  SUM(CASE WHEN E_TYPE = 0 THEN E_VALUE ELSE 0 END) AS WATER_ENERGY,--水耗
  SUM(CASE WHEN E_TYPE = 1 THEN E_VALUE ELSE 0 END) AS ELE_ENERGY,--电耗
  SUM(CASE WHEN E_TYPE = 2 THEN E_VALUE ELSE 0 END) AS HEAT_ENERGY--热耗
FROM 
  THTF_ENERGY_TEST
GROUP BY
  E_CODE

国家（country）    人口（population）
中国    600
美国    100
加拿大    100
英国    200
法国    300
日本    250
德国    200
墨西哥    50
印度    250

SELECT  SUM(population),
        CASE country
                WHEN '中国'     THEN '亚洲'
                WHEN '印度'     THEN '亚洲'
                WHEN '日本'     THEN '亚洲'
                WHEN '美国'     THEN '北美洲'
                WHEN '加拿大'  THEN '北美洲'
                WHEN '墨西哥'  THEN '北美洲'
        ELSE '其他' END
FROM    Table_A
GROUP BY CASE country
                WHEN '中国'     THEN '亚洲'
                WHEN '印度'     THEN '亚洲'
                WHEN '日本'     THEN '亚洲'
                WHEN '美国'     THEN '北美洲'
                WHEN '加拿大'  THEN '北美洲'
                WHEN '墨西哥'  THEN '北美洲'
        ELSE '其他' END;

CASE country
                WHEN '中国'     THEN '亚洲'
                WHEN '印度'     THEN '亚洲'
                WHEN '日本'     THEN '亚洲'
                WHEN '美国'     THEN '北美洲'
                WHEN '加拿大'  THEN '北美洲'
                WHEN '墨西哥'  THEN '北美洲'
        ELSE '其他' END

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;