SQL中case when用法详解及使用案例

大家好，欢迎来到IT知识分享网。

一、语法

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

简单Case函数格式：

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

Case搜索函数：

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

二、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 ('中国','印度','日本') UNION SELECT sum(population) from Table_A where country in ('美国','加拿大','墨西哥') UNION SELECT sum(population) from Table_A where country not in ('中国','印度','日本','美国','加拿大','墨西哥'); 

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

使用case when进行改造，如下：

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

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

2.2 案例二

SELECT a.country, ( SELECT SUM( a1.population ) FROM table_a a1 WHERE a1.country = a.country AND a1.sex = '男' ) 男, ( SELECT SUM( a1.population ) FROM table_a a1 WHERE a1.country = a.country AND a1.sex = '女' ) 女 FROM table_a a GROUP BY a.country; 

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; 

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, -- 货架id PRIMARY KEY (`goods_id`) ) ; 

SELECT shelves_area shelvesArea, shelves_num shelvesNum, COUNT( DISTINCT goods_type ) goodsTypeSum, COUNT( goods_id ) goodsSum, ( SELECT COUNT(*) FROM goods WHERE goods_expiryTime < NOW() AND goods_shelvesId = shelves_id ) isNotExpiry, ( SELECT COUNT(*) FROM goods WHERE goods_expiryTime > NOW() AND goods_shelvesId = shelves_id) isExpiry FROM shelves LEFT JOIN goods ON shelves_id = goods_shelvesId GROUP BY shelves_id; 

使用case when的写法：

SELECT shelves_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 ELSE 0 END) isExpiry FROM shelves LEFT JOIN goods ON shelves_id = goods_shelvesId GROUP BY shelves_id; 

三、扩展

3.1 根据条件有选择的UPDATE

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

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

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

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函数中Else部分的默认值是NULL，这点是需要注意的地方。

UPDATE SomeTable SET p_key = CASE WHEN p_key = 'a' THEN 'b' WHEN p_key = 'b' THEN 'a' ELSE p_key END WHERE p_key IN('a', 'b'); 

四、参考来源

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