SQL 中case when ...then...else ...end用法

CASE WHEN 基本概念

case是一个行级的语法，相当于对于每一行满足条件的数据，等到一个新的结果，也是新的一列。

简单Case函数：
CASE sex
WHEN ‘1’ THEN ‘男’
WHEN ‘0’ THEN ‘女’
ELSE ‘其他’ END as sex_cn

解释：每一行sex字段的值为1得出一个结果‘男’否则得到‘其他’，会另起一列，命名为sex_cn，如果没有as就会使用这一段case语句命名

Case搜索函数：
CASE WHEN sex = ‘1’ THEN ‘男’
WHEN sex = ‘0’ THEN ‘女’
ELSE ‘其他’ END as sex_cn

解释：每一行sex字段的值为1得出一个结果‘男’，会另起一列，命名为sex_cn，如果没有as就会使用这一段case语句命名

在使用 CASE WHEN时，可以把它当作一个没有字段名的字段，字段值根据条件确认，在需要使用字段名时可以是用 as来定义别名。

案例1

1. 创建表

create table people( country varchar(255), population bigint);
insert into people values('中国',600);
insert into people values('美国',100);
insert into people values('加拿大',100);
insert into people values('英国',200);
insert into people values('法国',300);
insert into people values('日本',250);
insert into people values('德国',200);
insert into people values('墨西哥',50);
insert into people values('印度',250);

mysql> select * from people;
+-----------+------------+
| country   | population |
+-----------+------------+
| 美国      |        100 |
| 加拿大    |        100 |
| 英国      |        200 |
| 法国      |        300 |
| 日本      |        250 |
| 德国      |        200 |
| 墨西哥    |         50 |
| 印度      |        250 |
| 中国      |        600 |
+-----------+------------+

2. 将country中文转化为拼音

mysql> SELECT  
    -> CASE country
    ->     WHEN '中国'     THEN 'cn'
    ->     WHEN '印度'     THEN 'ind'
    ->     WHEN '日本'     THEN 'jp'
    ->     WHEN '美国'     THEN 'ua'
    ->     WHEN '加拿大'  THEN 'cad'
    ->     WHEN '墨西哥'  THEN 'mex'
    ->     ELSE '$$$' END as 'country_en'
    -> FROM people;
+------------+
| country_en |
+------------+
| ua         |
| cad        |
| $$$        |
| $$$        |
| jp         |
| $$$        |
| mex        |
| ind        |
| cn         |
+------------+
9 rows in set (0.00 sec)


这个案例就很明显，另起一列，对每一行数据when进行判断，then进行重新赋值，然后另起一列放入then或者else之后的值。

3. 根据这个国家人口数据，统计亚洲和北美洲的人口数量。使用如下 SQL：

SELECT  
	CASE country
	    WHEN '中国'     THEN '亚洲'
	    WHEN '印度'     THEN '亚洲'
	    WHEN '日本'     THEN '亚洲'
	    WHEN '美国'     THEN '北美洲'
	    WHEN '加拿大'  THEN '北美洲'
	    WHEN '墨西哥'  THEN '北美洲'
    ELSE '其他' END as '洲' , 
    SUM(population) as '人口'
FROM people
GROUP BY 
	CASE country
	    WHEN '中国'     THEN '亚洲'
	    WHEN '印度'     THEN '亚洲'
	    WHEN '日本'     THEN '亚洲'
	    WHEN '美国'     THEN '北美洲'
	    WHEN '加拿大'  THEN '北美洲'
	    WHEN '墨西哥'  THEN '北美洲'
    ELSE '其他' END ;
+-----------+--------+
| 洲        | 人口   |
+-----------+--------+
| 亚洲      |   1100 |
| 其他      |    700 |
| 北美洲    |    250 |
+-----------+--------+

解释：group by之后case也会when...then...新增一列数据，然后根据这个新增一列进行分组，中间会出现的一个中间表。

这里的两个CASE WHEN都相当于一个字段，不过值得一提的是，第二个CASE WHEN 的THEN值并不用写明是什么洲，它只是用于将记录进行分组，所以THEN后面的值只有能区分这三种记录就行，GROUP BY也可以写成：

GROUP BY CASE country
    WHEN '中国'     THEN 0
    WHEN '印度'     THEN 0
    WHEN '日本'     THEN 0
    WHEN '美国'     THEN 1
    WHEN '加拿大'  THEN 1
    WHEN '墨西哥'  THEN 1
    ELSE 2 END;

案例2

1.创建表

create table people_sex( country varchar(255),sex int, population bigint);
insert into people_sex values('中国',1,340);
insert into people_sex values('中国',2,260);
insert into people_sex values('美国',1,45);
insert into people_sex values('美国',2,55);
insert into people_sex values('加拿大',1,51);
insert into people_sex values('加拿大',2,49);
insert into people_sex values('英国',1,40);
insert into people_sex values('英国',1,60);

mysql> select * from people_sex;
+-----------+------+------------+
| country   | sex  | population |
+-----------+------+------------+
| 中国      |    1 |        340 |
| 中国      |    2 |        260 |
| 美国      |    1 |         45 |
| 美国      |    2 |         55 |
| 加拿大    |    1 |         51 |
| 加拿大    |    2 |         49 |
| 英国      |    1 |         40 |
| 英国      |    1 |         60 |
+-----------+------+------------+
8 rows in set (0.00 sec)

2.用一个SQL语句完成不同条件的分组。（列转行）

-- then后面可以跟具体数值也可以跟字段对应的值
SELECT country,
    SUM( CASE WHEN sex = '1' THEN population ELSE 0 END  ) as man_number,  
    SUM( CASE WHEN sex = '2' THEN population ELSE 0 END ) as woman_number   
FROM  people_sex
GROUP BY country;

+-----------+------------+--------------+
| country   | man_number | woman_number |
+-----------+------------+--------------+
| 中国      |        340 |          260 |
| 加拿大    |         51 |           49 |
| 美国      |         45 |           55 |
| 英国      |        100 |            0 |
+-----------+------------+--------------+