通往T-SQL的阶梯：超越基础的第六级：使用CASE表达式和IIF函数
来自 Gregory Larsen，2016/04/20 (第一次出版：2014/04/09)
系列
这篇文章是阶梯系列的一部分:通往T-SQL的阶梯：超越基础
从他的阶梯到T-SQL DML，Gregory Larsen涵盖了更多的高级方面的T-SQL语言，例如子查询。
有时你需要编写一个可以根据另一个表达式的评估返回不同的TSQL表达式的单个TSQL语句。当你需要这种功能时，你可以使用CASE表达式或IIF函数来满足此要求。在本文中，我将回顾CASE和IIF语法，并向你展示CASE表达式和IIF函数的示例。
理解CASE表达
Transact-SQL CASE表达式允许你在TSQL代码中放置条件逻辑。这个条件逻辑为你提供了一种在TSQL语句中放置不同代码块的方法，该语句可以根据条件逻辑的TRUE或FALSE评估来执行。你可以在单个CASE表达式中放置多个条件表达式。当CASE子句中有多个条件表达式时，第一个计算结果为TRUE的表达式将是由TSQL语句评估的代码块。为了更好地了解CASE表达式的工作原理，我将回顾一下CASE表达式的语法，然后浏览一些不同的例子。
CASE表达式语法
CASE表达式有两种不同的格式：简单和搜索。 每种类型的格式都有轻微的不同，如图1所示。
图1：CASE表达式语法
通过查看图1中CASE表达式的两种不同格式，你可以看到每种格式如何提供不同的方法来标识确定CASE表达式结果的多个表达式之一。对于两种类型的CASE，对每个WHEN子句执行布尔测试。使用简单CASE表达式，布尔测试的左侧出现在CASE单词之后，称为“input_expression”，右侧的“WHEN”表示右侧，称为“when expression”。使用简单CASE表达式，“input_expression”和“when_expression”之间的运算符始终是相等运算符。而搜索到的CASE表达式，每个WHEN子句将包含一个“Boolean_expression”。这个“Boolean_expression”可以是一个带有单个运算符的简单布尔表达式，也可以是具有许多不同条件的复杂布尔表达式。另外，搜索到的CASE表达式可以使用完整的布尔运算符集。
无论使用哪种CASE格式，每个WHEN子句按其出现顺序进行比较。 CASE表达式的结果将基于评估为TRUE的第一个WHEN子句。如果没有WHEN子句求值为TRUE，则返回ELSE表达式。当ELSE子句被省略且WHEN子句的计算结果为TRUE时，将返回NULL值。
示例数据样本
为了有一个表来演示使用CASE表达式，我将使用清单1中的脚本创建一个名为MyOrder的示例表。如果你想遵循我的示例并在SQL Server实例上运行它们，你可以在选择的数据库中创建此表。
清单1：创建示例表MyOrder
使用简单的CASE表达式与WHEN和ELSE表达式
为了演示简单的CASE表达式格式如何工作，让我运行清单2中的代码。
清单2：使用ELSE表达式的简单CASE表达式
让我先谈一谈为什么这是一个简单的CASE表达。如果你查看清单2中的代码，你可以看到紧跟在CASE字之后，我指定了表达式“YEAR（OrderDT）”，然后我按照三个不同的WHEN表达式，每个具有不同的年份指定，从2014开始。因为我指定CASE和第一个WHEN关键字之间的表达式告诉SQL Server这是一个简单的CASE表达式。
当我简单的CASE表达式被评估时，它使用“YEAR（OrderDate）”值和不同的WHEN表达式之间的等号运算符（“=”）。因此，清单1中的代码将为OrderType列显示“Year 1”，其中OrderDT年值为“2014”，否则对于OrderDT年份为“2013”的行将显示“Year 2”，或者将为OrderDT年份为“2012”的行显示“Year 3”。如果OrderDT的年份与WHEN表达式不匹配，则ELSE条件将显示“Year 4 and beyond”。
当我运行清单2中的代码时，我得到结果1中显示的输出。
结果1：运行清单2时的结果
使用没有ELSE表达式的简单CASE表达式
让我运行清单3中的代码，它将显示一个Simple CASE表达式没有ELSE子句时会发生什么。
清单3：没有ELSE子句的简单CASE表达式
清单3中的代码就像清单2中的代码，但没有ELSE子句。 当我运行清单3中的代码时，会生成结果2中显示的结果。
结果2：运行清单3时的结果
通过查看结果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将返回与第一个WHEN表达式关联的结果表达式，该WHEN表达式计算结果为true。因此，如果多个WHEN子句评估为TRUE，则WHEN子句的顺序将控制从CASE表达式返回的结果。
为了证明这一点，当OrderAmt在$ 200范围内时，我们使用CASE表达式显示“200美元订单”，当OrderAmt在$ 100范围内时，“100美元订单”，当OrderAmt小于$ 100时，“100美元订单”当OrderAmt不属于这些类别时，将该订单分类为“300美元及以上订单”。我们来看看清单5中的代码，以演示当尝试将订单分类到其中一个OrderAmt_Category值时，当多个WHEN表达式求值为TRUE时会发生什么。
清单5：多个WHEN表达式求值为TRUE的示例
当我运行清单5中的代码时，我得到Result 3中的输出。
结果3：运行清单5时的结果
通过查看结果3中的结果，你可以看到每个订单都被报告为200或300及以上的订单，我们知道这是不正确的。 这是因为我仅使用少于（“<”）运算符来简单地对在CASE表达式中导致多个WHEN表达式求值为TRUE的Orders进行分类。 WHEN子句的排序不允许返回正确的表达式。
通过重新排序我的WHEN子句，我可以得到我想要的结果。 清单6中的代码与清单5相同，但是我重新命令WHEN子句正确地分类了我的订单。
清单6：与清单5类似的代码，但WHEN子句的顺序不同
当我运行清单5中的代码时，我得到结果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表达式生成的字符串，以查看它是否等于值“Year 1”，当它是从MyOrder表返回的行。请记住，当有其他更好的方法，如使用YEAR功能选择给定年份的行时，不建议使用CASE表达式从日期列中选择日期。我只在这里说明如何在WHERE子句中使用CASE语句。
使用IIF功能快速切换CASE表达式
随着SQL Server 2012的推出，微软增加了IIF功能。 IIF函数可以被认为是CASE语句的快捷方式。在图2中，你可以找到IIF函数的语法。
图2：IIF功能的语法
“Boolean_expression”是一个有效的布尔表达式，相当于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时，显示“低$订单”。 如果运行清单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表达式（Pick 2）之间的区别。
a.简单CASE语法仅支持等式运算符，而Searched CASE语法支持多个运算符
b.简单CASE语法支持多个运算符，而Searched CASE语法仅支持等式运算符
c.简单CASE语法在WHEN子句之后指定了布尔表达式，而Searched CASE语法在CASE语句之后具有布尔表达式的左侧，WHEN子句后面的布尔表达式的右侧。
d。简单CASE语法在CASE语句之后具有布尔表达式的左侧，WHEN子句后面的布尔表达式的右侧，而Searched CASE表达式在WHEN子句之后具有其布尔表达式
问题2：
如果CASE表达式有多个WHEN子句评估为TRUE，执行THEN / ELSE子句？
a.执行评估为TRUE的最后一个WHEN子句的THEN表达式。
b.执行计算为TRUE的第一个WHEN子句的THEN表达式。
c.执行评估为TRUE的WHEN子句的所有THEN表达式。
d.执行THE ELSE表达式
问题3：
CASE表达式或IIF函数有多少嵌套级别？
a.8
b.10
c.16
d.32
回答
问题1：
答案是a和d。简单的CASE语句只能使用相等运算符，而Searched CASE表达式可以处理多个运算符以及复杂的布尔表达式。另外，简单CASE语法具有紧靠单词CASE之后的等式运算符的左手部分，并且等于WHEN之后的等式运算符的右手部分。搜索的CASE表达式必须在WHEN子句之后完成布尔运算（左手部分，运算符，右手部分）
问题2：
正确的答案是b。如果多个WHEN子句求值为TRUE，则SQL Server仅执行第一个WHEN子句的THEN部分，该WHEN子句的计算结果为TRUE。被评估为TRUE的任何其他WHEN子句的所有其他THEN子句将被跳过。
问题3：
正确的答案是b。 CASE表达式和IIF功能仅支持最多10个嵌套级别。
本文是T-SQL阶梯的一部分：超越基础阶梯
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
