第八周翻译（Stairway to T-SQL: Beyond The Basics Level 6: Using the CASE Expression and IIF Function）



通往t - sql的方法:超越基本的6级阶梯:使用CASE表达式和IIF函数

这篇文章是楼梯系列的一部分:通往t -sql的楼梯:超越基础。

从他的阶梯到T - SQL DML,GregoryLarsen包括了t - sql语言的更高级的方面，如子查询。

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

理解CASE表达

Transact - sql CASE表达式允许你在TSQL代码中放置条件逻辑。这个条件逻辑为你提供了一种操作，可以将不同的代码块放置在你的TSQL语句中，这取决于对条件逻辑的正确或错误的判断。你可以在一个CASE表达式中放多个条件表达式。当你在CASE子句中有多个条件表达式时，计算为TRUE的第一个表达式将是由TSQL语句计算的代码块。为了更好地理解CASE表达式的工作方式，我将回顾一下CASE表达式的语法，然后再看一些不同的例子。

 CASE表达式语法

CASE表达式有两种不同的格式:简单和搜索。每种类型都有略微不同的格式，如图1所示。

 

 图1:CASE表达式语法

通过查看图1中的CASE表达式的两种不同的格式，你可以看到每个格式都提供了一种不同的方法来确定多个表达式中的一个，这些表达式决定了CASE表达式的结果。在这两种情况下，每个WHEN子句都执行布尔测试。在简单的CASE表达式中，布尔测试的左手边出现在CASE word之后，被称为“input_expression”，而布尔测试的右手边则是“WHEN”，被称为“WHEN表达式”。使用简单的CASE表达式，在“input_expression”和“何时表达式”之间的操作符始终是相等的运算符。而在搜索的CASE表达式中，每个WHEN子句将包含一个“Boolean_expression”。这个“Boolean_expression”可以是一个简单的布尔表达式，一个操作符，或者一个复杂的布尔表达式，有许多不同的条件。此外，搜索的CASE表达式可以使用完整的布尔运算符集。

不管使用哪种情况格式，每个WHEN子句都按其出现的顺序进行比较。CASE表达式的结果将基于第一个WHEN子句计算为TRUE，或者返回ELSE表达式。当ELSE子句被省略时，当子句计算为TRUE时，则返回空值。

样本数据的例子

为了使用CASE表达式演示一个表，我使用清单1中的脚本创建一个名为MyOrder的示例表。如果你要跟着我的示例并在你的SQL服务器实例上运行，你可以在你选择的数据库中创建此表

清单1:创建样例表MyOrder

使用一个简单的CASE表达式和其他表达式

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

 

清单2:用ELSE表达式进行简单的CASE表达式

我先说说为什么这是一个简单的案例表达。如果你回顾清单2中的代码，你可以看到在单词CASE之后，我指定了表达式“YEAR(OrderDT)”，然后我遵循了三种不同的表达式，每一个都有不同的年份，从2014开始。因为我在CASE和第一个关键字之间指定了这个表达式，它让SQL Server知道这是一个简单的CASE表达式。

当我的简单的CASE表达式被评估时，它使用“YEAR(OrderDate)”值和不同的表达式之间的等号运算符(“=”)。因此,清单1中的代码将显示为YearType列“1年”行OrderDT年值“2014”,或将显示“2年”行OrderDT一年的将显示“2013”或者“三年级”行OrderDT年的“2012”。如果OrderDT的年份不匹配任何表达式，那么ELSE条件将显示“year 4及beyond”。

当我运行清单2中的代码时，将得到结果1所示的输出。

 

结果1:例2运行的结果

使用一个没有其他表达式的简单的CASE表达式

运行清单3中的代码，它将显示当一个简单的CASE表达式没有其他子句时发生的情况。

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

清单3中的代码就像清单2中的代码，但没有其他子句。当我运行例3中的代码时，它会产生结果2所示的结果。

 

结果2:运行例3的结果

通过检查结果2中的输出，可以看到，当MyOrder表中的OrderDT年不满足任何when子句条件时，SQL Server显示该行的YearType值为“NULL”。

使用一个搜索的CASE表达式

在简单的情况下，表达式是基于等式运算符来计算的。通过搜索的CASE表达式，你可以使用其他操作符，而CASE表达式语法略有不同。为了演示这一点，我们来看看清单4中的代码。

 

清单4：搜索CASE表达式

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

 

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

 

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

为了证明这一点，让我们使用CASE，来显示“200美元的订单”OrderAmt200美元的范围内时,“100美元的订单”OrderAmt时100美元的范围内和“< 100美元订单“当OrderAmt小于100美元，当OrderAmt小于100 $当OrderAmt不属于任何这些类别时，则将订单归类为“ 300美元及以上订单 ”。让我们回顾一下代码5中的代码，显示多个时WHEN表达式的计算结果为TRUE试图订单归类到这些之一时，会发生什么OrderAmt_ 范畴上ÿ值。 

 

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

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

 

结果3:运行清单5的结果

通过回顾结果3，你可以看到每个订单都被报告为200或300以上的订单，我们知道这是不正确的。之所以发生这种情况，是因为我只使用了小于(“<”)运算符来简化排序，当表达式在我的CASE表达式中值为TRUE时，会导致多个命令。WHEN从句的顺序不允许返回正确的表达式。

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

 

 

例6:类似例5的代码，但是当子句的顺序不同时

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

 

 

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

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

嵌套CASE表达式

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

 

 

例7:嵌套CASE语句

例7中的代码清单6中的代码类似。唯一的区别是，我添加了一个额外的CASE表达式，以查看MyOrder表中的订单是否使用了Layaway选项购买，该选项仅允许购买超过200美元。请记住，当您的nest CASE表达式SQL服务器只允许您有多达10级的嵌套。

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

到目前为止，我的所有示例都使用CASE表达式，通过将CASE表达式放置在TSQL select语句的select列表中来创建一个结果字符串。你还可以在UPDATE、DELETE和SET语句中使用CASE表达式。另外，CASE表达式可以与in、WHERE、ORDER BY和HAVING子句一起使用。在清单8中，我使用了一个表示WHERE子句的案例。

 

例8:在WHERE子句中使用CASE表达式

在例8中，我只希望从MyOrder表中返回“Year 1”中的行。为了实现这一点，我将相同的CASE表达式放在WHERE子句中使用的清单2中。我用条件表达式的左边部分,所以它会产生不同的“…”基于OrderDT列字符串。然后我测试了从CASE表达式生成的字符串，看它是否等于“第一年”的值，当它是一行的时候，将从MyOrder表返回。请记住，我不建议使用一个CASE表达式，使用像“Year 1”这样的“sting”来从日期列中选择日期，这时还有其他更好的方法，比如使用Year函数来选择给定年份的行。我只是在这里演示了如何在WHERE子句中使用CASE语句。

 

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

随着SQL Server 2012的推出，微软增加了IIF功能。IIF函数可以被认为是CASE语句的快捷方式。在图2中，你可以找到IIF函数的语法。

 

图2:IIF函数的语法

“Boolean_expression”是一个有效的布尔表达式，它等于TRUE或FALSE。当布尔表达式等价于一个TRUE值时，执行“true_value”表达式。如果布尔表达式等于FALSE，则执行“虚值”。与CASE表达式一样，IIF函数可以嵌套到10级。

使用IIF的例子

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

 

清单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时显示值“高$ Order”。当布尔表达式“OrderAmt >200”被评估为FALSE时，显示“低$ Order”。如果您运行清单9和10中的代码，您将看到它们都产生完全相同的输出。

嵌套IIF函数的示例

就像CASE表达式SQLServer允许你嵌套IIF函数。清单11是嵌套IIF函数的一个示例。

 

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

在这个示例中，您可以看到我多次使用了IIF函数。每一个额外的一个被用在“真值”或“假值”的IIF函数。清单11中的代码与清单7中使用嵌套CASE表达式的代码相当。

限制

与大多数TSQL功能一样，它也有局限性。下面是关于案例和IIF构造的一些限制。

案例表达的局限性:

1、您只能有多达10级的嵌套在情况表达式。

2、不能使用CASE表达式来控制TSQL语句的执行流。

IIF功能限制:

您只能有多达10层的IIF子句嵌套。

概要

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

问题和答案

在本节中，您可以通过回答下列问题来回顾您对案例和IIF构造的理解。

问题1:

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

 

简单CASE语法只支持相等运算符，而Searched CASE语法支持多个运算符 简单CASE语法支持多个运算符，而Searched CASE语法仅支持相等运算符 简单CASE语法在WHEN子句之后指定了其布尔表达式，而Searled CASE语法在CASE语句后面有布尔表达式的左侧，在WHEN子句后面布尔表达式的右侧。 简单CASE语法在CASE语句后面布尔表达式的左侧，在WHEN子句后面布尔表达式的右侧，而搜索CASE表达式在WHEN子句后面具有布尔表达式

问题2：

如果CASE表达式有多个子句，值为TRUE，那么哪个子句执行呢?

 

a、最后一个WHEN子句的表达式是执行的。

b、然后执行计算为TRUE的第一个WHEN子句的表达式。

c、然后执行计算为TRUE的WHEN子句的所有表达式。

d、另一个表达式被执行

 

问题3：

一个CASE表达式或IIF函数有多少嵌套级别?

 

a、8个

b、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个嵌套级别。