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

通往T-SQL的Stairway:超越基本的6级:使用CASE表达式和IIF函数。

作者Gregory Larsen, 2016/04/20(第一期出版:2014/04/09)

该系列

这篇文章是Stairway系列的一部分:T-SQL的Stairway:超越基础。

从他的Stairway到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所示。

Simple CASE expression:

 

CASE input_expression

    WHEN when_expression THEN result_expression [ ...n ]

    [ ELSE else_result_expression ]

END

 

Searched CASE expression:

 

CASE

    WHEN Boolean_expression THEN result_expression [ ...n ]

    [ ELSE else_result_expression ]

END

图1:CASE语法

通过查看图1中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表达式的结果将基于对WHEN子句的计算结果是否为TRUE。如果没有WHEN子句计算为TRUE，则返回ELSE表达式。当没有ELSE子句，且WHEN子句计算都不为TRUE时，则返回空值。

样本数据的例子

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

CREATE TABLE MyOrder (

ID int identity,

OrderDT date,

OrderAmt decimal(10,2),

Layaway char(1));

INSERT into MyOrder VALUES

('12-11-2012', 10.59,NULL),

('10-11-2012', 200.45,'Y'),

('02-17-2014', 8.65,NULL),

('01-01-2014', 75.38,NULL),

('07-10-2013', 123.54,NULL),

('08-23-2009', 99.99,NULL),

('10-08-2013', 350.17,'N'),

('04-05-2010', 180.76,NULL),

('03-27-2011', 1.49, NULL);

清单1:创建示例表MyOrder。

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

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

SELECT YEAR(OrderDT) AS OrderYear,

       CASE YEAR(OrderDT)

                     WHEN 2014 THEN 'Year 1'

                     WHEN 2013 THEN 'Year 2'

                     WHEN 2012 THEN 'Year 3'

                     ELSE 'Year 4 and beyond' END AS YearType

FROM MyOrder;

 

让我来运行一下清单3来展示一下当一个CASE表示不包含ELSE子句的时候会发生什么。

清单3：没有ELSE字句的简单CASE表达

这个在清单3中的代码只是清单2中的代码去掉了ELSE原句的形式，当我运行这个在清单3中的代码时，它生成的是在结果2中展示的结果。

结果2：运行清单3的结果

通过回顾结果2的输出你可以发现，当OrderDT在MyOrder表格中的年份没有出现任何问题，而SQL SERVER会在“YearType“那一行显示“NULL”。

 

使用一个表达式

在一个简单表达式中，当表达被基于等号操作符被评估的时候，用这个表达式你可以使用其他操作方式，并且这个表达式语法有一点点不一样，为了证明这个，让我们来看看清单4的代码。

 清单4：表达式

如果你仔细观察清单4的清单你会发现当子句直接跟在CASE子句后面而两个子句之间没有文本。这样的表达告诉SQL Server这是一个表达式，也在后面每个WHEN子句中记下布尔表达式。如你所看到的不是所有布尔式使用的都是等号操作符。  ，在最后WHEN表达式使用符号”<”。这个在清单4中的表达式 在逻辑上和清单2中的表达式是相同的。尽管如此但是在我运行清单4中的代码的时候它产生了和结果1所显示相同的结果。

如果混合表达式WHEN被判定为TRUE的时候表达式会转换成什么样?

这里可能会出现当表达式在单独的CASE表达式中被判定为TRUE的情况。当这样发生的时候好，SQL Server会基于第一次WHEN被判定的情况返回表达式的结果。尽管你的WHEN的顺序会控制你从表达式中所得到的返回值当子句被判定为True的时候。

为了证明这一点, 让我们使用案例表达式显示"200 美元订单" 时, 当 OrderAmt 是在 $ 200 范围内, "100 美元订单" 时, OrderAmt 是在100范围内和 "100 美元秩序" 时, OrderAmt 是不到100美元, 当 OrderAmt 不下降然后将订单归类为 "300 美元和以上订单"。让我们回顾一下清单5中的代码, 以演示当试图将订单归类为这些 OrderAmt_Category 值之一时, 当表达式计算为 TRUE 时, 会发生什么情况。

清单 5: 当我运行清单5中的代码时, 当表达式的倍数计算为 TRUE

我得到结果3中的输出。结果 3: 在运行清单5的结果中, 通过查看结果3中的结果, 可以看到每个订单都报告为200或300以及以上顺序, 我们知道这是不正确的。发生这种情况的原因是, 我只使用小于 ("简单") 运算符对在我的事例表达式中计算为 TRUE 时导致多个表达式的顺序进行分类。当子句的排序不允许返回正确的表达式时。

通过重新订购我的时间子句, 我可以得到我想要的结果。清单6中的代码与清单5相同, 但我重新订购了何时子句以正确地对我的订单进行分类。

清单 6: 类似于清单5的代码, 但是当我运行清单5中的代码时子句的顺序不同, 我得到结果4的输出。结果 4: 通过在结果4中查看输出来运行清单6时的结果, 您可以看到, 通过更改表达式的顺序, 我得到了每个订单的正确结果。嵌套事例表达式有时, 您可能需要进行其他测试, 以进一步使用 CASE 表达式对数据进行分类。发生这种情况时, 可以使用嵌套的事例表达式。清单7中的代码显示了嵌套事例表达式的示例, 以进一步对 MyOrder 表中的订单进行分类, 以确定订单在200美元以上时是否使用分期预付价值购买订单。

清单 7: 嵌套 CASE 语句

清单7中的代码与清单6中的代码类似唯一的区别是, 我添加了一个额外的案例表达式, 以查看是否使用分期付款方式购买MyOrder 表中的订单, 这只允许在购买超过200美元。请记住, 当您嵌套 CASE 表达式时, SQL server 只允许您拥有多达10层的嵌套。到目前为止可以使用案例表达式的其他地方, 我的所有示例都使用 case 表达式在 TSQL select 语句的选择列表中放置 case 表达式来创建结果字符串。还可以在更新、删除和设置语句中使用 CASE 表达式。此外, CASE 表达式可以与 in、WHERE、ORDER 和有子句一起使用。在清单8中, 我使用一个表示 where 子句的案例。

清单 8: 在清单8的 where 子句中使用 CASE 表达式

我只想从 MyOrder 表中返回 "年 1" 中的行的订单。为此, 我将相同的事例表达式与我在where 子句中的清单2中使用的相同。我使用 CASE 表达式作为 WHERE 条件的左手部分, 以便根据 OrderDT 列生成不同的 "年..." 字符串。

然后，我测试了从case表达式生成的从MyOrder表返回的一行字符串，看看它是否等于“Year1”的值。请记住，当还有其他更好的方法，如使用YEAR函数来为给定的一年选择行的时候，我不建议使用case表达式像使用字符串“YEAR1”从日期列中选择日期。我只在这里演示了如何在WHERE子句中使用case语句。

使用IIF函数的case表达式的快捷方式

随着SQL Server 2012的引入，微软添加了IIF函数。IIF函数可以被视为CASE语句的快捷方式。在图2中，您可以找到IIF函数的语法。

IIF ( boolean_expression, true_value,false_value )

图2：IIF函数的语法

“Boolean_expression”是一个有效的布尔表达式，等于true或false。当布尔表达式等于一个真值时，则执行“true_value”表达式。如果布尔表达式等于false，则执行“false_value”。与case表达式一样，IIF函数可以嵌套到10个级别。

使用IIF的例子

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

清单9：简单的case表达式示例

清单9中的代码只有一个表达式，用于确定OrderAmt是高还是低的订单。如果表达式“OrderAMT> 200”计算为true时，则OrderType值被设置为“High $Order”。如果当表达式计算为false时，则为OrderType值设置“Low $ Order”。

使用IIF函数代替case表达式的重编写代码可以在清单10中找到。

清单10：使用IIF函数的示例

通过查看清单10，您可以看到IIF函数为什么被视为case表达式的速写版本。用IIF（string，用逗号替换“THE”子句，用逗号替换“ELSE”子句，用“a”结尾替换“END”）替换case字。当布尔表达式“OrderAmt> 200”为真时，显示“High $ Orde”的值。当布尔表达式“OrderAmt> 200”被评估为false时，则显示“Low $ Order””。如果运行清单9和清单10中的代码，您将看到它们都产生完全相同的输出。

嵌套IIF函数的示例

就像CASE表达式那样，SQL Server允许您嵌套IIF函数。在清单11中是嵌套IIF函数的示例。

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

在这个例子中，你可以看到我已经多次使用了IIF函数。每增加的一个都使用IIF功能的“真值”或“假值”。清单11中的代码与清单7中使用嵌套CASE表达式的代码相同。

限制

与大多数TSQL功能一样，这是有限制的。以下是有关CASE和IIF结构的一些限制。

CASE表达限制：

CASE表达式中最多只能有10层嵌套。

CASE表达式不能用于控制SQL语句的执行流程。

IIF功能限制：

你最多只能有10层嵌套IIF语句。

概要

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

问题和答案

在本节中，您可以通过回答以下问题来复习你对CASE和IIF构造的使用理解。

问题1：

CASE表达式有两种不同的语法变体：Simple和Searched。 以下哪两条语句最好地描述了CASE表达式Simple和Searched之间的区别（选择两个）。

a. Simple  CASE语法只支持相等运算符，而SearchedCASE语法支持多个运算符

b. Simple  CASE语法支持多个运算符，而SearchedCASE语法仅支持相等运算符

c. Simple  CASE语法在WHEN子句之后指定了其布尔表达式，而Searched CASE语法在CASE语句后面有布尔表达式的左侧，在WHEN子句后面有布尔表达式的右侧。

d. Simple CASE语法在CASE语句后面有布尔表达式的左侧，在WHEN子句后面有布尔表达式的右侧，而Searched CASE表达式在WHEN子句后面具有布尔表达式

问题2：

a. 执行最后一个计算为TRUE的WHEN子句的THEN表达式。

b. 执行第一个计算结果为TRUE的WHEN子句的THEN表达式。

c. 执行所有计算式值为TRUE的WHEN子句的THEN表达式，。

d. ELSE表达式被执行

问题3：

CASE表达式或IIF函数有多少嵌套层次？

a.8

b.10

c.16

d.32

回答：

问题1：

答案是a和d。一个Simple CASE语句只能使用相等运算符，而Searched CASE表达式可以处理多个运算符以及复杂的布尔表达式。另外，Simple CASE语法在单词CASE之后有相等运算符的左边部分，在WHEN之后有相等运算符的右边部分。Searched的CASE表达式必须在WHEN子句之后立即完成布尔运算（左边部分，运算符，右边部分）

问题2：