SQL 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 表达式

1
2
3
4
5
6
7
8
CASE WHEN <求值表达式> THEN <表达式>
     WHEN <求值表达式> THEN <表达式>
     WHEN <求值表达式> THEN <表达式>
       .
       .
       .
     ELSE <表达式>
END

WHEN 子句中的“<求值表达式>”就是类似“列 = 值”这样,返回值为真值(TRUE/FALSE/UNKNOWN)的表达式。我们也可以将其看作使用 =!= 或者 LIKEBETWEEN 等谓词编写出来的表达式。

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 的字符串加入到商品种类当中

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
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 也无法实现行列转换

    1
    2
    3
    4
    
    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 表达式进行行列转换

    1
    2
    3
    4
    5
    6
    7
    8
    
    -- 对按照商品种类计算出的销售单价合计值进行行列转换
    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 表达式

1
2
3
4
5
6
7
8
9
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 添加到商品种类中

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- 使用搜索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

1
2
3
4
5
6
7
8
-- 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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
-- 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 语句。

请参阅#

(完)


  1. 在 C 语言和 Java 等流行的编程语言中,通常都会使用 IF 语句或者 CASE 语句。CASE 表达式就是这些语句的 SQL 版本。 ↩︎

comments powered by Disqus