Skip to main content

第 13 篇 CASE 表达式与控制流函数

imodouAbout 7 minMySQL

《MySQL 入门教程》第 13 篇 CASE 表达式与控制流函数

MySQL 为 SQL 语句提供了类似 if-then-else 的逻辑处理功能，可以基于不同的条件返回不同的结果。这些功能包括 CASE 表达式和一些控制流函数。

13.1 CASE 表达式

CASE 表达式支持两种形式：简单 CASE 表达式和搜索 CASE 表达式。

13.1.1 简单 CASE 表达式

简单 CASE 表达式的语法如下：

CASE expression
  WHEN value1 THEN result1
  WHEN value2 THEN result2
  ...
  [ELSE default_result]
END

表达式的计算过程如下图所示：

image-20230106151729782
image-20230106151729782

首先计算 expression 的值；然后依次与 WHEN 列表中的值（value1，value2，…）进行比较，找到第一个相等的值并返回对应的结果（result1，result2，…）；如果没有找到相等的值，返回 ELSE 中的默认结果；如果此时没有指定 ELSE，返回 NULL 值。

以下语句使用简单 CASE 表达式将员工的部门编号显示为相应的部门名称：

select emp_name as "员工姓名",
       case dept_id
         when 1 then '行政管理部'
         when 2 then '人力资源部'
         when 3 then '财务部'
         when 4 then '研发部'
         when 5 then '销售部'
         when 6 then '保卫部'
         else '其他部门'
       end as "所在部门"
from employee;
员工姓名|所在部门 |
------|--------|
刘备   |行政管理部|
关羽   |行政管理部|
张飞   |行政管理部|
...
邓芝   |销售部   |
简雍   |销售部   |
孙乾   |销售部   |

首先，判断部门编号是否等于 1，等于就显示为“行政管理部”；否则，如果部门编号等于 2， 显示为“人力资源部”；依次类推；如果部门编号不等于 1 到 6 中的任何值，显示为“其他部门”。

CASE 表达式的一个常见应用就是实现表的行列转换。创建以下学生成绩表：

-- 创建成绩表 t_case，sname 为学生姓名，cname 为课程名称，score 为考试成绩
CREATE TABLE t_case(sname varchar(10), cname varchar(10), score int);

-- 插入测试数据
INSERT INTO t_case(sname, cname, score) VALUES ('张三', '语文', 80);
INSERT INTO t_case(sname, cname, score) VALUES ('李四', '语文', 77);
INSERT INTO t_case(sname, cname, score) VALUES ('王五', '语文', 91);
INSERT INTO t_case(sname, cname, score) VALUES ('张三', '数学', 85);
INSERT INTO t_case(sname, cname, score) VALUES ('李四', '数学', 90);
INSERT INTO t_case(sname, cname, score) VALUES ('王五', '数学', 60);
INSERT INTO t_case(sname, cname, score) VALUES ('张三', '英语', 81);
INSERT INTO t_case(sname, cname, score) VALUES ('李四', '英语', 69);
INSERT INTO t_case(sname, cname, score) VALUES ('王五', '英语', 82);

该表中的数据如下：

select *
from t_case;
sname |cname  |score|
------|-------|-----|
张三   |语文   |   80|
李四   |语文   |   77|
王五   |语文   |   91|
张三   |数学   |   85|
李四   |数学   |   90|
王五   |数学   |   60|
张三   |英语   |   81|
李四   |英语   |   69|
王五   |英语   |   82|

接下来我们利用 CASE 表达式将其转换为按列显示的形式：

select sname,
       sum(case cname when '语文' then score else 0 end) as "语文",
       sum(case cname when '数学' then score else 0 end) as "数学",
       sum(case cname when '英语' then score else 0 end) as "英语"
from t_case
group by sname;
sname |语文|数学|英语|
------|---|---|---|
张三   | 80| 85| 81|
李四   | 77| 90| 69|
王五   | 91| 60| 82|

第一个 CASE 表达式用于获取学生的语文成绩，cname 等于“语文”就返回考试成绩，不是“语文”就记为 0 分；第二个和第三个 CASE 表达式分别用于获取数学和英语成绩。然后，使用 SUM 汇总函数和 GROUP BY 分组操作将每个学生的成绩合并成一条记录。

简单 CASE 表达式使用的是等值比较（=），只能处理简单的逻辑。如果想要进行复杂的逻辑处理，例如根据考试成绩评出优秀、良好、及格等，或者判断表达式的值是否为空，就需要使用更加强大的搜索 CASE 表达式。

13.1.2 搜索 CASE 表达式

搜索 CASE 表达式的语法如下：

CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ...
  [ELSE default_result]
END

表达式的计算过程如下图所示：

image-20230106151715988
image-20230106151715988

按照顺序依次计算每个分支中的条件（condition1，condition2，…），找到第一个结果为真的分支并返回相应的结果（result1，result2，…）；如果没有任何条件为真，返回 ELSE 中的默认结果；如果此时没有指定 ELSE，返回 NULL 值。

所有的简单 CASE 表达式都可以替换为等价的搜索 CASE 表达式。我们可以将上一节的示例改写如下：

select emp_name as "员工姓名",
       case 
         when dept_id = 1 then '行政管理部'
         when dept_id = 2 then '人力资源部'
         when dept_id = 3 then '财务部'
         when dept_id = 4 then '研发部'
         when dept_id = 5 then '销售部'
         when dept_id = 6 then '保卫部'
         else '其他部门'
       end as "所在部门"
from employee;

首先，判断部门编号等于 1 是否成立（为真），成立就显示为“行政管理部”；否则，判断部门编号等于 2 是否成立， 成立就显示为“人力资源部”；依次类推；如果部门编号不等于 1 到 6 中的任何值，显示为“其他部门”。

以下查询按照考试分数对成绩进行评价：

select sname as "学生姓名",
       cname as "学科名称",
       case 
         when score >= 90 then '优秀'
         when score >= 80 then '良好'
         when score >= 70 then '中等'
         when score >= 60 then '及格'
         else '不及格'
       end as "考试成绩"
from t_case;
学生姓名|学科名称|考试成绩|
-------|------|-------|
张三   |语文   |良好    |
李四   |语文   |中等    |
王五   |语文   |优秀    |
张三   |数学   |良好    |
李四   |数学   |优秀    |
王五   |数学   |及格    |
张三   |英语   |良好    |
李四   |英语   |及格    |
王五   |英语   |良好    |

CASE 表达式除了可以用于 SELECT 列表，也可以出现在其他子句中，例如 WHERE、GROUP BY、ORDER BY 等。以下语句使用 CASE 表达式实现了第 10 篇 数据排序中的自定义排序：

select emp_name,
       case emp_name
         when '刘备' then 1
         when '关羽' then 2
         when '张飞' then 3
         else 99
       end as num
from employee
where dept_id = 1
order by case emp_name
           when '刘备' then 1
           when '关羽' then 2
           when '张飞' then 3
           else 99
         end;
 emp_name|num|
---------|---|
刘备      |  1|
关羽      |  2|
张飞      |  3|

通过 CASE 表达式将“刘备”编号为 1，“关羽”编号为 2，“张飞”编号为 3，其他人员编号为 99。

13.2 IF 函数

IF(expr1,expr2,expr3) 函数的处理逻辑如下：如果表达式 expr1 的结果为 True，也就是不等于 0 也不为 NULL，函数返回表达式 expr2 的值；否则，函数返回表达式 expr3 的值。

以下语句实现了与上文中 CASE 表达式相同的行转列功能：

select sname,
       sum(if(cname = '语文', score, 0)) as "语文",
       sum(if(cname = '数学', score, 0)) as "数学",
       sum(if(cname = '英语', score, 0)) as "英语"
from t_case
group by sname;

以下语句将 bonus 为空的数据显示为 0：

select emp_name, if(bonus, bonus, 0) as bonus
from employee;
emp_name |bonus   |
---------|--------|
刘备      |10000.00|
关羽      |10000.00|
张飞      |10000.00|
...
邓芝      |       0|
简雍      |       0|
孙乾      |       0|

13.3 IFNULL 函数

IFNULL(expr1,expr2) 函数的处理逻辑如下：如果表达式 expr1 不为空，返回 expr1 的值；否则，返回表达式 expr2 的值。

以下语句同样可以将 bonus 为空的数据显示为 0：

select emp_name, ifnull(bonus, 0) as bonus
from employee;
emp_name |bonus   |
---------|--------|
刘备      |10000.00|
关羽      |10000.00|
张飞      |10000.00|
...
邓芝      |       0|
简雍      |       0|
孙乾      |       0|

13.4 COALESCE 函数

IFNULL 函数只能处理两个参数，COALESCE(expr1,...) 函数可以返回参数中第一个非空的值，相当于嵌套的 IFNULL 函数。例如：

select coalesce(null, null, 1), coalesce(null, null, null);
coalesce(null, null, 1)|coalesce(null, null, null)|
-----------------------|--------------------------|
                      1|                          |

显然，我们也可以使用 COALESCE 函数将 bonus 为空的数据显示为 0。

13.5 NULLIF 函数

NULLIF(expr1,expr2) 函数的处理逻辑如下：如果表达式 expr1 和 expr2 相等，返回 NULL；否则，返回 expr1 的值。NULLIF 函数可以使用等价的 CASE 表达式进行表示：

CASE
  WHEN expr1 = expr2 THEN NULL
  ELSE expr1
END

NULLIF 函数的一个常见用途是防止除零错误：

-- 除零错误
select 1 / 0; 

select 1 / nullif(0 , 0);

第一个语句会产生除零错误（MySQL 可能只产生警告而不是错误）；第二个语句返回 NULL。

Default footer
Copyright © 2024 imodou