CASE语句的一些进阶用法

CASE的形式

• 简单CASE

CASE sex 
    WHEN '1' THEN '男' 
    WHEN '2' THEN '女' 
ELSE '其他' END 

• CASE搜索函数

CASE WHEN sex = '1' THEN '男' 
     WHEN sex = '2' THEN '女' 
ELSE '其他' END 

已知数据按照另外一种方式进行分组，分析

有如下数据

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

希望得到如下结果

洲 人口 
亚洲 1100 
北美洲 250 
其他 700 

MySQL语句

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; 

用一个SQL语句完成不同条件的分组

有如下数据

country sex population
中国 1 340 
中国 2 260 
美国 1 45 
美国 2 55 
加拿大 1 51 
加拿大 2 49 
英国 1 40 
英国 2 60 

希望得到如下结果

国家 男 女 
中国 340 260 
美国 45 55 
加拿大 51 49 
英国 40 60 

MySQL语句

SELECT country, 
       SUM( CASE WHEN sex = '1' THEN 
                      population ELSE 0 END),  --男性人口 
       SUM( CASE WHEN sex = '2' THEN 
                      population ELSE 0 END)   --女性人口 
FROM  Table_A 
GROUP BY country; 

根据条件有选择的UPDATE

例，有如下更新条件
工资5000以上的职员，工资减少10%
工资在2000到4600之间的职员，工资增加15%

• 如果使用两个Update语句，则由于步骤一执行后可能干扰步骤二导致错误

UPDATE Personnel 
SET salary = CASE WHEN salary >= 5000 
　            THEN salary * 0.9 
WHEN salary >= 2000 AND salary < 4600 
THEN salary * 1.15 
ELSE salary END; 

这里要注意一点，最后一行的ELSE salary是必需的，要是没有这行，不符合这两个条件的人的工资将会被写成NUll

在Case函数中使用聚合函数

有下列数据
有的学生选择了同时修几门课程(100,200)也有的学生只选择了一门课程(300,400,500)。选修多门课程的学生，要选择一门课程作为主修，主修flag里面写入 Y。只选择一门课程的学生，主修flag为N

学号(std_id) 课程ID(class_id) 课程名(class_name) 主修flag（main_class_flg) 
100 1 经济学 Y 
100 2 历史学 N 
200 2 历史学 N 
200 3 考古学 Y 
200 4 计算机 N 
300 4 计算机 N 
400 5 化学 N 
500 6 数学 N 

现在我们要按照下面两个条件对这个表进行查询

• 只选修一门课程的人，返回那门课程的ID
• 选修多门课程的人，返回所选的主课程ID

只使用一条语句

SELECT  std_id, 
CASE WHEN COUNT(*) = 1  --只选择一门课程的学生的情况 
THEN MAX(class_id) 
ELSE MAX(CASE WHEN main_class_flg = 'Y'   --NULL值会被MAX忽略
THEN class_id END 
) 
END AS main_class 
FROM Studentclass 
GROUP BY std_id; 

参考

mysql操作查询结果case when then else end用法举例