SQL中CASE表达式怎么用

这篇文章主要介绍了SQL中CASE表达式怎么用，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。

首先我们来学习一下基本的写法，CASE 表达式有简单 CASE 表达式

（simple case expression）和搜索 CASE 表达式（searched case

expression）两种写法，它们分别如下所示。

这两种写法的执行结果是相同的，“sex”列（字段）如果是 '1' ，那

么结果为男；如果是 '2' ，那么结果为女。简单 CASE 表达式正如其

名，写法简单，但能实现的事情比较有限。

我们在编写 SQL 语句的时候需要注意，在发现为真的 WHEN 子句

时，CASE 表达式的真假值判断就会中止，而剩余的 WHEN 子句会被

忽略。为了避免引起不必要的混乱，使用 WHEN 子句时要注意条件的

排他性.

此外，使用 CASE 表达式的时候，还需要注意以下几点。

注意事项 1：统一各分支返回的数据类型

    虽然这一点无需多言，但这里还是要强调一下：一定要注意 CASE 表

达式里各个分支返回的数据类型是否一致。某个分支返回字符型，而

其他分支返回数值型的写法是不正确的。

注意事项 2：不要忘了写 END

    使用 CASE 表达式的时候，最容易出现的语法错误是忘记写 END 。虽

然忘记写时程序会返回比较容易理解的错误消息，不算多么致命的错

误。但是，感觉自己写得没问题，而执行时却出错的情况大多是由这

个原因引起的，所以请一定注意一下。

注意事项 3：养成写 ELSE 子句的习惯

    与 END 不同，ELSE 子句是可选的，不写也不会出错。不写 ELSE 子

句时，CASE 表达式的执行结果是 NULL 。但是不写可能会造成“语法

没有错误，结果却不对”这种不易追查原因的麻烦，所以最好明确地

写上 ELSE 子句（即便是在结果可以为 NULL 的情况下）。养成这样

的习惯后，我们从代码上就可以清楚地看到这种条件下会生成 NULL

，而且将来代码有修改时也能减少失误。

将已有编号方式转换为新的方式并统计

在进行非定制化统计时，我们经常会遇到将已有编号方式转换为另外

一种便于分析的方式并进行统计的需求。

例如，现在有一张按照“‘1：北海道’、‘2：青森’、……、‘47：冲

绳’”这种编号方式来统计都道府县 人口的表，我们需要以东北、关

东、九州等地区为单位来分组，并统计人口数量。具体来说，就是统

计下表 PopTbl 中的内容，得出如上表“统计结果”所示的结果。

这里的关键在于将 SELECT 子句里的 CASE 表达式复制到 GROUP BY

子句里。需要注意的是，如果对转换前的列“pref_name ”进行 GROUP

BY ，就得不到正确的结果（因为这并不会引起语法错误，所以容易

被忽视）。

同样地，也可以将数值按照适当的级别进行分类统计。例如，要按人

口数量等级（pop_class ）查询都道府县个数的时候，就可以像下

面这样写 SQL 语句。

这个技巧非常好用。不过，必须在 SELECT 子句和 GROUP BY 子句这

两处写一样的 CASE 表达式，这有点儿麻烦。后期需要修改的时候，

很容易发生只改了这一处而忘掉改另一处的失误。

所以，如果我们可以像下面这样写，那就方便多了。

没错，这里的 GROUP BY 子句使用的正是 SELECT 子句里定义的列的

别称——district 。但是严格来说，这种写法是违反标准 SQL 的规

则的。因为 GROUP BY 子句比 SELECT 语句先执行，所以在 GROUP

BY 子句中引用在 SELECT 子句里定义的别称是不被允许的。事实

上，在 Oracle、DB2、SQL Server 等数据库里采用这种写法时就会出

错。

不过也有支持这种 SQL 语句的数据库，例如在 PostgreSQL 和

MySQL 中，这个查询语句就可以顺利执行。这是因为，这些数据库

在执行查询语句时，会先对 SELECT 子句里的列表进行扫描，并对列

进行计算。不过因为这是违反标准的写法，所以这里不强烈推荐大家

使用。但是，这样写出来的 SQL 语句确实非常简洁，而且可读性也

很好。

用一条 SQL 语句进行不同条件的统计

进行不同条件的统计是 CASE 表达式的著名用法之一。例如，我们需

要往存储各县人口数量的表 PopTbl 里添加上“性别”列，然后求按性

别、县名汇总的人数。具体来说，就是统计表 PopTbl2 中的数据，然

后求出如表“统计结果”所示的结果。

上面这段代码所做的是，分别统计每个县的“男性”（即 '1' ）人数

和“女性”（即 '2' ）人数。也就是说，这里是将“行结构”的数据转换

成了“列结构”的数据。除了 SUM ，COUNT 、AVG 等聚合函数也都可以

用于将行结构的数据转换成列结构的数据。

这个技巧可贵的地方在于，它能将 SQL 的查询结果转换为二维表的

格式。如果只是简单地用 GROUP BY 进行聚合，那么查询后必须通过

宿主语言或者 Excel 等应用程序将结果的格式转换一下，才能使之成

为交叉表。看上面的执行结果会发现，此时输出的已经是侧栏为县

名、表头为性别的交叉表了。在制作统计表时，这个功能非常方便。

如果用一句话来形容这个技巧，可以这样说：

新手用 WHERE 子句进行条件分支，高手用 SELECT 子句进行条件分

支。

在 UPDATE 语句里进行条件分支

        下面思考一下这样一种需求：以某数值型的列的当前值为判断对象，

将其更新成别的值。这里的问题是，此时 UPDATE 操作的条件会有多

个分支。例如，我们通过下面这样一张公司人事部的员工工资信息表

Salaries 来看一下这种情况。

假设现在需要根据以下条件对该表的数据进行更新。

01. 对当前工资为 30 万日元以上的员工，降薪 10%。

02. 对当前工资为 25 万日元以上且不满 28 万日元的员工，加薪

20%。

乍一看，分别执行下面两个 UPDATE 操作好像就可以做到，但这样的

结果却是不正确的。

我们来分析一下不正确的原因。例如这里有一个员工，当前工资是

30 万日元，按“条件 1”执行 UPDATE 操作后，工资会被更新为 27 万

日元，但继续按“条件 2”执行 UPDATE 操作后，工资又会被更新为

32.4 万日元。

这样的结果当然并非人事部所愿。员工相田的工资必须被准确地降为

27 万日元。问题在于，第一次的 UPDATE 操作执行后，“当前工资”发

生了变化，如果还拿它当作第二次 UPDATE 的判定条件，结果就会不

准确。然而，即使将两条 SQL 语句的执行顺序颠倒一下，当前工资

为 27 万日元的员工，其工资的更新结果也会出现问题。为了避免这

些问题，准确地表达出可恶的人事部长的意图，可以像下面这样用

CASE 表达式来写 SQL。

SQL 语句最后一行的 ELSE salary 非常重要，必须

写上。因为如果没有它，条件 1 和条件 2 都不满足的员工的工资就会

被更新成 NULL 。这一点与 CASE 表达式的设计有关，在刚开始介绍

CASE 表达式的时候我们就已经了解到，如果 CASE 表达式里没有明

确指定 ELSE 子句，执行结果会被默认地处理成 ELSE NULL 。现在

大家明白笔者最开始强调使用 CASE 表达式时要习惯性地写上 ELSE

子句的理由了吧？

这个技巧的应用范围很广。例如，可以用它简单地完成主键值调换这

种繁重的工作。通常，当我们想调换主键值 a 和 b 时，需要将主键值

临时转换成某个中间值。使用这种方法时需要执行 3 次 UPDATE 操

作，但是如果使用 CASE 表达式，1 次就可以做到。

显而易见，这条 SQL 语句按照“如果是 a 则更新为 b ，如果是 b 则更

新为 a ”这样的条件分支进行了 UPDATE 操作。

CASE 表达式的一大优势在于能够判断表达式。也就是说，在 CASE 表达式里，我们可以使用 BETWEEN 、LIKE和 < 、> 等便利的谓词组合，以及能嵌套子查询的 IN 和 EXISTS 谓词。因此，CASE 表达式具有非常强大的表达能力。

如下所示，这里有一张资格培训学校的课程一览表和一张管理每个月

所设课程的表。

我们要用这两张表来生成下面这样的交叉表，以便于一目了然地知道

每个月开设的课程。

我们需要做的是，检查表 OpenCourses 中的各月里有表 CourseMaster

中的哪些课程。这个匹配条件可以用 CASE 表达式来写。

使用IN

使用EXISTS

这样的查询没有进行聚合，因此也不需要排序，月份增加的时候仅修

改 SELECT 子句就可以了，扩展性比较好。

无论使用 IN 还是 EXISTS ，得到的结果是一样的，但从性能方面来

说， EXISTS 更好。通过 EXISTS 进行的子查询能够用到“month,

course_id ”这样的主键索引，因此尤其是当表 OpenCourses 里数据

比较多的时候更有优势。

在 CASE 表达式中使用聚合函数

接下来介绍一下稍微高级的用法。这个用法乍一看可能让人觉得像是

语法错误，实际上却并非如此。我们来看一道例题，假设这里有一张

显示了学生及其加入的社团的一览表。如表 StudentClub 所示，这张

表的主键是“学号、社团 ID”，存储了学生和社团之间多对多的关系

有的学生同时加入了多个社团（如学号为 100、200 的学生），有的

学生只加入了某一个社团（如学号为 300、400、500 的学生）。对于

加入了多个社团的学生，我们通过将其“主社团标志”列设置为 Y 或

者 N 来表明哪一个社团是他的主社团；对于只加入了一个社团的学

生，我们将其“主社团标志”列设置为 N。

接下来，我们按照下面的条件查询这张表里的数据。

01. 获取只加入了一个社团的学生的社团 ID。

02. 获取加入了多个社团的学生的主社团 ID。

很容易想到的办法是，针对两个条件分别写 SQL 语句来查询。要想

知道学生“是否加入了多个社团”，我们需要用 HAVING 子句对聚合结

果进行判断。

这样做也能得到正确的结果，但需要写多条 SQL 语句。而如果使用

CASE 表达式，下面这一条 SQL 语句就可以了。

这条 SQL 语句在 CASE 表达式里使用了聚合函数，又在聚合函数里使

用了 CASE 表达式。这种嵌套的写法让人有点眼花缭乱，其主要目的

是用 CASE WHEN COUNT(*) = 1 …… ELSE ……. 这样的 CASE 表达式

来表示“只加入了一个社团还是加入了多个社团”这样的条件分支。我

们在初学 SQL 的时候，都学过对聚合结果进行条件判断时要用

HAVING 子句，但从这道例题可以看到，在 SELECT 语句里使用 CASE

表达式也可以完成同样的工作，这种写法比较新颖。如果用一句话来

形容这个技巧，可以这样说：

新手用 HAVING 子句进行条件分支，高手用 SELECT 子句进行条件分

支。

通过这道例题我们可以明白：CASE 表达式用在 SELECT 子句里时，

既可以写在聚合函数内部，也可以写在聚合函数外部。这种高度自由

的写法正是 CASE 表达式的魅力所在。

作为表达式，CASE 表达式在执行时会被判定为一个固定值，因此它

可以写在聚合函数内部；也正因为它是表达式，所以还可以写在

SELECE 子句、GROUP BY 子句、WHERE 子句、ORDER BY 子句里。简

单点说，在能写列名和常量的地方，通常都可以写 CASE 表达式。

感谢你能够认真阅读完这篇文章，希望小编分享的“SQL中CASE表达式怎么用”这篇文章对大家有帮助，同时也希望大家多多支持亿速云，关注亿速云行业资讯频道，更多相关知识等着你来学习!