MySQL基础入门之Case语句用法实例

目录

• 引言
• CASE 的语法
• CASE 语句在 MySQL 中是如何工作的？
• 带有 SELECT 和 ORDER BY 子句的 CASE 语句
• 具有聚合函数的 CASE 语句
• 补充：case行转列用法
• 总结

引言

MySQL CASE 是一个 MySQL 语句查询关键字，它定义了处理循环概念以执行条件集并使用 IF ELSE 返回匹配案例的方式。 MySQL 中的 CASE 是一种控制语句，它验证条件案例集，并在第一个案例满足 else 值时显示值并退出循环。

如果没有找到 TRUE 且语句没有 ELSE 部分或值，则 CASE 返回 NULL。基本上，CASE 语句类似于 IF THEN ELSE 逻辑循环语句。在 MySQL 服务器上运行时，它会读取条件表达式，当表达式大小写匹配时，nit 会显示结果。之后，它会进一步停止执行。

CASE 的语法

下面的语法定义了 CASE 语句 SQL 查询结构：

CASE
WHEN cond1 THEN value1
WHEN cond2 THEN value2
WHEN condN THEN valueN
ELSE value
END;

• Cond1,cond2,…..,condN：表示在 CASE 语句列表中需要评估的条件。
• Value1, value2, .....,valueN：表示满足条件时需要显示的各个值。
• Value：表示 else 部分满足时显示的值。

这部分允许在 MySQL 的查询中添加逻辑 CASE 语句。为了生成有效的语句，我们在任何地方使用带有 WHERE SELECT 和 ORDER BY 等子句的 CASE 语句。您可以通过如下所示的语法进行评估：

SELECT column1,column2,
CASE
WHEN cond1 THEN value1
WHEN cond2 THEN value2
WHEN condN THEN valueN
ELSE value
END
FROM TableName;

CASE 语句在 MySQL 中是如何工作的？

CASE 语句包含两个过程：一个是 Simple CASE，另一个是 Searched CASE。我们使用 CASE 语句根据匹配条件提供结果值，使用逻辑控制方法与 SQL 子句（如 SELECT、WHERE 和 ORDER BY）一起为 SQL 查询提供结果值。

假设，下面的查询解释了 CASE 逻辑部分，其中我们提到了一个像 Student 的表，其中包含字段 StudentName、State、City，那么查询将被写为：

SELECT StudentName, State, City FROM Students ORDER BY (
CASE
WHEN State IS NULL THEN City
ELSE State
END);

从这里我们将知道，当我们在 SELECT 查询中应用 CASE 语句来获取满足特定 case 条件的特定值时。在上图中，我们将 CASE 语句放在 State 和 City 列上，如果 State 列包含 NULL 值，则查询显示 City 列值，如果 case 语句无效或找不到值，则显示部分值，即 State返回列值。在这里，我们使用 ORDER BY 子句获取了应用 CASE 的学生、州和城市的名称，以对结果行进行排序。

在 Simple CASE 中，CASE 的列值与 WHEN 子句中的条件语句值匹配以实现等价性，然后在语法中的一个之后生成结果值。但如果没有相等的值，则返回 ELSE 部分值（如果提供）。

您不应在 WHEN 子句值中使用 NULL，因为如果执行，逻辑部分将为 NULL = NULL，结果为 FALSE。

现在对于 Search CASE，它遵循与 Simple CASE 相同的逻辑过程，但搜索案例的一部分使其执行起来有所不同。在这里，满足 MySQL 数据类型的值的 CASE 是基于它用于的上下文。例如，如果语句中使用了字符串上下文，则结果值将采用相同的数据类型，字符串。此外，如果 CASE 条件表达式使用数字上下文，则返回的值将是整数、小数或实数值数据类型。

带有 SELECT 和 ORDER BY 子句的 CASE 语句

创建数据库表

CREATE TABLE Students(StudentID int, StudentName varchar(255), State varchar(255), City varchar(255));

往数据库插入新值

INSERT INTO Students (StudentID, StudentName, State, City) VALUES ('01', 'Wade', 'UK', 'London');
INSERT INTO Students (StudentID, StudentName, State, City) VALUES ('02', 'Vivi', 'US', 'NewYork');
INSERT INTO Students (StudentID, StudentName, State, City) VALUES ('03', 'Kyrie', 'CN', 'SH');
INSERT INTO Students (StudentID, StudentName, State, City) VALUES ('04', 'James', '', 'Cleveland ');

查询数据

SELECT * FROM Students;

带有 SELECT 查询的 CASE 语句

SELECT StudentName, State, City FROM Students ORDER BY (
CASE
WHEN State IS NULL THEN City
ELSE State
END);

具有聚合函数的 CASE 语句

我们将使用 CASE 语句和 SUM() MySQL 函数按订单状态从 Orders 表中计算总销售额。

创建数据库表

CREATE TABLE Orders (OrderID int,SalesID int, OrderStatus varchar(255) );

插入数据

INSERT INTO Orders (OrderID, SalesID,OrderStatus) VALUES ('10', '001', 'Shipped');
INSERT INTO Orders (OrderID, SalesID,OrderStatus) VALUES ('11', '002', 'Shipped');
INSERT INTO Orders (OrderID, SalesID,OrderStatus) VALUES ('12', '003', 'Not Developed');
INSERT INTO Orders (OrderID, SalesID,OrderStatus) VALUES ('13', '004', 'Success');
INSERT INTO Orders (OrderID, SalesID,OrderStatus) VALUES ('14', '005', 'In Process');
INSERT INTO Orders (OrderID, SalesID,OrderStatus) VALUES ('15', '006', 'Cancelled');

SELECT 语句显示表数据

SELECT * FROM Orders;

得到的数据如下：

10    1    Shipped
11    2    Shipped
12    3    Not Developed
13    4    Success
14    5    In Process
15    6    Cancelled

在 SQL SELECT 查询中使用 SUM() 和 COUNT 作为聚合函数的 CASE 语句

SELECT
SUM(CASE
WHEN OrderStatus = 'Success' THEN 1
ELSE 0
END) AS 'Success Count',
SUM(CASE
WHEN OrderStatus = 'On Hold' THEN 1
ELSE 0
END) AS 'Hold Count',
SUM(CASE
WHEN OrderStatus = 'In Process' THEN 1
ELSE 0
END) AS 'Processing',
SUM(CASE
WHEN OrderStatus = 'Shipped' THEN 1
ELSE 0
END) AS 'Shipping count',
SUM(CASE
WHEN OrderStatus = 'Cancelled' THEN 1
ELSE 0
END) AS 'Cancellation Count',
SUM(CASE
WHEN OrderStatus = 'Not Developed' THEN 1
ELSE 0
END) AS 'Not Developed count',
COUNT(*) AS 'Sum Total'
FROM
Orders;

运行结果：

补充：case行转列用法

现有表数据如下图所示（性别列中，1表示男，2表示女）：

需统计各年级男女人数。虽然表中数据已有各年级的男女人数，但是还是不够一目了然。接下来使用case语句查询如下：

SELECT
    class,
    sum( CASE WHEN sex = '1' THEN population ELSE 0 END ) AS cnt_m,
    sum( CASE WHEN sex = '2' THEN population ELSE 0 END ) AS cnt_f 
FROM
    `school_test` 
GROUP BY
    class;

结果如下：

这是最经典的case行转列用法。

总结

MySQL CASE 语句允许对 SQL 查询执行 IF ELSE 逻辑，以检查条件语句并从数据库表中获取所需的结果集或值。

我们只能将 CASE 语句与存储过程、存储事件、函数和触发器一起使用。它用于将条件表达式与一系列不同的值进行比较，这些值提供了相应的结果，该结果包含取决于查询中使用的上下文的数据类型。

因此，我们可以说 MySQL 中的 CASE 语句使查询代码更加高效和可读。

参看文章：

MySQL CASE Statement