结构化查询语言/SELECT：CASE 表达式

有时需要将存储的值（或要存储的值）从一种表示形式转换为另一种表示形式。假设有一个名为 status 的列，其合法值从 0 到 9，但最终用户应该收到简短解释数字值的字符串，例如：'ordered'、'delivered'、'back delivery'、'out of stock'，... 。推荐的方法是使用一个单独的表，将数字值映射到解释性字符串。尽管如此，应用程序开发人员可能更喜欢在应用程序服务器中找到解决方案。

CASE 表达式（在本页展示）是一种技术，用于解决在 SELECT、INSERT 或 UPDATE 命令中描述的情况，以及解决其他问题。作为语言的一部分，它是一个强大的术语，可以应用在 SQL 命令中的许多地方。在本页中，我们将重点关注它与 SELECT 命令一起使用的情况。在 INSERT 和 UPDATE 中，CASE 的策略和语法是等效的，并在那里进行了介绍。与推荐的为翻译使用单独表的技术相比，CASE 表达式更加灵活（并非在所有情况下都是优势）。

两个示例

-- Technical term: "simple case" 
-- Select id, contact_type in a translated version and contact_value
SELECT id,
       CASE contact_type
         WHEN 'fixed line' THEN 'Phone'
         WHEN 'mobile'     THEN 'Phone'
         ELSE                   'Not a telephone number'
       END AS 'contact_type',
       contact_value
FROM   contact;

CASE 表达式以其关键字 CASE 作为开头，一直延续到 END 关键字。在这个第一个例子中，它指定了一个列名和一系列 WHEN/THEN 子句，以及一个可选的 ELSE 子句。依次比较和评估 WHEN/THEN 子句，并与命名列的值进行比较。如果没有任何命中，则应用 ELSE 子句。如果没有 ELSE 子句，并且没有任何 WHEN/THEN 子句命中，则将应用 NULL 特殊标记。

列的值与 WHEN/THEN 子句中的固定值之间的比较仅通过 "="（等于）进行。这是一个良好的起点，但实际应用需要更多内容。因此，存在 CASE 的变体。

-- Technical term: "searched case" 
-- Select persons name, weight and a denomination of the weight 
SELECT firstname,
       lastname,
       weight,
       CASE
         WHEN (weight IS NULL OR weight = 0) THEN 'weight is unknown'
         WHEN weight < 40                    THEN 'lightweight'
         WHEN weight BETWEEN 40 AND 85       THEN 'medium'
         ELSE                                     'heavyweight'
       END AS 'weight'
FROM   person;

关键点是 CASE 和 WHEN 两个关键字的直接衔接。它们之间没有任何列名。在此变体中，在每个 WHEN 和 THEN 之间必须有一个完整的表达式，该表达式评估为三个值逻辑项之一：true、false 或 unknown。现在可以像 WHERE 子句 中已知的那样，使用所有比较和布尔运算符。甚至可以比较不同的列或函数调用。

语法

存在两种变体：简单 CASE 和 搜索 CASE。

-- "simple case" performs successive comparisons using the equal operator: <column_name> = <expression_x>
CASE <column_name>
  WHEN <expression_1> THEN <result_1>
  WHEN <expression_2> THEN <result_2>
  ...
  ELSE                     <default_result>  -- optional
END

-- "searched case" is recognised by 'nothing' between CASE and first WHEN
CASE
  WHEN <condition_1> THEN <result_1>
  WHEN <condition_2> THEN <result_2>
  ...
  ELSE                    <default_result>  -- optional
END

简单 CASE 仅限于一列和等号运算符的使用，而搜索 CASE 可以使用任意运算符、函数或谓词来评估（中间）结果的任意列。

典型用例

CASE 表达式的使用并不局限于投影（SELECT 和 FROM 之间的列列表）。由于该子句会评估为一个值，因此可以将其应用为 SQL 命令中多个地方的值的替代品。在下面，我们将提供一些示例。

ORDER BY 子句

按以下顺序对联系值进行排序：所有固定电话、所有手机、所有电子邮件、所有 ICQ。在每个组内，按联系值排序。

SELECT *
FROM   contact
ORDER  BY
       -- a "simple case" construct as substitution for a column name
       CASE contact_type
         WHEN 'fixed line' THEN 0
         WHEN 'mobile'     THEN 1
         WHEN 'email'      THEN 2
         WHEN 'icq'        THEN 3
         ELSE                   4
       END,
       contact_value;

在下一个示例中，人员按体重类别排序，在每个类别内按姓名排序。

-- order by weight classes
SELECT firstname, lastname, weight,
       CASE
         WHEN (weight IS NULL OR weight = 0) THEN 'weight is unknown'
         WHEN weight < 40                    THEN 'lightweight'
         WHEN weight BETWEEN 40 AND 85       THEN 'medium'
         ELSE                                     'heavyweight'
       END
FROM   person
ORDER  BY
       -- a "searched case" construct with IS NULL, BETWEEN and 'less than'.
       CASE
         WHEN (weight IS NULL OR weight = 0) THEN 0
         WHEN weight < 40                    THEN 1
         WHEN weight BETWEEN 40 AND 85       THEN 2
         ELSE                                     3 
       END, lastname, firstname;

WHERE 子句

在 WHERE 子句中，可能会出现固定值或列名。CASE 表达式可以用作它们的替代品。在示例中，人员会根据其出生地的不同获得不同的体重折扣（将其视为理论示例）。因此，体重为 95 公斤的金先生只算 76 公斤，不属于结果集的一部分。

SELECT *
FROM   person
WHERE  CASE
         -- Modify weight depending on place of birth.
         WHEN place_of_birth = 'Dallas'   THEN weight * 0.8
         WHEN place_of_birth = 'Richland' THEN weight * 0.9
         ELSE                                  weight
       END > 80
OR     weight < 20; -- any other condition

练习

显示所有人员的姓氏、名字和性别。将 Larry、Tom、James、John、Elias、Yorgos、Victor 视为 'male'，
Lisa 视为 'female'，其余所有人视为 'unknown gender'。使用 简单 CASE 表达式。

点击查看解决方案

SELECT firstname, lastname,
       CASE firstname
         WHEN 'Larry'   THEN 'male'
         WHEN 'Tom'     THEN 'male'
         WHEN 'James'   THEN 'male'
         WHEN 'John'    THEN 'male'
         WHEN 'Elias'   THEN 'male'
         WHEN 'Yorgos'  THEN 'male'
         WHEN 'Victor'  THEN 'male'
         WHEN 'Lisa'    THEN 'female'
         ELSE                'unknown gender'
       END
FROM   person;

使用 搜索 CASE 表达式来解决上一个问题。

点击查看解决方案

SELECT firstname, lastname,
       CASE
         WHEN firstname in ('Larry', 'Tom', 'James', 'John', 'Elias', 'Yorgos', 'Victor')
                                     THEN 'male'
         WHEN firstname = 'Lisa'     THEN 'female'
         ELSE                             'unknown gender'
       END
FROM   person;

显示所有人员的姓氏、名字和分类。根据姓氏的长度对人员进行分类。如果 character_length(firstname) < 4，则将该类别命名为 'short name'，如果 < 6，则命名为 'medium length'，否则命名为 'long name'。

点击查看解决方案

-- Hint: Some implementations use a different function name: length() or len().
SELECT firstname, lastname,
       CASE 
         WHEN CHARACTER_LENGTH(firstname) < 4 THEN 'short name'
         WHEN CHARACTER_LENGTH(firstname) < 6 THEN 'medium length'
         ELSE                                      'long name'
       END
FROM   person;

统计上一个练习中短名、中长名和长名的数量。

点击查看解决方案

-- Hint: Some implementations use a different function name: length() or len().
SELECT SUM(CASE 
             WHEN CHARACTER_LENGTH(firstname) < 4 THEN 1
             ELSE                                      0
           END) as short_names,
       SUM(CASE 
             WHEN CHARACTER_LENGTH(firstname) between 4 and 5 THEN 1
             ELSE                                                  0
           END) as medium,
       SUM(CASE 
             WHEN CHARACTER_LENGTH(firstname) > 5 THEN 1
             ELSE                                      0
           END) as long_names
FROM   person;