Stairway to T-SQL: Beyond The Basics Level 6: Using the CASE Expression and IIF Function

该系列这篇文章是阶梯系列的一部分:T-SQL的阶梯:超越基础。
从他的阶梯到T-SQL DML，格雷戈里·拉森涵盖了T-SQL语言的更高级的方面，比如子查询。
有时需要编写一个TSQL语句，该语句可以根据另一个表达式的值返回不同的TSQL表达式。当您需要这种功能时，您可以使用CASE表达式或IIF函数来满足这一需求。在本文中，我将回顾这个案例和IIF语法，并向您展示CASE表达式和IIF函数的示例。

理解CASE的表达

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

CASE表达式的语法

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

图1:案例表达式语法

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

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

样本数据的例子

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


清单1:创建示例表MyOrder.


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


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


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


让我先谈谈为什么这是一个简单的案例表达。如果您回顾一下清单2中的代码，您可以看到，在单词CASE之后，我指定了表达式“YEAR(OrderDT)”，然后我遵循了3个不同的表达式，每个表达式都指定了不同的年份，从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表达式没有ELSE子句时发生了什么。


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


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


结果2:运行清单3时的结果。


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


使用并搜索CASE表达式


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


清单4:搜索的CASE表达式。


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


当表达式值为TRUE时，返回的表达式是多少?


当表达式在单个表达式中值为TRUE时，可能会出现不同的情况。当这种情况发生时，SQL Server将返回与第一个计算值为true的表达式相关联的结果表达式。因此，当子句中的多个子句的值为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从句的顺序不允许返回正确的表达式。


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


清单6:与清单5类似的代码，但是当子句处于不同的顺序时。


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


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


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

问题和答案

在本节中，您可以通过回答下列问题来回顾您如何理解使用这个案例和IIF结构。

问题1:

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

A.简单的CASE语法只支持相等运算符，而搜索的CASE语法支持多个运算符。

B.简单的CASE语法支持多个操作符，而搜索的CASE语法只支持相等运算符。

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

D.简单的CASE语法在CASE语句后面的布尔表达式的左边，在WHEN子句后面的布尔表达式的右边，而搜索的CASE表达式在WHEN子句后面有它的布尔表达式。

问题2：

如果CASE表达式有多个WHEN子句的值为TRUE，那么/ELSE子句是否被执行?

A.最后一个计算为TRUE的子句的表达式被执行。

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

C.所有计算结果为TRUE的子句的表达式都被执行。

D.其他表达式执行。

问题3：

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

A.8

B.10

C.16

D.32

答案

问题1：

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

问题2：

正确的答案是b。如果多个WHEN子句计算为TRUE，那么SQL Server只执行第一个WHEN子句的then部分。当被评估为真的子句被跳过时，所有其他的子句都被省略了。

问题3：

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