case when then else多个条件_SQL巡礼之CASE用法

使用CASE表达式使SQL语句的条件判断形式变得十分丰富，也因为CASE表达式不依赖于具体的数据库技术，所以它的可移植性也会更高。

现在就让我们一起来领略一下CASE语句的用法吧。

CASE表达式语法

我们先创建一个Table用来举例（本文全部代码在MySQL 8.0.17和 PostgreSQL 12.2中测试通过）：

CREATE TABLE Goods
(goods_id       CHAR(4)         NOT NULL    ,
goods_name      VARCHAR(100)    NOT NULL    ,
goods_items     VARCHAR(32)     NOT NULL    ,
selling_price   INTEGER                     ,
cost_price      INTEGER                     ,
decision_date   DATE                        ,
PRIMARY KEY (goods_id));-- MySQL中请写成START TRANSACTION;
BEGIN TRANSACTION;  INSERT INTO Goods VALUES ('0001','T恤衫','衣服',1000,500,'2009-09-20');
INSERT INTO Goods VALUES ('0002','打孔器','办公用品',500,320,'2009-09-11');
INSERT INTO Goods VALUES ('0003','运动T恤','衣服',4000,2800,NULL);
INSERT INTO Goods VALUES ('0004','菜刀','厨房用具',3000,2800,'2009-09-20');
INSERT INTO Goods VALUES ('0005','高压锅','厨房用具',6800,5000,'2009-01-15');
INSERT INTO Goods VALUES ('0006','叉子','厨房用具',500,NULL,'2009-09-20');
INSERT INTO Goods VALUES ('0007','擦菜板','厨房用具',880,790,'2008-04-28');
INSERT INTO Goods VALUES ('0008','圆珠笔','办公用品',100,NULL,'2009-11-11');COMMIT;

数据库里的表长这样：

现在表建好了，我们开始吧！

两种表达式

CASE表达式的语法分为简单和复杂两种，其中复杂语法又称“搜索CASE表达式”，已经完全包含了简单语法，建议直接学习复杂语句语法，简单语法了解一下即可。

简单CASE表达式语法

CASE <表达式>WHEN <表达式> THEN <表达式>WHEN <表达式> THEN <表达式>WHEN <表达式> THEN <表达式>...ELSE <表达式>
END;

举个栗子，如果我们要从Goods表中选取出各个物品，并为这些物品类别重命名，命名为 “字母：品类名称” 的形式，那么可以这样操作：

PostgreSQL代码

SELECT goods_name, CASE goods_itemsWHEN '衣服' THEN 'A:' || goods_itemsWHEN '办公用品' THEN 'B:' || goods_itemsWHEN '厨房用具' THEN 'C:' || goods_itemsELSE NULLEND AS abc_goods_items
FROM Goods;

MySQL代码

SELECT goods_name, CASE goods_itemsWHEN '衣服' THEN CONCAT('A:', goods_items)WHEN '办公用品' THEN CONCAT('B:', goods_items)WHEN '厨房用具' THEN CONCAT('C:', goods_items)ELSE NULLEND AS abc_goods_items
FROM Goods;

可以看出当CASE指定了goods_items列后，只需要在“WHEN”关键字后面写对应的值就行，并且在“THEN”后面指明满足这种条件的情况下，返回什么值。“ELSE NULL” 表明“如果上述情况都不满足，就返回一个NULL”。ELSE语句也可以不写，默认返回一个NULL值，但为了代码的可读性，还是建议写上去。

搜索CASE表达式语法

CASE WHEN <判断表达式> THEN <判断表达式>WHEN <判断表达式> THEN <判断表达式>WHEN <判断表达式> THEN <判断表达式>...ELSE <判断表达式>
END

还是上面那个例子，我们使用搜索CASE表达式语法可以写成以下这种形式：

PostgreSQL代码

SELECT goods_name,CASE WHEN goods_items = '衣服' THEN 'A:' || goods_itemsWHEN goods_items = '办公用品' THEN 'B:' || goods_itemsWHEN goods_items = '厨房用具' THEN 'C:' || goods_itemsELSE NULLEND AS abc_goods_items
FROM Goods;

MySQL代码

SELECT goods_name,CASE WHEN goods_items = '衣服' THEN CONCAT('A:', goods_items)WHEN goods_items = '办公用品' THEN CONCAT('B:', goods_items)WHEN goods_items = '厨房用具' THEN CONCAT('C:', goods_items)ELSE NULLEND AS abc_goods_items
FROM Goods;

两者的区别

简单CASE表达式在只针对一列操作的时候，简单易写，代码量较少，像上述代码中展示的一样，简单CASE表达式在写了一次列名“goods_items”后，在WHEN后面就不需要再写了。然而，在实际业务场景下，经常需要针对多列多条件判断，这时候简单CASE表达式就无能为力了，譬如：

SELECT goods_name,selling_price,CASE WHEN selling_price - cost_price > 0  THEN 'Profitable'WHEN selling_price - cost_price < 0  THEN 'Defective'WHEN selling_price - cost_price = 0  THEN 'Fair'ELSE NULLEND AS "P&L"
FROM Goods;

可以看出，搜索CASE表达式的语法可读性更强，后期维护也比简单CASE表达式来得简单。推荐大家使用搜索CASE表达式。

CASE表达式的奇技淫巧

行列互换

使用CASE表达式可以实现行列互换，举个栗子，我们创建一个这样的表格：

MySQL代码：

CREATE TABLE IF NOT EXISTS sales
(`年`        INTEGER     NOT NULL,
`季度`    INTEGER     NOT NULL,
`销售量`   INTEGER);
START TRANSACTION;INSERT INTO sales VALUES ( 1991, 1, 11),(1991, 2, 12),(1991, 3, 13),(1991, 4, 14),(1992, 1, 21),(1992, 2, 22),(1992, 3, 23),(1992, 4, 24);COMMIT;

PostgreSQL代码：

CREATE TABLE IF NOT EXISTS sales
("年"       INTEGER     NOT NULL,
"季度"      INTEGER   NOT NULL,
"销售量"     INTEGER);BEGIN TRANSACTION;INSERT INTO sales VALUES ( 1991, 1, 11),(1991, 2, 12),(1991, 3, 13),(1991, 4, 14),(1992, 1, 21),(1992, 2, 22),(1992, 3, 23),(1992, 4, 24);COMMIT;

表格长这样：

现在，我们需要把表格变成这样：

需要怎么做呢？

很简单，我们可以借助CASE表达式。

MySQL代码：

SELECT `年`,  SUM(CASE WHEN `季度` = 1 THEN `销售量` ELSE 0 END) AS `一季度`, SUM(CASE WHEN `季度` = 2 THEN `销售量` ELSE 0 END) AS `二季度`, SUM(CASE WHEN `季度` = 3 THEN `销售量` ELSE 0 END) AS `三季度`, SUM(CASE WHEN `季度` = 4 THEN `销售量` ELSE 0 END) AS `四季度`
FROM sales GROUP BY `年`;

PostgreSQL代码：

SELECT "年",  SUM(CASE WHEN "季度" = 1 THEN "销售量" ELSE 0 END) AS "一季度", SUM(CASE WHEN "季度" = 2 THEN "销售量" ELSE 0 END) AS "二季度", SUM(CASE WHEN "季度" = 3 THEN "销售量" ELSE 0 END) AS "三季度", SUM(CASE WHEN "季度" = 4 THEN "销售量" ELSE 0 END) AS "四季度"
FROM sales GROUP BY "年";

将已有方式换成新的方式统计

如想将以下表格统计方式更换：

CREATE TABLE IF NOT EXISTS Pop   
(country_name VARCHAR(20)     NOT NULL,   
population        INTEGER         NOT NULL);-- PostgreSQL 请将以下半角双引号换成半角单引号INSERT INTO Pop VALUES ("中国", 1400050000),                        ("印度", 1369640000),                          ("巴西", 211330000),                          ("尼日利亚", 206510000),                          ("英国", 66690000),                          ("美国", 329460000);

表格长这样：

现在我们想按洲来统计人数，结果如下：

可以这样做：

SELECT CASE country_name WHEN '中国' THEN '亚洲'WHEN '印度' THEN '亚洲'WHEN '巴西' THEN '南美洲'WHEN '尼日利亚' THEN '非洲'WHEN '英国' THEN '欧洲'WHEN '美国' THEN '北美洲' ELSE NULL END AS name_of_continent ,SUM(population) as population
FROM Pop
GROUP BY CASE country_name WHEN '中国' THEN '亚洲'WHEN '印度' THEN '亚洲'WHEN '巴西' THEN '南美洲'WHEN '尼日利亚' THEN '非洲'WHEN '英国' THEN '欧洲'WHEN '美国' THEN '北美洲' ELSE NULL END ;

这里需要注意的是，GROUP BY后面要接上SELECT 子句中的CASE条件。如果对转换前的country_name进行GROUP BY 就会出现统计错误（而不是语法错误，容易被忽视）。