T-SQL的楼梯:超越基本级别6:使用案例表达式和IIF函数
Gregory Larsen，2016/4/20(第一次出版:2014/04/09)
该系列
本文是楼梯系列的一部分:T-SQL的阶梯:超越基础
从他的楼梯到T-SQL DML，Gregory Larsen涵盖了更高级的t-sql语言，比如子查询。
有时，您需要编写一个TSQL语句，该语句能够根据对另一个表达式的评估返回不同的TSQL表达式。当您需要这种功能时，您可以使用CASE表达式或IIF函数来满足此需求。在本文中，我将回顾该案例和IIF语法，并向您展示案例表达式和IIF函数的示例。
理解这样表达
Transact-SQL CASE表达式允许您将条件逻辑放入您的TSQL代码中。这个条件逻辑为您提供了在TSQL语句中放置不同的代码块的方法，这些代码块可以根据条件逻辑的真实或错误的评估来执行。您可以在一个CASE表达式中放置多个条件表达式。当您的CASE子句中有多个条件表达式时，第一个计算为TRUE的表达式将是由您的TSQL语句计算的代码块。为了更好地理解案例表达式是如何工作的，我将回顾案例表达式的语法，然后通过一系列不同的示例。
格子Syntax用语
CASE表达式有两种不同的格式:简单和搜索。这两种类型的格式稍有不同，如图1所示。
图1:CASE syntax用语
通过查看图1中的CASE表达式的两种不同格式，您可以看到每种格式如何提供一种不同的方式来标识一个确定案例表达式结果的多个表达式。对于这两种情况，每个子句都执行一个布尔测试。在简单的CASE表达式中，布尔测试的左手边出现在CASE单词之后，被称为“input表达式”，而布尔测试的右边则是在单词后面，被称为“表达式”的时候。对于简单的CASE表达式，“input表达式”和“表达式”之间的运算符始终是相等的运算符。而对于搜索的CASE表达式，每个子句将包含一个“布尔表达式”。这个“布尔表达式”可以是一个简单的布尔表达式，它可以是一个运算符，也可以是一个具有许多不同条件的复杂布尔表达式。此外，搜索的CASE表达式可以使用全部的布尔运算符。
不管使用哪种CASE格式，每个子句都按照它出现的顺序进行比较。CASE表达式的结果将基于第一个计算为TRUE的子句。如果no当子句的值为TRUE时，则返回ELSE表达式。当忽略ELSE子句，而当子句的值为TRUE时，则返回NULL值。
样本数据的例子
为了使用案例表达式演示一个表，我将使用清单1中的脚本创建一个名为MyOrder的示例表。如果您愿意跟随我的示例，并在SQL Server实例上运行它们，那么可以在您选择的数据库中创建这个表。
清单1:创建示例表MyOrder
使用一个简单的案例表达式来表示何时和其他表达式
为了演示简单的CASE表达式格式如何工作，让我运行清单2中的代码。
清单2:使用ELSE表达式的简单的CASE表达式
让我先来解释一下为什么这是一个简单的例子。如果你回顾清单2中的代码后可以看到,这个词我指定表达式“(OrderDT)”,然后我跟着,通过三种不同当表达式指定每一个拥有不同的一年,从2014年开始。因为我在CASE和第一个关键字之间指定了这个表达式，它告诉SQL Server这是一个简单的CASE表达式。
当我的简单的CASE表达式被求值时，它使用相等运算符(“=”)在“年(OrderDate)”值和不同的表达式之间。因此,清单1中的代码将显示为YearType列“1年”行OrderDT年值“2014”,或将显示“2年”行OrderDT一年的将显示“2013”或者“三年级”行OrderDT年的“2012”。如果OrderDT的年份不匹配任何表达式，那么ELSE条件将显示“第4年和以后”。
当我运行清单2中的代码时，我得到了结果1中所示的输出。
结果1:运行清单2时的结果
使用一个没有其他表达式的简单的CASE表达式
让我运行清单3中的代码，它将显示当一个简单的CASE表达式没有其他子句时发生的情况。
清单3:没有ELSE子句的简单CASE表达式
清单3中的代码就像清单2中的代码，但没有其他子句。当我运行清单3中的代码时，它会产生结果2所示的结果。
结果2:运行清单的结果
通过检查结果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中的结果相同。
当表达式求值为TRUE时，返回什么表达式?
在一个单独的CASE表达式中，当表达式计算为TRUE时，可能会出现不同的情况。当这种情况发生时，SQL Server将返回与第一个表达式相关联的结果表达式。因此，当子句计算为TRUE时，当子句将控制你从CASE表达式返回的结果时。
展示我们用这样的表情来显示“200美元的秩序”OrderAmt 200美元的范围内时,“100美元的秩序”OrderAmt时100美元的范围内和“< 100美元订单“当OrderAmt小于100美元当一个OrderAmt不属于任何这些类别的分类顺序为“300美元以上的秩序”。让我们回顾一下清单5中的代码，以演示当在尝试将订单分类为这些OrderAmt_Category值时，当多个表达式对TRUE执行时，会发生什么情况。
清单5:当表达式计算为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中的代码展示了一个示例，用于在MyOrder表中进一步对订单进行分类，以确定订单是否在订单额超过200美元时使用分期支付购买。
清单7:嵌套案例语句
清单7中的代码与清单6中的代码类似。唯一不同的是，我添加了一个额外的CASE表达式，以查看MyOrder表中的订单是否使用分期支付购买选项，该选项仅允许购买超过200美元的。记住，当您嵌套CASE表达式时，SQL Server只允许您有最多10个级别的嵌套。
其他可以使用CASE表达式的地方
一直以来，我的所有示例都使用CASE表达式来创建一个结果字符串，方法是将CASE表达式放在TSQL SELECT语句的SELECT列表中。您还可以在UPDATE、DELETE和SET语句中使用CASE表达式。另外，CASE表达式可以与IN、ORDER BY和HAVING一起使用。在清单8中，我使用了一个CASE表达式嵌套于WHERE子句的案例。
清单8:在WHERE子句中使用CASE表达式
在清单8中，我只想返回MyOrder表中“Year 1”的一行订单。为了达到这个目的，我将使用与清单2中相同的，在WHERE子句中使用的CASE表达式。我将CASE表达式作为WHERE条件的左边部分，这样它就可以根据OrderDT列生成不同的“Year…”字符串。然后，我测试了从CASE表达式生成的字符串，看MyOrder表中的行，这一项的返回值是否等于“Year 1”的值，请记住，我不建议使用CASE表达式从日期列中选择日期，使用类似“Year 1”这样的字符串，还有其他更好的方法。比如使用年度函数来选择给定年份的行。我只是在这里演示了如何在WHERE子句中使用CASE语句。
使用IIF函数缩短CASE表达式
随着SQL Server 2012的引入，微软加入了IIF功能。IIF函数可以被认为是CASE语句的快捷方式。在图2中，您可以找到有关IIF函数的语法。
IIF(Boolean_expression，rue_value、false_value)
图2:IIF函数的用法
“Boolean_expression”是一个有效的布尔表达式，它等于TRUE或FALSE。当布尔表达式等同于TRUE时，就会返回“truevalue”表达式。如果布尔表达式等同于FALSE，则返回“false_value”。就像CASE表达式一样，IIF函数可以被嵌套到10个级别。
使用IIF的例子
为了演示如何使用IIF函数来替换CASE表达式，让我们来回顾一下清单9中使用CASE表达式查询的代码。
清单9:简单的CASE表达式示例
清单9中的代码只有一个单独的WHEN表达式，用于确定OrderAmt是高价还是低价的订单。如果“OrderAMT>200”的表达式返回值值为TRUE，那么OrderType值将被设置为“High $ Order”。如果表达式求值为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功能一样，它也有局限性。下面是关于案例和IIF构造的一些限制。
案例表达的局限性:
•您只能有多达10层的嵌套，以防表达式。
•无法使用case表达式来控制TSQL语句的执行流。
IIF功能限制:
•你只能有多达10层的IIF子句嵌套。
摘要
CASE表达式和IIF函数允许您将表达式逻辑放置在TSQL代码中，这将根据表达式的计算结果更改代码的结果。通过使用IIF函数和CASE表达式支持的比较表达式，您可以根据比较表达式计算为TRUE或FALSE执行不同的代码块。CASE表达式和IIF函数为您提供了编程控制，以满足您可能不具备的业务需求。
问题和答案
在本节中，您可以通过回答下列问题来回顾您对案例和IIF构造的理解。
问题1:
对于CASE表达式有两种不同的语法变体:Simple和搜索。以下两个语句最好地描述了简单和搜索的CASE表达式之间的区别(选择两个)。
a.简单的CASE语法只支持等式运算符，而搜索的CASE语法支持多个运算符
b.简单的CASE语法支持多个运算符，而搜索的CASE语法只支持相等运算符
c.简单的CASE语法有在WHEN子句之后指定的布尔表达式，而搜索的CASE语法在CASE语句后面有布尔表达式的左侧，以及WHEN子句之后布尔表达式的右侧。
d .简单的CASE语法在CASE语句后面是布尔表达式的左边，在WHEN子句后面是布尔表达式的右侧，而搜索的CASE表达式在WHEN子句后面有它的布尔表达式
问题2:
如果CASE表达式有多个子句，值为TRUE，那么哪个子句执行呢?
a.最后一个WHEN子句计算为TRUE时的表达式。
b .当计算为TRUE时，第一个WHEN子句的表达式将被执行。
c.所有计算结果为TRUE的子句的表达式都被执行。
d.另一个表达式被执行
问题3:
一个CASE表达式或IIF函数有多少嵌套级别?
答:8
b . 10
c . 16
d . 32
答案:
问题1:
答案是a和d。一个简单的CASE语句只能使用相等运算符，而搜索的CASE表达式可以处理多个运算符，以及复杂的布尔表达式。另外，简单的CASE语法在单词CASE后面有等式运算符的左边部分，而等号右边的右边部分则是相等运算符的右边部分。在WHEN子句之后，搜索的CASE表达式必须完成布尔操作(左手部分，操作符，右手部分)
问题2:
正确的答案是b。如果多个WHEN子句值为TRUE，那么SQL Server只执行第一个WHEN子句的TRUE。当被评估为TRUE的子句被跳过时，所有其他的子句都将被省略。
问题3:
正确的答案是b . CASE表达式和IIF函数只支持多达10个嵌套级别。
这篇文章是通往t - sql的楼梯的一部分:除了基本的楼梯
扫一扫
138
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
