如何在SQL中使用CASE表达式

介绍

编程语言通常具有 条件语句 ，它们是执行指定操作直到满足特定条件的命令。一个常见的条件语句是if, then, else语句，一般遵循这样的逻辑：

if condition=true

    then action A

    else action B

该语句的逻辑翻译成以下语言：“如果条件为真，则执行动作A。否则（否则），执行动作 B。”

CASE 表达式是结构化查询语言 (SQL) 中的一项功能，它允许您将类似的逻辑应用于数据库查询，并设置您希望如何返回或显示结果集中的值的条件。

在本教程中，您将学习如何使用 CASE 表达式通过 WHEN、THEN、ELSE 和 END 关键字。

先决条件

要完成本教程，您需要：

运行 Ubuntu 20.04 的服务器，非 root 用户具有 sudo 管理权限并启用了防火墙。按照我们的使用 Ubuntu 20.04 的初始服务器设置开始。
在服务器上安装并保护 MySQL。按照我们的如何在 Ubuntu 20.04 上安装 MySQL 指南进行设置。本指南假定您还设置了非 root MySQL 用户，如本指南的 Step 3 中所述。

注意： 请注意，许多关系数据库管理系统使用自己独特的 SQL 实现。尽管本教程中概述的命令适用于大多数 RDBMS，但如果您在 MySQL 以外的系统上测试它们，确切的语法或输出可能会有所不同。

要在本教程中练习使用 CASE 表达式，您需要一个加载了示例数据的数据库和表。如果您没有准备好插入，您可以阅读以下 连接到 MySQL 并设置示例数据库 部分，以了解如何创建数据库和表。本教程将自始至终引用此示例数据库和表。

您还可以使用嵌入在此页面上的交互式终端来试验本教程中的示例查询。单击下面的Launch an Interactive Terminal!按钮开始使用。

启动交互式终端！

连接到 MySQL 并设置示例数据库

如果您的 SQL 数据库在远程服务器上运行，请从本地计算机通过 SSH 连接到您的服务器：

ssh sammy@your_server_ip

接下来，打开 MySQL 提示符，将 sammy 替换为您的 MySQL 用户帐户信息。如果您在此页面上使用嵌入式交互式终端，请注意提示时使用的密码是单词 secret：

mysql -u sammy -p

创建一个名为 caseDB 的数据库：

CREATE DATABASE caseDB;

如果数据库创建成功，您将收到以下输出：

OutputQuery OK, 1 row affected (0.01 sec)

要选择 caseDB 数据库，请运行以下 USE 语句：

USE caseDB;

OutputDatabase changed

选择数据库后，在其中创建一个表。对于本教程的示例，我们将创建一个表格，其中包含有关有史以来最畅销的十张专辑的数据。该表将包含以下六列：

music_id：显示 int 数据类型的值，并将用作表的主键，这意味着该列中的每个值都将用作其各自的唯一标识符排。
artist_name：使用 varchar 数据类型存储每个艺术家的姓名，最多 30 个字符。
album_name：使用 varchar 数据类型，同样最多 30 个字符来保存每个专辑的名称。
release_date：使用 DATE 数据类型跟踪每张专辑的发行日期，该数据类型使用 YYYY-MM-DD 日期格式。
genre_type：使用最多 25 个字符的 varchar 数据类型显示每个专辑的流派分类。
copies_sold：使用 decimal 数据类型存储以百万计销售的专辑副本总数。此列指定精度为 4，小数位数为 1，这意味着此列中的值可以有四位数字，其中一个数字位于小数点的右侧。

通过运行以下 CREATE TABLE 命令创建一个名为 top_albums 的表，其中包含这些列中的每一列：

CREATE TABLE top_albums (
music_id int, 
artist_name varchar(30),
album_name varchar(30), 
release_date DATE,
genre_type varchar(25),
copies_sold decimal(4,1),
PRIMARY KEY (music_id)
);

接下来将一些示例数据插入到空表中：

INSERT INTO top_albums
(music_id, artist_name, album_name, release_date, genre_type, copies_sold)
VALUES
(1, 'Michael Jackson', 'Thriller', '1982-11-30', 'Pop', 49.2),
(2, 'Eagles', 'Hotel California', '1976-12-08', 'Soft Rock', 31.5),
(3, 'Pink Floyd', 'The Dark Side of the Moon', '1973-03-01', 'Progressive Rock', 21.7),
(4, 'Shania Twain', 'Come On Over', '1997-11-04', 'Country', 29.6),
(5, 'AC/DC', 'Back in Black', '1980-07-25', 'Hard Rock', 29.5),
(6, 'Whitney Houston', 'The Bodyguard', '1992-11-25', 'R&B', 32.4),
(7, 'Fleetwood Mac', 'Rumours', '1977-02-04', 'Soft Rock', 27.9),
(8, 'Meat Loaf', 'Bat Out of Hell', '1977-10-11', 'Hard Rock', 21.7),
(9, 'Eagles', 'Their Greatest Hits 1971-1975', '1976-02-17', 'Country Rock', 41.2),
(10, 'Bee Gees', 'Saturday Night Fever', '1977-11-15', 'Disco', 21.6);

OutputQuery OK, 10 rows affected (0.01 sec)
Records: 10  Duplicates: 0  Warnings: 0

插入数据后，您就可以开始在 SQL 中使用 CASE 表达式了。

了解 CASE 表达式语法

CASE 表达式允许您为数据设置条件并使用与 if-then 语句类似的逻辑来搜索数据、比较值并评估它们是否与您设置的条件匹配为“真” . 以下是 CASE 表达式的一般语法示例：

CASE 表达式语法

. . .
CASE 
    WHEN condition_1 THEN outcome_1
    WHEN condition_2 THEN outcome_2
    WHEN condition_3 THEN outcome_3
    ELSE else_outcome
END 
. . .

根据您要为数据设置的条件数量，您还将在 CASE 表达式中包含以下关键字：

WHEN：此关键字根据您设置的条件或标准评估和比较您在表中的数据值。 WHEN 与典型 if-then-else 语句中的 if 相当。
THEN：如果特定值不符合条件，则此关键字过滤您可能设置的每个条件。
ELSE：如果数据值在经过每个WHEN和THEN语句后，不满足您设置的任何条件，则可以使用该关键字指定它可以归类为最终条件。
END：要成功运行 CASE 表达式并设置条件，您必须以 END 关键字结尾。

了解了 CASE 表达式结构和语法后，您就可以开始练习示例数据了。

使用 CASE 表达式

想象一下，你是一名 DJ，正在为你古怪的 Carol 姨妈的 65 岁生日庆祝活动准备一个曲目。您知道她的品味很难确定，因此您决定对有史以来最畅销的十张专辑进行一些研究，以了解您的一些音乐决定。

首先，通过运行 SELECT 和 * 符号查看您在 top_albums 表中编译的列表，以查看每列的所有数据：

SELECT * FROM top_albums;

Output+----------+-----------------+-------------------------------+--------------+------------------+-------------+
| music_id | artist_name     | album_name                    | release_date | genre_type       | copies_sold |
+----------+-----------------+-------------------------------+--------------+------------------+-------------+
|        1 | Michael Jackson | Thriller                      | 1982-11-30   | Pop              |        49.2 |
|        2 | Eagles          | Hotel California              | 1976-12-08   | Soft Rock        |        31.5 |
|        3 | Pink Floyd      | The Dark Side of the Moon     | 1973-03-01   | Progressive Rock |        21.7 |
|        4 | Shania Twain    | Come On Over                  | 1997-11-04   | Country          |        29.6 |
|        5 | AC/DC           | Back in Black                 | 1980-07-25   | Hard Rock        |        29.5 |
|        6 | Whitney Houston | The Bodyguard                 | 1992-11-25   | R&B              |        32.4 |
|        7 | Fleetwood Mac   | Rumours                       | 1977-02-04   | Soft Rock        |        27.9 |
|        8 | Meat Loaf       | Bat Out of Hell               | 1977-10-11   | Hard Rock        |        21.7 |
|        9 | Eagles          | Their Greatest Hits 1971-1975 | 1976-02-17   | Country Rock     |        41.2 |
|       10 | Bee Gees        | Saturday Night Fever          | 1977-11-15   | Disco            |        21.6 |
+----------+-----------------+-------------------------------+--------------+------------------+-------------+
10 rows in set (0.00 sec)

自从 Carol 姨妈 1957 年出生以来，她年轻时就喜欢上了很多七八十年代的热门歌曲。你知道她是流行音乐、软摇滚和迪斯科的忠实粉丝，所以你想把这些列为你的歌单上的最高优先级。

您可以使用 CASE 表达式通过在 genre_type 列下查询这些数据值来为这些特定类型设置“高优先级”条件。以下查询执行此操作，并为 CASE 表达式创建的结果列创建一个别名，将其命名为 priority。此查询还包括 artist_name、album_name 和 release_date 以获得更多上下文。不要忘记使用 END 关键字来完成完整的 CASE 表达式：

SELECT artist_name, album_name, release_date,
CASE WHEN genre_type = 'Pop' THEN 'High Priority' 
WHEN genre_type = 'Soft Rock' THEN 'High Priority'
WHEN genre_type = 'Disco' THEN 'High Priority'
END AS priority
FROM top_albums;

Output+-----------------+-------------------------------+--------------+---------------+
| artist_name     | album_name                    | release_date | priority      |
+-----------------+-------------------------------+--------------+---------------+
| Michael Jackson | Thriller                      | 1982-11-30   | High Priority |
| Eagles          | Hotel California              | 1976-12-08   | High Priority |
| Pink Floyd      | The Dark Side of the Moon     | 1973-03-01   | NULL          |
| Shania Twain    | Come On Over                  | 1997-11-04   | NULL          |
| AC/DC           | Back in Black                 | 1980-07-25   | NULL          |
| Whitney Houston | The Bodyguard                 | 1992-11-25   | NULL          |
| Fleetwood Mac   | Rumours                       | 1977-02-04   | High Priority |
| Meat Loaf       | Bat Out of Hell               | 1977-10-11   | NULL          |
| Eagles          | Their Greatest Hits 1971-1975 | 1976-02-17   | NULL          |
| Bee Gees        | Saturday Night Fever          | 1977-11-15   | High Priority |
+-----------------+-------------------------------+--------------+---------------+
10 rows in set (0.00 sec)

尽管此输出反映了您为那些 High Priority 流派类型设置的条件，但由于您遗漏了 ELSE 关键字，这会导致称为 NULL 值的未知或缺失数据值. 如果您的数据值满足您在 CASE 表达式中设置的所有条件，则 ELSE 关键字可能不是必需的，但它对于任何残差数据都很有用，因此可以将其正确分类为单个健康）状况。

对于下一个查询，编写相同的 CASE 表达式，但这次使用 ELSE 关键字设置条件。在以下示例中，ELSE 参数将 genre_type 的任何非高优先级数据值标记为“可能”：

SELECT artist_name, album_name, release_date,
CASE WHEN genre_type = 'Pop' THEN 'High Priority' 
WHEN genre_type = 'Soft Rock' THEN 'High Priority'
WHEN genre_type = 'Disco' THEN 'High Priority'
ELSE 'Maybe'
END AS priority
FROM top_albums;

[sceondary_label Output]
+-----------------+-------------------------------+--------------+---------------+
| artist_name     | album_name                    | release_date | priority      |
+-----------------+-------------------------------+--------------+---------------+
| Michael Jackson | Thriller                      | 1982-11-30   | High Priority |
| Eagles          | Hotel California              | 1976-12-08   | High Priority |
| Pink Floyd      | The Dark Side of the Moon     | 1973-03-01   | Maybe         |
| Shania Twain    | Come On Over                  | 1997-11-04   | Maybe         |
| AC/DC           | Back in Black                 | 1980-07-25   | Maybe         |
| Whitney Houston | The Bodyguard                 | 1992-11-25   | Maybe         |
| Fleetwood Mac   | Rumours                       | 1977-02-04   | High Priority |
| Meat Loaf       | Bat Out of Hell               | 1977-10-11   | Maybe         |
| Eagles          | Their Greatest Hits 1971-1975 | 1976-02-17   | Maybe         |
| Bee Gees        | Saturday Night Fever          | 1977-11-15   | High Priority |
+-----------------+-------------------------------+--------------+---------------+
10 rows in set (0.00 sec)

此输出现在更能代表您为那些具有最高优先级和没有它的专辑设置的条件。尽管这有助于优先考虑前四张专辑——Thriller、Hotel California、Rumours 和 Saturday Night Fever——但你确信需要更多种类这个歌单。但你也必须说服卡罗尔阿姨这件事。

你决定做一个小实验，并请卡罗尔阿姨拓宽她的音乐调色板并听剩下的专辑。您没有提供有关专辑的任何背景信息，并指示她如实给它们评分为“醇厚”、“有趣”或“无聊”。完成后，她会给你一份手写的成绩单。您现在拥有设置查询条件所需的信息，如下所示：

SELECT artist_name, album_name, release_date,
CASE WHEN genre_type = 'Hard Rock' THEN 'Boring' 
WHEN genre_type = 'Country Rock' THEN 'Mellow'
WHEN genre_type = 'Progressive Rock' THEN 'Fun'
WHEN genre_type = 'Country' THEN 'Fun'
WHEN genre_type = 'R&B' THEN 'Boring'
ELSE 'High Priority' 
END AS score
FROM top_albums;

Output
+-----------------+-------------------------------+--------------+---------------+
| artist_name     | album_name                    | release_date | score         |
+-----------------+-------------------------------+--------------+---------------+
| Michael Jackson | Thriller                      | 1982-11-30   | High Priority |
| Eagles          | Hotel California              | 1976-12-08   | High Priority |
| Pink Floyd      | The Dark Side of the Moon     | 1973-03-01   | Fun           |
| Shania Twain    | Come On Over                  | 1997-11-04   | Fun           |
| AC/DC           | Back in Black                 | 1980-07-25   | Boring        |
| Whitney Houston | The Bodyguard                 | 1992-11-25   | Boring        |
| Fleetwood Mac   | Rumours                       | 1977-02-04   | High Priority |
| Meat Loaf       | Bat Out of Hell               | 1977-10-11   | Boring        |
| Eagles          | Their Greatest Hits 1971-1975 | 1976-02-17   | Mellow        |
| Bee Gees        | Saturday Night Fever          | 1977-11-15   | High Priority |
+-----------------+-------------------------------+--------------+---------------+
10 rows in set (0.00 sec)

根据这个输出，Carol 姨妈似乎对新的声音持开放态度，你对她为 Pink Floyd 的得分感到惊喜。但你对她对 AC/DC、Meat Loaf 和 Whitney Houston 的优秀曲调缺乏兴趣有点失望。

如果你可以向卡罗尔阿姨展示某些专辑客观上比其他专辑更受欢迎，那么卡罗尔阿姨可能会更灵活，因此你决定引入一些数字来影响决定。事实上，这些是前十张专辑，因为它们在过去几十年里已经向歌迷出售了数百万张。因此，对于下一个查询，您将创建一个新的 CASE 表达式，该表达式根据迄今为止已售出的专辑的 copies_sold 中的数字数据设置分数。

您将使用 CASE 表达式来设置至少 3500 万张专辑销量为“最佳”、2500 万张为“优秀”、2000 万张为“好”以及低于就像下面的例子一样“平庸”：

SELECT artist_name, album_name, release_date, CASE WHEN copies_sold >35.0 THEN 'best'
WHEN copies_sold >25.0 THEN 'great'
WHEN copies_sold >20.0 THEN 'good'
ELSE 'mediocre' END AS score FROM top_albums;

Output+-----------------+-------------------------------+--------------+-------+
| artist_name     | album_name                    | release_date | score |
+-----------------+-------------------------------+--------------+-------+
| Michael Jackson | Thriller                      | 1982-11-30   | best  |
| Eagles          | Hotel California              | 1976-12-08   | great |
| Pink Floyd      | The Dark Side of the Moon     | 1973-03-01   | good  |
| Shania Twain    | Come On Over                  | 1997-11-04   | great |
| AC/DC           | Back in Black                 | 1980-07-25   | great |
| Whitney Houston | The Bodyguard                 | 1992-11-25   | great |
| Fleetwood Mac   | Rumours                       | 1977-02-04   | great |
| Meat Loaf       | Bat Out of Hell               | 1977-10-11   | good  |
| Eagles          | Their Greatest Hits 1971-1975 | 1976-02-17   | best  |
| Bee Gees        | Saturday Night Fever          | 1977-11-15   | good  |
+-----------------+-------------------------------+--------------+-------+
10 rows in set (0.00 sec)

根据这个输出，没有一张专辑被评为“平庸”，因为它们每张的销量都超过了 2000 万张。但是，根据分数，有一些专辑在其他专辑中脱颖而出。现在你可以为 Carol 姨妈提供演奏 AC/DC 或 Whitney Houston 的确凿证据，因为他们的专辑销量超过 2500 万张，使它们成为最伟大的两部音乐作品。

现在您已经了解了如何使用 CASE 表达式为各种目的以及字符和数值数据值设置条件。此外，CASE 如何使用 if-then 逻辑来比较这些值并根据您所需的条件生成响应。

结论

了解如何使用 CASE 表达式可以帮助您将数据范围缩小到您设置的任何条件。无论您是想为某些值设置不同的优先级，还是根据流行意见或数字的标准对其进行评分，它都可以灵活地满足您的需求。如果您想了解可以在结果集中处理数据值的其他方法，请查看我们的 CAST 函数和连接表达式指南。