DATABASE SYSTEM CONCEPTS4.26

该系列

 

本文是“Stairway系列：T-SQL的基石：超越基础”的一部分

 

从他的Stairway到T-SQL DML之后，Gregory Larsen涵盖了T-SQL语言的更多高级方面，例如子查询。

 

 

有时您需要编写一个TSQL语句，该语句能够根据另一个表达式的求值返回不同的TSQL表达式。 当您需要这种功能时，您可以使用CASE表达式或IIF函数来满足此要求。 在本文中，我将回顾CASE和IIF语法，并向您展示CASE表达式和IIF函数的示例。

 

理解CASE表达式

 

Transact-SQL CASE表达式允许您在TSQL代码中放置条件逻辑。 这种条件逻辑为您提供了一种方法，可以根据条件逻辑的TRUE或FALSE评估来执行TSQL语句中可以执行的不同代码块。 您可以在单个CASE表达式中放置多个条件表达式。 当您的CASE子句中有多个条件表达式时，第一个表达式的值为TRUE将是由您的TSQL语句评估的代码块。 为了更好地理解CASE表达式的工作原理，我将回顾CASE表达式的语法，然后通过一些不同的例子。

 

CASE表达式语法

 

CASE表达式有两种不同的格式：Simple和Searched。 每种类型的格式都略有不同，如图1所示。

 

 

图1：CASE表达式语法

 

通过查看图1中CASE表达式的两种不同格式，您可以看到每种格式如何提供一种不同的方式来标识决定CASE表达式结果的多个表达式之一。对于两种类型的CASE，都会为每个WHEN子句执行布尔测试。使用简单CASE表达式，布尔测试的左手侧出现在CASE单词的后面，称为“输入表达式”，布尔测试的右侧恰好在WHEN之后，称为“when表达式”。使用Simple CASE表达式，“input_expression”和“when_expression”之间的运算符总是等于运算符。而搜索CASE表达式的每个WHEN子句将包含一个“布尔表达式”。该“布尔表达式”可以是具有单个运算符的简单布尔表达式，也可以是具有许多不同条件的复杂布尔表达式。另外，搜索到的CASE表达式可以使用全套布尔运算符。

 

无论使用哪种CASE格式，每个WHEN子句按其出现的顺序进行比较。 CASE表达式的结果将基于评估为TRUE的第一个WHEN子句。如果没有WHEN子句评估为TRUE，则返回ELSE表达式。当ELSE子句被省略并且WHEN子句的计算结果为TRUE时，则返回NULL值。

示例的示例数据

 

为了让一个表使用CASE表达式演示，我将使用清单1中的脚本创建一个名为MyOrder的示例表。 如果您想跟随我的示例并在SQL Server实例上运行它们，则可以在您选择的数据库中创建此表。

清单1：创建示例表MyOrder

 

在WHEN和ELSE表达式中使用简单的CASE表达式

 

为了演示简单的CASE表达式格式如何工作，让我运行清单2中的代码。

清单2：使用ELSE表达式的简单CASE表达式

 

让我先来谈谈为什么这是一个简单的CASE表达式。如果您查看清单2中的代码，您可以在CASE之后指定表达式“YEAR（OrderDT）”后面看到，然后我从三个不同的WHEN表达式开始，每个表达式都指定了不同的年份，从2014年开始。因为我指定CASE和第一个WHEN关键字之间的表达式，这告诉SQL Server这是一个简单的CASE表达式。

 

当我的简单CASE表达式被评估时，它使用“YEAR（OrderDate）”值和不同WHEN表达式之间的等号运算符（“=”）。因此，清单1中的代码将针对OrderDT年值为“2014”的行的YearType列显示“Year 1”，或者它将显示OrderDT年为“2013”​​的行的“Year 2”或它将显示OrderDT年份为“2012”的行的“Year 3”。如果OrderDT的年份与任何WHEN表达式不匹配，则ELSE条件将显示“第4年及以后”。

 

当我运行清单2中的代码时，得到了Result 1中显示的输出

使用没有ELSE表达式的简单CASE表达式

 

让我运行清单3中的代码，该代码将显示简单CASE表达式没有ELSE子句时会发生什么。

 

清单3：没有ELSE子句的简单CASE表达式

 

清单3中的代码就像清单2中的代码一样，但没有ELSE子句。 当我运行清单3中的代码时，它会生成结果2中显示的结果。

 

结果2：运行清单3时的结果

 

通过查看Result 2中的输出，您可以看到，如果MyOrder表中OrderDT的年份不符合任何WHEN子句条件，SQL Server将为该行的YearType值显示“NULL”。

使用搜索CASE表达式

 

在简单的CASE表达式中，WHEN表达式基于相等运算符进行评估。 通过搜索CASE表达式，您可以使用其他运算符，而CASE表达式语法稍有不同。 为了演示这个，我们来看看清单4中的代码

清单4：搜索CASE表达式

 

如果您查看清单4中的代码，您可以看到WHEN子句紧跟在CASE子句之后，两个子句之间没有文本。 这告诉SQL Server这个搜索的CASE表达式。 还要注意每个WHEN子句后面的布尔表达式。 正如您所看到的，并非所有这些布尔表达式都使用了相等运算符，最后一个WHEN表达式使用小于（“<”）运算符。 清单4中的CASE表达式在逻辑上与清单2中的CASE表达式相同。因此，当我运行清单4中的代码时，它会产生与结果1中所示相同的结果。

如果多个WHEN表达式计算为TRUE，会返回什么表达式？

 

在单个CASE表达式中，可能会出现不同的WHEN表达式计算为TRUE的情况。发生这种情况时，SQL Server将返回与计算结果为true的第一个WHEN表达式关联的结果表达式。因此，如果多个WHEN子句评估为TRUE，那么WHEN子句的顺序将控制从CASE表达式返回的结果。

 

为了演示这一点，让我们使用CASE表达式来显示OrderAmt在200美元范围内时的“200美元订单”，当OrderAmt在100美元范围内时显示为“100美元订单”，当订单金额小于100美元时显示“<100美元订单”当OrderAmt不属于任何这些类别时，则将订单归类为“300美元及以上订单”。让我们回顾清单5中的代码，以演示当尝试将订单分类到其中一个OrderAmt_Category值时，多个WHEN表达式计算为TRUE时会发生什么情况

清单5：多个WHEN表达式评估为TRUE的示例

 

当我运行清单5中的代码时，得到Result 3中的输出。

 

结果3：运行清单5时的结果

 

通过查看结果3中的结果，您可以看到每个订单都被报告为200或300以上的订单，并且我们知道这是不正确的。发生这种情况是因为我只使用小于（“<”）运算符来简化Orders，将CASE表达式中的多个WHEN表达式求值为TRUE。 WHEN子句的排序不允许返回正确的表达式。

 

通过重新排列我的WHEN子句，我可以得到我想要的结果。清单6中的代码与清单5中的代码相同，但是我重新命令了WHEN子句来正确分类我的订单。

清单6：与清单5类似的代码，但WHEN子句的顺序不同

 

当我运行清单5中的代码时，得到Result 4中的输出。

 

结果4：运行清单6时的结果

 

通过查看结果4中的输出，您可以看到，通过更改WHEN表达式的顺序，我获得了每个订单的正确结果。

嵌套CASE表达式

 

有时您可能需要执行额外的测试，以使用CASE表达式进一步对数据进行分类。发生这种情况时，您可以使用嵌套的CASE表达式。清单7中的代码显示了一个嵌套CASE表达式的例子，以进一步对MyOrder表中的订单进行分类，以确定订单是否在订单超过200美元时使用Layaway值购买。

清单7：嵌套CASE语句

 

清单7中的代码与清单6中的代码类似。唯一的区别是我添加了一个额外的CASE表达式，以查看MyOrder表中的订单是否使用Layaway选项购买的，该选项仅在超过$ 200的购买时才被允许。 请注意，嵌套CASE表达式时，SQL Server只允许您有多达10个嵌套级别。

 

其他可以使用CASE表达式的地方

 

到目前为止，我的所有示例都使用CASE表达式通过将CASE表达式放置在TSQL SELECT语句的选择列表中来创建结果字符串。您也可以在UPDATE，DELETE和SET语句中使用CASE表达式。此外 ，CASE表达式可以与IN，WHERE，ORDER BY和HAVING子句结合使用。在清单8中，我使用了一个表达WHERE子句的CASE。

 

清单8：在WHERE子句中使用CASE表达式

 

在清单8中，我只想从MyOrder表中返回“Year 1”中的行的订单。为了实现这一点，我在WHERE子句中放置了与清单2中相同的CASE表达式。我使用CASE表达式作为WHERE条件的左侧部分，以便根据OrderDT列生成不同的“Year ...”字符串。然后我测试了从CASE表达式生成的字符串，看它是否等于值“年1”，当它是从MyOrder表返回的行时。请记住，如果还有其他更好的方法，例如使用YEAR函数为给定年份选择行，我不建议使用CASE表达式使用类似“Year 1”的sting从日期列中选择日期。我只是在这里演示了如何在WHERE子句中使用CASE语句。

 

使用IIF函数快速切换CASE表达式

 

随着SQL Server 2012的推出，微软添加了IIF功能。 IIF功能可被视为CASE声明的捷径。在图2中，您可以找到IIF函数的语法。

 

图2：IIF功能的语法

 

“布尔表达式”是一个有效的布尔表达式，等同于TRUE或FALSE。 当布尔表达式等同于TRUE值时，将执行“true_value”表达式。 如果布尔表达式等于FALSE，则执行“false_value”。 就像CASE表达式一样，IIF函数可以嵌套到10个级别。

 

使用IIF的例子

 

为了演示如何使用IIF函数替换CASE表达式，让我们回顾一下在清单9中使用搜索的CASE表达式的代码

清单9：简单CASE表达式示例

 

清单9中的代码只有一个WHEN表达式，用于确定OrderAmt是高位还是低位美元顺序。 如果WHEN表达式“OrderAMT> 200”评估为TRUE，那么OrderType值设置为“High $ Order”。 如果WHEN表达式的计算结果为FALSE，则为OrderType值设置“Low $ Order”。

 

使用IIF函数而不是CASE表达式的重写代码可以在清单10中找到。

清单10：使用IIF函数的示例

 

通过查看清单10，您可以看到为什么IIF函数被视为CASE表达式的简写版本。 CASE字被替换为“IIF（”字符串，“THEN”替换为逗号，“ELSE”替换为逗号，单词“END”替换为“）”。 当布尔表达式“OrderAmt> 200”为TRUE时，将显示值“High $ Order”。 当布尔表达式'OrderAmt> 200“被评估为FALSE时，则显示”Low $ Order“。 如果您运行清单9和10中的代码，您会看到它们都产生完全相同的输出。

 

嵌套IIF功能的示例

 

就像CASE表达式SQL Server允许您嵌套IIF函数。 在清单11中是嵌套IIF函数的示例。

清单11：嵌套IIF函数的示例

 

在这个例子中，你可以看到我已经多次使用了IIF函数。 每增加一个用于IIF功能的“真值”或“假值”。 清单11中的代码与清单7中使用嵌套CASE表达式的代码相同。

 

限制

 

与大多数TSQL功能一样，这是有限制的。 以下是有关CASE和IIF结构的一些限制。

 

CASE表达限制：

•在CASE表达式中最多只能有10层嵌套。

•CASE表达式不能用于控制TSQL语句的执行流程。

 

IIF功能限制：

•您最多只能有10层嵌套IIF条款。

概要

 

CASE表达式和IIF函数允许您将表达式逻辑放置在TSQL代码中，这将根据表达式的评估结果改变代码的结果。 通过使用IIF函数和CASE表达式支持的比较表达式，您可以根据比较表达式计算结果为TRUE还是FALSE来执行不同的代码块。 CASE表达式和IIF函数为您提供程序控制，以满足您可能不具备的业务需求。

 

问题和答案

 

在本节中，您可以通过回答以下问题来复习使用CASE和IIF构造的理解程度。

问题1：

 

CASE表达式有两种不同的语法变体：Simple和Searched。 以下哪两条语句最好地描述了简单搜索CASE表达式（选择两个）之间的区别。

一个。 简单CASE语法只支持相等运算符，而Searched CASE语法支持多个运算符

湾 简单CASE语法支持多个运算符，而Searched CASE语法仅支持相等运算符

C。 简单CASE语法在WHEN子句后面指定了其布尔表达式，而搜索CASE语法在CASE语句后面有布尔表达式的左侧，在WHEN子句后面布尔表达式的右侧。

d。 简单CASE语法在CASE语句后面布尔表达式的左侧，在WHEN子句后面布尔表达式的右侧，而搜索CASE表达式在WHEN子句后面具有布尔表达式

问题2：

 

如果CASE表达式具有多个评估为TRUE的WHEN子句，哪个THEN / ELSE子句被执行？

一个。 执行最后一个计算为TRUE的WHEN子句的THEN表达式。

湾 执行第一个计算结果为TRUE的WHEN子句的THEN表达式。

C。 执行所有THEN表达式的WHEN子句，其值为TRUE。

d。 ELSE表达式被执行

 

问题3：

 

CASE表达式或IIF函数有多少嵌套层次？

一个。8

湾10

C。16

d。32

 

回答：

 

问题1：

 

答案是a和d。 一个简单的CASE语句只能使用相等运算符，而Searled CASE表达式可以处理多个运算符以及复杂的布尔表达式。 另外，简单CASE语法在单词CASE之后有相等运算符的左边部分，在WHEN之后有相等运算符的右边部分。 搜索的CASE表达式必须在WHEN子句之后立即完成布尔运算（左边部分，运算符，右边部分）

 

问题2：

 

正确答案是b。 如果多个WHEN子句评估为TRUE，那么SQL Server仅执行第一个WHEN子句的THEN部分，其结果为TRUE。 所有其他THEN子句的任何其他WHEN子句评估为TRUE都会被跳过。

 

问题3：

 

正确答案是b。 CASE表达式和IIF函数最多只支持10个嵌套级别。

翻译自《DATABASE SYSTEM CONCEPTS》  Abraham Silberschatz   Henry F.Korth   S.Sudarshan