SQL CASE 表达式
- 一、什么是 CASE 表达式
- 二、CASE 表达式的语法
- 三、CASE 表达式的使用方法
- 请参阅
学习重点
CASE
表达式分为简单CASE
表达式和搜索CASE
表达式两种。搜索CASE
表达式包含简单CASE
表达式的全部功能。虽然
CASE
表达式中的ELSE
子句可以省略,但为了让 SQL 语句更加容易理解,还是希望大家不要省略。
CASE
表达式中的END
不能省略。使用
CASE
表达式能够将SELECT
语句的结果进行组合。虽然有些 DBMS 提供了各自特有的
CASE
表达式的简化函数,例如 Oracle 中的DECODE
和 MySQL 中的IF
,等等,但由于它们并非通用的函数,功能上也有些限制,因此有些场合无法使用。
一、什么是 CASE
表达式
本文将要学习的 CASE
表达式,和“1 + 1
”或者“120 / 4
”这样的表达式一样,是一种进行运算的功能。这就意味着 CASE
表达式也是函数的一种。它是 SQL 中数一数二的重要功能,希望大家能够在这里好好学习掌握。
CASE
表达式是在区分情况时使用的,这种情况的区分在编程中通常称为 (条件)分支 [1]。
KEYWORD
CASE
表达式分支(条件分支)
二、CASE
表达式的语法
CASE
表达式的语法分为简单 CASE
表达式和搜索 CASE
表达式两种。但是,由于搜索 CASE
表达式包含了简单 CASE
表达式的全部功能,因此本文只会介绍搜索 CASE
表达式。想要了解简单 CASE
表达式语法的读者,可以参考本文末尾的“简单 CASE
表达式”专栏。
KEYWORD
简单
CASE
表达式搜索
CASE
表达式
下面就让我们赶快来学习一下搜索 CASE
表达式的语法吧。
语法 16 搜索 CASE
表达式
CASE WHEN <求值表达式> THEN <表达式>
WHEN <求值表达式> THEN <表达式>
WHEN <求值表达式> THEN <表达式>
.
.
.
ELSE <表达式>
END
WHEN
子句中的“<求值表达式>
”就是类似“列 = 值
”这样,返回值为真值(TRUE
/FALSE
/UNKNOWN
)的表达式。我们也可以将其看作使用 =
、!=
或者 LIKE
、BETWEEN
等谓词编写出来的表达式。
CASE
表达式会从对最初的 WHEN
子句中的“<求值表达式>
”进行求值开始执行。所谓求值,就是要调查该表达式的真值是什么。如果结果为真(TRUE
),那么就返回 THEN
子句中的表达式,CASE
表达式的执行到此为止。如果结果不为真,那么就跳转到下一条 WHEN
子句的求值之中。如果直到最后的 WHEN
子句为止返回结果都不为真,那么就会返回 ELSE
中的表达式,执行终止。
KEYWORD
WHEN
子句求值
THEN
子句
ELSE
从 CASE
表达式名称中的“表达式”我们也能看出来,上述这些整体构成了一个表达式。并且由于表达式最终会返回一个值,因此 CASE
表达式在 SQL 语句执行时,也会转化为一个值。虽然使用分支众多的 CASE
表达式编写几十行代码的情况也并不少见,但是无论多么庞大的 CASE
表达式,最后也只会返回类似“1
”或者“" 渡边先生 "
”这样简单的值。
三、CASE
表达式的使用方法
那么就让我们来学习一下 CASE
表达式的具体使用方法吧。例如我们来考虑这样一种情况,现在 Product
(商品)表中包含衣服、办公用品和厨房用具 3 种商品类型,请大家考虑一下怎样才能够得到如下结果。
A :衣服
B :办公用品
C :厨房用具
因为表中的记录并不包含“A :”或者“B :”这样的字符串,所以需要在 SQL 中进行添加。我们可以使用 各种各样的函数 中学过的字符串连接函数“||
”来完成这项工作。
剩下的问题就是怎样正确地将“A :”“B :”“C :”与记录结合起来。这时就可以使用 CASE
表达式来实现了(代码清单 41)。
代码清单 41 通过 CASE
表达式将 A ~ C 的字符串加入到商品种类当中
SELECT product_name,
CASE WHEN product_type = "衣服"
THEN "A:" || product_type
WHEN product_type = "办公用品"
THEN "B:" || product_type
WHEN product_type = "厨房用具"
THEN "C:" || product_type
ELSE NULL
END AS abc_product_type
FROM Product;
执行结果
product_name | abc_product_type
--------------+------------------
T恤衫 | A :衣服
打孔器 | B :办公用品
运动T恤 | A :衣服
菜刀 | C :厨房用具
高压锅 | C :厨房用具
叉子 | C :厨房用具
擦菜板 | C :厨房用具
圆珠笔 | B :办公用品
6 行 CASE
表达式代码最后只相当于 1 列(abc_product_type
)而已,大家也许有点吃惊吧!与商品种类(product_type
)的名称相对应,CASE
表达式中包含了 3 条 WHEN
子句分支。最后的 ELSE NULL
是“上述情况之外时返回 NULL
”的意思。ELSE
子句指定了应该如何处理不满足 WHEN
子句中的条件的记录,NULL
之外的其他值或者表达式也都可以写在 ELSE
子句之中。但由于现在表中包含的商品种类只有 3 种,因此实际上有没有 ELSE
子句都是一样的。
KEYWORD
ELSE NULL
ELSE
子句也可以省略不写,这时会被默认为 ELSE NULL
。但为了防止有人漏读,还是希望大家能够显式地写出 ELSE
子句。
法则 3
虽然
CASE
表达式中的ELSE
子句可以省略,但还是希望大家不要省略。
此外,CASE
表达式最后的“END
”是不能省略的,请大家特别注意不要遗漏。忘记书写 END
会发生语法错误,这也是初学时最容易犯的错误。
法则 4
CASE
表达式中的END
不能省略。
-
CASE
表达式的书写位置CASE
表达式的便利之处就在于它是一个表达式。之所以这么说,是因为表达式可以书写在任意位置,也就是像“1 + 1”这样写在什么位置都可以的意思。例如,我们可以像下面这样利用CASE
表达式将SELECT
语句的结果中的行和列进行互换。执行结果
sum_price_clothes | sum_price_kitchen | sum_price_office ------------------+-------------------+----------------- 5000 | 11180 | 600
上述结果是根据商品种类计算出的销售单价的合计值,通常我们将商品种类列作为
GROUP BY
子句的聚合键来使用,但是这样得到的结果会以“行”的形式输出,而无法以列的形式进行排列(代码清单 42)。代码清单 42 通常使用
GROUP BY
也无法实现行列转换SELECT product_type, SUM(sale_price) AS sum_price FROM Product GROUP BY product_type;
执行结果
product_type | sum_price --------------+---------- 衣服 | 5000 办公用品 | 600 厨房用具 | 11180
我们可以像代码清单 43 那样在
SUM
函数中使用CASE
表达式来获得一个 3 列的结果。代码清单 43 使用
CASE
表达式进行行列转换-- 对按照商品种类计算出的销售单价合计值进行行列转换 SELECT SUM(CASE WHEN product_type = "衣服" THEN sale_price ELSE 0 END) AS sum_price_clothes, SUM(CASE WHEN product_type = "厨房用具" THEN sale_price ELSE 0 END) AS sum_price_kitchen, SUM(CASE WHEN product_type = "办公用品" THEN sale_price ELSE 0 END) AS sum_price_office FROM Product;
在满足商品种类(
product_type
)为“衣服”或者“办公用品”等特定值时,上述CASE
表达式输出该商品的销售单价(sale_price
),不满足时输出 0。对该结果进行汇总处理,就能够得到特定商品种类的销售单价合计值了。在对
SELECT
语句的结果进行编辑时,CASE
表达式能够发挥较大作用。
专栏
简单
CASE
表达式
CASE
表达式分为两种,一种是本文学习的“搜索CASE
表达式”,另一种就是其简化形式——“简单CASE
表达式”。简单
CASE
表达式比搜索CASE
表达式简单,但是会受到条件的约束,因此通常情况下都会使用搜索CASE
表达式。在此我们简单介绍一下其语法结构。简单
CASE
表达式的语法如下所示。语法 A 简单
CASE
表达式
CASE <表达式> WHEN <表达式> THEN <表达式> WHEN <表达式> THEN <表达式> WHEN <表达式> THEN <表达式> . . . ELSE <表达式> END
与搜索
CASE
表达式一样,简单CASE
表达式也是从最初的WHEN
子句开始进行,逐一判断每个WHEN
子句直到返回真值为止。此外,没有能够返回真值的WHEN
子句时,也会返回ELSE
子句指定的表达式。两者的不同之处在于,简单CASE
表达式最初的“CASE <表达式>
”也会作为求值的对象。下面就让我们来看一看搜索
CASE
表达式和简单CASE
表达式是如何实现相同含义的 SQL 语句的。将代码清单 41 中的搜索CASE
表达式的 SQL 改写为简单CASE
表达式,结果如下所示(代码清单 A)。代码清单 A 使用
CASE
表达式将字符串 A ~ C 添加到商品种类中
-- 使用搜索CASE表达式的情况(重写代码清单6-41) SELECT product_name, CASE WHEN product_type = "衣服" THEN "A :" | |product_type WHEN product_type = "办公用品" THEN "B :" | |product_type WHEN product_type = "厨房用具" THEN "C :" | |product_type ELSE NULL END AS abc_product_type FROM Product; -- 使用简单CASE表达式的情况 SELECT product_name, CASE product_type WHEN "衣服" THEN "A :" || product_type WHEN "办公用品" THEN "B :" || product_type WHEN "厨房用具" THEN "C :" || product_type ELSE NULL END AS abc_product_type FROM Product;
像“
CASE product_type
”这样,简单CASE
表达式在将想要求值的表达式(这里是列)书写过一次之后,就无需在之后的WHEN
子句中重复书写“product_type
”了。虽然看上去简化了书写,但是想要在WHEN
子句中指定不同列时,简单CASE
表达式就无能为力了。
专栏
特定的
CASE
表达式由于
CASE
表达式是标准 SQL 所承认的功能,因此在任何 DBMS 中都可以执行。但是,有些 DBMS 还提供了一些特有的CASE
表达式的简化函数,例如 Oracle 中的DECODE
、MySQL 中的IF
等。KEYWORD
DECODE
函数(Oracle)
IF
函数(MySQL)使用 Oracle 中的
DECODE
和 MySQL 中的IF
将字符串 A ~ C 添加到商品种类(product_type
)中的 SQL 语句请参考代码清单 B。代码清单 B 使用
CASE
表达式的特定语句将字符串 A ~ C 添加到商品种类中Oracle
-- Oracle中使用DECODE代替CASE表达式 SELECT product_name, DECODE(product_type, "衣服", "A :" || product_type, "办公用品", "B :" || product_type, "厨房用具", "C :" || product_type, NULL) AS abc_product_type FROM Product;
MySQL
-- MySQL中使用IF代替CASE表达式 SELECT product_name, IF( IF( IF(product_type = "衣服", CONCAT("A :", product_type), NULL) IS NULL AND product_type = "办公用品", CONCAT("B :", product_type), IF(product_type = "衣服", CONCAT("A :", product_type), NULL)) IS NULL AND product_type = "厨房用具", CONCAT("C :", product_type), IF( IF(product_type = "衣服", CONCAT("A :", product_type), NULL) IS NULL AND product_type = "办公用品", CONCAT("B :", product_type), IF(product_type = "衣服", CONCAT("A :", product_type), NULL))) AS abc_product_type FROM Product;
但上述函数只能在特定的 DBMS 中使用,并且能够使用的条件也没有
CASE
表达式那么丰富,因此并没有什么优势。希望大家尽量不要使用这些特定的 SQL 语句。
请参阅
- 各种各样的函数
- SQL 谓词
- CASE 表达式
(完)
在 C 语言和 Java 等流行的编程语言中,通常都会使用
IF
语句或者CASE
语句。CASE
表达式就是这些语句的 SQL 版本。 ↩︎
免责声明:
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341