Mysql查询时case when语句的使用

前言

前几天在技术论坛论坛上发现一个求助帖，大体意思就是要把一个表中的数据按条件分成两类，每一类排序方式不同，然后整体作为查询的结果集，乍一看这问题不是很难，很多人给出的答案是分别查询排序后再 union合并到一起，但是后来楼主明确指出不想使用 union 操作，这时有一位高人巧用 case when 语句解决了问题，其实这是我第一次接触 case when 语句，于是查询了一下具体用法，在此做个小结，方便日后查询使用。

创建示例表格

数据库表格结构很简单，马上要期末了，就以学习成绩为数据来建立一张数据表，表中包含唯一ID、学号、姓名、性别、分数等列，其中性别这一列用整数代表，0表示男，1表示女，建立表格的sql语句如下：

CREATE TABLE `grade` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `number` int(4) NOT NULL DEFAULT '0',
  `name` varbinary(32) NOT NULL DEFAULT '',
  `sex` int(4) NOT NULL DEFAULT '0',
  `score` int(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `find_index` (`number`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=binary ROW_FORMAT=DYNAMIC;

插入测试数据

表格建立完成可以使用可视化工具或者insert语句插入测试数据，插入测试数据后查询结果如下：

mysql> select number,name,sex,score from grade;
+----------+----------+-----+-------+
| number   | name     | sex | score |
+----------+----------+-----+-------+
| 20180001 | xiaoming |   0 |    68 |
| 20180002 | xiaohong |   1 |    98 |
| 20180003 | xiaobing |   0 |    78 |
| 20180004 | xiaoli   |   0 |    88 |
| 20180005 | zhangsan |   0 |    32 |
| 20180006 | zhaosi   |   0 |    58 |
| 20180007 | marry    |   1 |    78 |
| 20180008 | tom      |   0 |   100 |
| 20180009 | feifei   |   1 |    90 |
| 20180010 | lili     |   1 |    92 |
| 20180011 | xiaozhao |   0 |    52 |
| 20180012 | xiaowang |   0 |    62 |
+----------+----------+-----+-------+
12 rows in set (0.00 sec)

获取平均成绩

班主任们坐在一起做喜欢做的事就是比一下自己的学生和别人班的差距，谁让他们每个人带的学生都是一届不如一届呢！（你们是我带过的学生中最差的一届！！！）说到比成绩一般都是比较并均分，sql语句可能会写成下面这样：

mysql> select avg(score) as 平均分 from grade;
+-----------+
| 平均分    |
+-----------+
|   74.6667 |
+-----------+
1 row in set (0.02 sec)

是的，很简单就能获得班级的平均分，如果要分组呢？比如分别查一下男生和女生的平均分，因为我们知道表中的sex表示性别，所以直接按照sex分组就可以实现，可以将语句简单写成这样：

mysql> select sex as 性别, avg(score) as 平均分 from grade group by sex;
+--------+-----------+
| 性别   | 平均分    |
+--------+-----------+
|      0 |   67.2500 |
|      1 |   89.5000 |
+--------+-----------+
2 rows in set (0.00 sec)

是不是很简单？可是性别显示成0和1确实不利于阅读，但是表中又没有保存0、1与男、女的对应关系，应该怎么办呢？这就要用到我们今天所要用到的case when语句了，语法上共有两种写法，看着具体例子体会一下吧。

case when 语句的使用

1. 第一种用法：case后面跟列名，when后面跟对应值

   CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
   END

   这种用法正好解决我们刚刚提出的问题，当sex值为0时当前列显示“男”，否则显示“女”，sql写法如下：

   mysql> select (case sex when 0 then '男' else '女' end) as 性别, avg(score) as 平均分
    -> from grade group by sex;
   +--------+-----------+
   | 性别   | 平均分    |
   +--------+-----------+
   | 男     |   67.2500 |
   | 女     |   89.5000 |
   +--------+-----------+
   2 rows in set (0.00 sec)

2. 第二种用法：case后面空白，when后面跟着判断条件

   CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
   END

   针对于这种写法，我们考虑这样一种需求，学生成绩是有评分的，大于等于90分的学生是A，小于90分大于等于60分的学生是B， 其余的学生是C，现在要查询评分为A、B、C的学生成绩的平均分分别是多少，因为成绩评分并不是单独的一列，所以不能简单的 使用 group by 来分组实现了，但是可以利用 case when 语句实现，写起来也很简单，看看下面的sql语句就知道了！

   mysql> select (case when score >= 90 then 'A' when score < 60 then 'C' else 'B' end) as 等级,
    -> avg(score) as 平均分 from grade group by 等级;
   +--------+-----------+
   | 等级   | 平均分    |
   +--------+-----------+
   | A      |   95.0000 |
   | B      |   74.8000 |
   | C      |   47.3333 |
   +--------+-----------+
   3 rows in set (0.00 sec)

总结

1. case when 语句共有两种写法，使用时要区别两种用法的差异。
2. 使用 case when 语句可以实现修改数值的对应关系，还可以按照复杂的条件进行分组。
3. 关于 case when 语句的详细用法，有兴趣的同学可以参考一下官方文档：13.6.5.1 CASE Syntax