一、语法

Case具有两种格式。简单Case函数和Case搜索函数。

简单Case函数格式:

CASE 列名WHEN 条件值1 THEN 选项1WHEN 条件值2 THEN 选项2……ELSE 默认值END

Case搜索函数:

CASEWHEN 条件1 THEN 选项1WHEN 条件2 THEN 选项2……ELSE 默认值END

二、case when应用场景

case when与子查询性能比较及优化。
为了方便说明,我们先创建表,并造点数据。

CREATE TABLE `table_a` (`id` INT NOT NULL AUTO_INCREMENT,`country` VARCHAR(50) NOT NULL,`sex` CHAR(2) not null,`population` int NOT NULL,PRIMARY KEY (`id`));insert into table_a values(null,"中国","男",10);insert into table_a values(null,"中国","女",5);insert into table_a values(null,"美国","男",2);insert into table_a values(null,"美国","女",4);insert into table_a values(null,"加拿大","男",4);insert into table_a values(null,"加拿大","女",4);insert into table_a values(null,"英国","男",6);insert into table_a values(null,"英国","女",6);insert into table_a values(null,"法国","男",2);insert into table_a values(null,"法国","女",2);insert into table_a values(null,"日本","男",7);insert into table_a values(null,"日本","女",7);insert into table_a values(null,"德国","男",2);insert into table_a values(null,"墨西哥","男",7);insert into table_a values(null,"印度","男",1);

2.1 案例一

统计亚洲和北美洲的人口数量,要求结果如下:

若第一时间没有想到case when,我们可能会写出下面的sql:

SELECT sum(population) from Table_A where country in ('中国','印度','日本')UNIONSELECT sum(population) from Table_A where country in ('美国','加拿大','墨西哥')UNIONSELECT sum(population) from Table_A where country not in ('中国','印度','日本','美国','加拿大','墨西哥');

运行结果:

这个sql的性能效率比较低,对同一个数据表查询了三次,也无法获得州的那一列。

使用case when进行改造,如下:

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

运行结果:

使用了case when的sql语句明显效率高一些,因为它仅查找了一次表而已,若想获得州的那一列,只需改写如下:

SELECTSUM(population), (CASE countryWHEN '中国' THEN '亚洲' WHEN '印度' THEN '亚洲' WHEN '日本' THEN '亚洲' WHEN '美国' THEN '北美洲' WHEN '加拿大'THEN '北美洲' WHEN '墨西哥'THEN '北美洲' ELSE '其他' END ) asFROMTable_A GROUP BYCASE country WHEN '中国' THEN '亚洲' WHEN '印度' THEN '亚洲'WHEN '日本' THEN '亚洲' WHEN '美国' THEN '北美洲' WHEN '加拿大'THEN '北美洲' WHEN '墨西哥'THEN '北美洲' ELSE '其他' END; 

运行结果:

2.2 案例二

统计每个国家的男生人数和女生人数,要求结果如下:

同样的,不使用case when的写法如下:

SELECTa.country,(SELECTSUM( a1.population ) FROMtable_a a1 WHEREa1.country = a.country AND a1.sex = '男' ),(SELECTSUM( a1.population ) FROMtable_a a1 WHEREa1.country = a.country AND a1.sex = '女' )FROMtable_a a GROUP BYa.country;

执行结果:

使用case when进行优化:

SELECT COUNTRY,SUM(CASE SEX WHEN '男' THEN population ELSE 0 END) AS '男',SUM(CASE SEX WHEN '女' THEN population ELSE 0 END) AS '女'FROM table_a GROUP BY COUNTRY;

执行结果:

两者对比,显然的case when的效率既简洁,效率也高。

2.3 案例三

上述两个案例也许不够贴近日常的工作内容,下面举个现实工作遇到的案例。

建表sql如下:

-- 货架表CREATE TABLE `shelves` (`shelves_id` INT NOT NULL AUTO_INCREMENT, -- 货架id`shelves_num` VARCHAR(50) NOT NULL UNIQUE,-- 货架号`shelves_area` VARCHAR(50) NOT NULL,--货架区域PRIMARY KEY (`shelves_id`));-- 物品表CREATE TABLE `goods` (`goods_id` INT NOT NULL AUTO_INCREMENT, -- 物品id`goods_name` VARCHAR(50) NOT NULL UNIQUE,-- 物品名称`goods_type` VARCHAR(20) NOT NULL,-- 物品类型`goods_quantity` int NOT NULL,-- 物品数量`goods_createTime` DATETIME NULL DEFAULT NULL,-- 创建时间`goods_expiryTime` DATETIME NULL DEFAULT NULL,-- 过期时间`goods_shelvesId` INT NULL DEFAULT NULL,-- 货架idPRIMARY KEY (`goods_id`));

需求说明:统计每个货架上的物品数量,要求统计结果如下

使用子查询的写法:

SELECTshelves_area shelvesArea,shelves_num shelvesNum,COUNT( DISTINCT goods_type ) goodsTypeSum,COUNT( goods_id ) goodsSum,(SELECTCOUNT(*)FROMgoodsWHEREgoods_expiryTime < NOW()AND goods_shelvesId = shelves_id ) isNotExpiry,(SELECTCOUNT(*)FROMgoodsWHEREgoods_expiryTime > NOW()AND goods_shelvesId = shelves_id) isExpiryFROMshelvesLEFT JOIN goods ON shelves_id = goods_shelvesIdGROUP BY shelves_id;

使用case when的写法:

SELECTshelves_area shelvesArea,shelves_num shelvesNum,COUNT( DISTINCT goods_type ) goodsTypeSum,COUNT( goods_id ) goodsSum,SUM(CASE WHEN (shelves_id = goods_shelvesId AND goods_expiryTime < NOW()) THEN 1 ELSE 0 END) isNotExpiry,SUM(CASE WHEN (shelves_id = goods_shelvesId AND goods_expiryTime > NOW()) THEN 1 ELSE0 END) isExpiryFROMshelvesLEFT JOIN goods ON shelves_id = goods_shelvesIdGROUP BY shelves_id;

两个不同写法的运行结果是一样的,但是性能效率上case when 显然比子查询的高一些。
运行结果如下(本人未造相关测试数据):

三、扩展

3.1 根据条件有选择的UPDATE

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

很容易考虑的是选择执行两次UPDATE语句,如下所示

--条件1 UPDATE PersonnelSET salary = salary * 0.9WHERE salary >= 5000; --条件2 UPDATE PersonnelSET salary = salary * 1.15 WHERE salary >= 2000 AND salary < 4600;

但是事情没有想象得那么简单,假设有个人工资5000块。首先,按照条件1,工资减少10%,变成工资4500。接下来运行第二个SQL时候,因为这个人的工资是4500在2000到4600的范围之内,需增加15%,最后这个人的工资结果是5175,不但没有减少,反而增加了。如果要是反过来执行,那么工资4600的人相反会变成减少工资。暂且不管这个规章是多么荒诞,如果想要一个SQL 语句实现这个功能的话,我们需要用到Case函数。代码如下:

UPDATEPersonnel SETsalary = CASEWHENsalary >= 5000THENsalary * 0.9 WHENsalary >= 2000 AND salary < 4600THENsalary * 1.15ELSEsalary END;

这里要注意一点,最后一行的ELSE salary是必需的,要是没有这行,不符合这两个条件的人的工资将会被写成NUll,那可就大事不妙了。在Case函数中Else部分的默认值是NULL,这点是需要注意的地方。

这种方法还可以在很多地方使用,比如说变更主键这种累活。
一般情况下,要想把两条数据的Primary key,a和b交换,需要经过临时存储,拷贝,读回数据的三个过程,要是使用Case函数的话,一切都变得简单多了。
p_key col_1 col_2
a 1 张三
b 2 李四
c 3 王五
假设有如上数据,需要把主键a和b相互交换。用Case函数来实现的话,代码如下

UPDATE SomeTable SET p_key = CASEWHENp_key = 'a'THEN 'b'WHEN p_key = 'b'THEN'a'ELSE p_key ENDWHERE p_key IN('a', 'b');

四、参考来源

https://blog.csdn.net/Max_Rzdq/article/details/79418893?utm_medium=distribute.pc_relevant.none-task-blog-BlogCommendFromBaidu-2.control&depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromBaidu-2.control