数据库第八周作业

楼梯到T-SQL：超越基本6级：使用case表达式和IIF函数
格雷戈里·拉森著
2016/04/20(首次出版：2014/04/09)
系列
本文是楼梯系列的一部分：楼梯到T-SQL：超越基础从楼梯到T-SQLDML，GregoryLarsen介绍了T-SQL语言的更高级方面，比如子查询。有时，您需要编写一个能够基于对另一个表达式的计算返回不同TSQL表达式的TSQL语句。当您需要这种功能时，您可以使用CASE表达式或IIF函数来满足这一要求。在本文中，我将回顾案例和IIF语法，并向您展示如何使用case表达式和IIF功能的示例。
理解案例表达式
Transact-SQL CASE表达式允许您在TSQL代码中放置条件逻辑。这个条件逻辑为您提供了一种在TSQL语句中放置不同代码块的方法，这些代码块可以根据条件逻辑的真值或错误值执行。可以将多个条件表达式放置在单个案例表达式中。当CASE子句中有多个条件表达式时，计算为true的第一个表达式将是由TSQL语句计算的代码块。为了更好地理解案例表达式是如何工作的，我将回顾一下CASE表达式的语法，然后再看一些不同的示例。
案例表达式语法案例表达式有两种不同的格式：简单和搜索。这些类型中的每一种都有稍微不同的格式，如图1所示。

图1：CASE表达式语法
通过查看图1中案例表达式的两种不同格式，您可以看到每种格式如何提供一种不同的方法来识别确定案例表达式结果的多个表达式中的一个。对于这两种类型的情况，将对每个WHERE子句执行布尔测试。对于简单的CASE表达式，布尔测试的左手边出现在大小写单词后面，称为“put_Expresture”，而布尔测试的右手边则紧跟在单词WHILE之后，被称为“WHILE表达式”。对于简单的CASE表达式，“INPUT_EXPRECT”和“WHERE_EXPRECT”之间的运算符始终是相等运算符。然而，对于搜索的CASE表达式，每个WHERE子句将包含一个“布尔表达式”。这个“布尔表达式”可以是一个带有单个运算符的简单布尔表达式，也可以是一个具有许多不同条件的复杂布尔表达式。此外，搜索的案例表达式可以使用整组布尔运算符。无论使用哪种情况格式，都会按照出现的顺序对每个WHERE子句进行比较。CASE表达式的结果将基于计算结果为true的第一个WHERE子句。如果NOWILE子句的计算结果为true，则返回Other表达式。如果省略了ASER子句，而不使用NOWERWILE子句计算为true，则返回一个NULL值。
示例数据
为了使用case表达式演示一个表，我将使用清单1中的脚本创建一个名为MyOrder的示例表。如果您想跟随我的示例并在您的SQLServer实例上运行它们，您可以在您选择的数据库中创建此表。

清单1：创建示例表MyOrder
使用简单的CASE表达式和时间表达式以及其他表达式为了演示简单的case表达式格式如何工作，让我运行清单2中的代码。

清单2：简单的案例表达式和其他表达式
让我首先谈谈为什么这是一个简单的案例表达。如果您查看清单2中的代码，您可以看到在单词case之后，我指定了表达式“YOYE(OrderDT)”，然后是三个不同的表达式，每个表达式都指定了一个不同的年份，从2014开始。因为我在case和第一个WHERE关键字之间指定了这个表达式，所以这告诉SQLServer这是一个简单的case表达式。在计算简单的CASE表达式时，它在“YOYE(OrderDate)”值和不同的WHERE表达式之间使用相等运算符(“=”)。因此，清单1中的代码将为OrderDT年值为“2014”的行显示“年份1”列，或者对于OrderDT年为“2013”的行显示“年份2”，或者对于OrderDT年为“2012”的行显示“年份3”。如果OrderDT的年份与任何WHERE表达式不匹配，则Other条件将显示“第4年及以后”。当我运行清单2中的代码时，结果1中显示了输出。

结果1：运行清单2时的结果
使用没有其他表达式的简单案例表达式让我运行清单3中的代码，它将显示当一个简单的case表达式没有OSE子句时会发生什么。

清单3：不带Other子句的简单案例表达式
清单3中的代码与清单2中的代码类似，但没有OSE子句。当我运行清单3中的代码时，它会产生结果2中所示的结果。

结果2：运行清单3时的结果
通过查看结果2中的输出，您可以看到，当MyOrder表中的OrderDT年不满足任何WHERE子句条件时，SQLServer将为该行的YERYType值显示“NULL”。
使用搜索的案例表达式
在简单的情况表达式中，基于相等运算符计算时间表达式。对于搜索的案例表达式，您可以使用其他运算符，而case表达式语法略有不同。为了演示这一点，让我们看一下清单4中的代码。

清单4：搜索的案例表达式
如果查看清单4中的代码，可以看到WHERE子句直接跟随在case子句之后，两个子句之间没有文本。这将告诉SQLServer这是一个已搜索的案例表达式。还请注意每个WHERE子句后面的布尔表达式。正如您所看到的，并不是所有的布尔表达式都使用相等运算符，最后一个表达式使用的是小于(<“”)运算符。清单4中的case表达式在逻辑上与清单2中的case表达式相同。因此，当我运行清单4中的代码时，它产生的结果与结果1中所示的结果相同。
当表达式计算为真时，如果多个表达式返回哪个表达式？在单个案例表达式中，表达式的计算结果可能有所不同。当这种情况发生时，SQLServer将返回与第一个计算为true的表达式关联的结果表达式。因此，如果多个WHERE子句的计算结果为true，则WHERE子句的顺序将控制从CASE表达式返回的结果。为了演示这一点，让我们使用CASE表达式来显示“200美元订单”(当OrderAmt在$200范围内)、“100美元订单”(当OrderAmt在$100范围内时)和“<100美元订单”(当OrderAmt小于$100时)，当OrderAmt不属于这些类别时，则将该订单分类为a“300美元及以上订单”。让我们回顾一下清单5中的代码，演示当多个表达式在尝试将订单分类为这些OrderAmt_class值之一时，当多个表达式计算为true时会发生什么。

清单5：多个表达式计算为true的示例
当我运行清单5中的代码时，将得到结果3中的输出。
结果3：运行清单5时的结果
通过查看结果3中的结果，您可以看到每个订单都报告为200或300以上的订单，我们知道这是不正确的。之所以会出现这种情况，是因为我只使用了小于(<“”)操作符来简单地对顺序进行分类，这会导致在表达式中求值为true时会出现多个顺序。WHERE子句的排序不允许返回正确的表达式。通过重新排序我的时间从句，我可以得到我想要的结果。清单6中的代码与清单5相同，但我重新排序了WHERE子句，以正确地对订单进行分类。

清单7：嵌套case语句
清单7中的代码类似于清单6中的代码。唯一的区别是我添加了一个额外的CASE表达式，以查看MyOrder表中的订单是否是使用Layaway选项购买的，该选项只允许在200美元以上的采购中购买。请记住，在嵌套CASE表达式时，SQL Server只允许嵌套多达10个级别的嵌套。可以使用案例表达式的其他地方到目前为止，我的所有示例都使用case表达式创建结果字符串，方法是将case表达式放在TSQL SELECT语句的SELECT列表中。还可以在UPDATE、DELETE和SET语句中使用CASE表达式。此外，CASE表达式还可以与IN、WHERE、ORDERBY和HAVING子句一起使用。在清单8中，我使用了一个表示WHERE子句的情况。

清单8：在WHERE子句中使用CASE表达式
在清单8中，我只想从MyOrder表中为“第一年”中的行返回一个订单。为了实现这一点，我在WHERE子句中放置了与清单2中使用的相同的CASE表达式。我使用case表达式作为WHERE条件的左边部分，这样它就会产生不同的“…”。基于OrderDT列的字符串。然后，我测试从case表达式中生成的字符串，看看它是否等于值“年份1”，当它是行时，将从MyOrder表返回它。请记住，我不建议使用CASE表达式从日期列中选择日期，使用诸如“年份1”这样的圈套，因为有其他更好的方法，比如使用YOYE函数来选择给定年份的行。我在这里这样做只是为了演示如何在WHERE子句中使用CASE语句。
使用IIF函数缩短用例表达式随着SQLServer 2012的引入，微软增加了IIF功能。IIF函数可以被视为CASE语句的快捷方式。在图2中，您可以找到IIF函数的语法。

图2：IIF函数的语法
“布尔表达式”是一个有效的布尔表达式，等于true或false。当布尔表达式等于真值时，则执行“true_value”表达式。如果布尔表达式等于false，则执行“false_value”。与CASE表达式一样，IIF函数可以嵌套到10个级别。
使用IIF的示例为了演示如何使用IIF函数替换CASE表达式，让我们回顾一下清单9中使用搜索过的CASE表达式的代码。

清单9：简单的案例表达式示例
清单9中的代码只有一个WHERE表达式，用于确定OrderAmt是高还是低美元顺序。如果时间表达式“OrderAMT>200”计算为true，则OrderType值设置为“High$Order”。如果WHERE表达式的计算结果为false，则为OrderType值设置“low$order”。使用IIF函数而不是case表达式的重写代码可以在清单10中找到。

清单11：IIF函数嵌套示例
在这个例子中，您可以看到我多次使用了IIF函数。每一个附加的值要么用于IIF函数的“真值”，要么用于“假值”。清单11中的代码相当于清单7中使用嵌套案例表达式的代码。局限性与大多数TSQL功能一样，也存在限制。下面是一些关于案例和IIF构造的限制。案例表达的限制：在情况表达式中，只能有最多10个层次的嵌套。Case表达式不能用于控制TSQL语句的执行流程。IIF职能限制：你只能有多达10个层次的嵌套IIF条款。
局限性
与大多数TSQL功能一样，也存在限制。下面是一些关于案例和IIF构造的限制。
案例表达的限制：在情况表达式中，只能有最多10个层次的嵌套。
Case表达式不能用于控制TSQL语句的执行流程。
IIF职能限制：你只能有多达10个层次的嵌套IIF条款。
摘要
CASE表达式和IIF函数允许您将表达式逻辑放置在TSQL代码中，这将根据表达式的计算结果更改代码的结果。通过使用IIF函数支持的比较表达式和CASE表达式，可以根据比较表达式的计算结果为true还是false来执行不同的代码块。CASE表达式和IIF函数为您提供编程控制，以满足您可能没有的业务需求。
问题与答案
在本节中，您可以通过回答以下问题来回顾如何使用案例和IIF构造来理解。
问题1：这个案例表达式有两个不同的语法变体：简单语法和搜索语法。下面哪两个语句最能描述简单的案例表达式和搜索的案例表达式(选取两个)之间的区别。简单案例语法只支持相等运算符，而搜索的案例语法支持多个运算符。简单案例语法支持多个运算符，而搜索的案例语法只支持相等运算符。简单CASE语法的布尔表达式在WHILE子句之后指定，而搜索的CASE语法在CASE语句之后具有布尔表达式的左侧，而布尔表达式的右侧则在WHILE子句之后。简单的CASE语法在case语句之后具有布尔表达式的左侧，在WHILE子句之后具有布尔表达式的右侧，而搜索的CASE表达式在WHERE子句之后有它的布尔表达式。
问题2：如果CASE表达式有多个计算为true的WHERE子句，那么执行哪个/OSE子句？执行计算为true的最后一个WHERE子句的然后表达式。执行计算为true的第一个WHERE子句的然后表达式。然后执行计算为true的WHERE子句的所有表达式。执行Other表达式。
问题3：一个案例表达式或IIF函数可以有多少嵌套级别？八十十六三十二答案：
问题1：答案是a和d。一个简单的case语句只能使用相等操作符，而搜索的case表达式可以处理多个操作符以及复杂的布尔表达式。此外，简单的CASE语法在单词case后面有相等操作符的左手部分，在单词WHILE后面有相等操作符的右边部分。搜索的案例表达式必须在WHERE子句之后的右边完成布尔运算(左手部分、操作符、右手部分)。
问题2：正确的答案是b。如果多个WHERE子句的计算结果为true，则SQLServer只执行第一个WILE子句中计算为true的RIT部分。当计算为true的子句被跳过时，所有其他的子句都会被跳过。
问题3：正确的答案是b。CASE表达式和IIF函数只支持最多10个嵌套级别。
本文是T-SQL楼梯的一部分：超越基本楼梯注册我们的RSS提要，并得到通知，一旦我们发布一个新的层次在楼梯上！