转译：T-SQL的楼梯: 基础级别 6: 使用 CASE 表达式和 IIF 函数

T-SQL的楼梯:  基础级别 6: 使用 CASE 表达式和 IIF 函数

 

作者：格雷戈里拉尔森   , 2016/04/20 (首次发布: 2014/04/09)

以下系列

本文是楼梯系列的一部分: T-SQL的楼梯: 超出基本内容

在从他的阶梯到 T-SQLDML 之后, 格雷戈里. 拉森介绍了T-SQL语言的更高级方面, 如子查询。

有时您需要编写一个T-SQL语句, 它可以根据对另一个表达式的计算返回不同的 T-SQL 表达式。当您需要这种功能时, 您可以使用 CASE 表达式或 IIF 函数来满足此要求。在本文中, 我将回顾案例和 iif 语法, 并向您展示 case 表达式和 iif 函数的示例。

 

了解案例表达式

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

CASE 表达式语法

CASE 表达式有两种不同的格式: 简单和搜索。每个类型的格式稍有不同, 如图1所示。

 

简单的 CASE 表达式:

案例 input_expression

当 when_expression 然后 result_expression [... n]

[其他 else_result_expression]

结束

搜索的案例表达式:

情况

当 Boolean_expression 然后 result_expression [... n]

[其他 else_result_expression]

结束

 

图 1: CASE 表达式语法

 

通过查看图1中 case 表达式的两种不同格式, 您可以看到每个格式如何提供一种不同的方法来标识确定 case 表达式结果的多个表达式之一。对于这两种类型的情况, 每个当子句都执行布尔测试。用简单的事例表达式, 布尔测试的左手边在案件词以后出现, 并且叫 "input_expression", 并且布尔测试的右手片面是正确的在词以后, 当并且叫 "当表示。使用简单的 CASE 表达式, "input_expression" 和 "when_expression" 之间的运算符始终是相等运算符。而随着搜索的案例表达式每一个当子句将包含一个 "Boolean_expression"。这个 "Boolean_expression" 可以是一个简单的布尔表达式, 带有一个运算符, 或者是一个具有多种不同条件的复杂布尔表达式。此外, 搜索的 case 表达式可以使用完整的布尔运算符集。

无论使用哪种情况格式, 每个当子句都按其出现的顺序进行比较。CASE 表达式的结果将基于第一个当计算为 TRUE 的子句。如果子句的计算结果为 TRUE, 则返回 ELSE 表达式。如果省略 ELSE 子句且子句的计算结果为 TRUE, 则返回 NULL 值。

 

示例数据

 

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

 

创建表 MyOrder (

 

ID int 标识,

 

OrderDT 日期,

 

OrderAmt 小数 (102),

 

分期字符 (1));

 

插入到 MyOrder 值中

 

("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中的代码。

 

选择年份 (OrderDT) 作为 OrderYear,

      

案例年 (OrderDT)

 

当2014然后 ' 年 1 '

 

当2013然后 ' 年 2 '

 

当2012然后 ' 年 3 '

 

其他年份4和超过 ' 结束作为 YearType

 

从 MyOrder;

 

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

 

让我先谈谈为什么这是一个简单的例子表达。如果您查看清单2中的代码, 您可以看到, 在单词大小写之后, 我指定了表达式 "年 (OrderDT)", 然后, 当表达式每一个都指定了不同的年份 (从2014开始), 我遵循三的不同。因为我指定了在 case 和第一次关键字之间的表达式, 这告诉 SQL Server 这是一个简单的 case 表达式。

 

当我的简单事例表达式被计算时, 它使用相等运算符 ("=") 在 "年份 (")"值和表达式之间的不同。因此, 清单1中的代码将在YearType列中显示OrderDT年值为2014的行的 "年 1", 否则将显示OrderDT年的行的 "年 2"。2013"或它将显示"年 3", 该行的OrderDT年份为2012。如果OrderDT的年份与任何表达式都不匹配, 则 ELSE 条件将显示 "年4及更远"。

 

当我运行清单2中的代码时, 我得到结果1中显示的输出。

 

OrderYear YearType

 

----------- -----------------

 

2012年3

 

2012年3

 

2014年1

 

2014年1

 

2013年2

 

2009年4及以后

 

2013年2

 

2010年4及以后

 

2011年4及以后

 

结果 1: 运行清单2时的结果

 

使用不带 ELSE 表达式的简单 CASE 表达式

 

让我运行清单3中的代码, 它将显示当一个简单的 CASE 表达式没有 ELSE 子句时会发生什么情况。

 

选择年份 (OrderDT) 作为 OrderYear,

 

案例年 (OrderDT)

 

当2014然后 ' 年 1 '

 

当2013然后 ' 年 2 '

 

当2012然后 ' 年 3 ' 结束作为 YearType

 

从 MyOrder;

 

清单 3: 不带 ELSE 子句的简单 CASE 表达式

 

清单3中的代码就像清单2中的代码, 但没有 ELSE 子句。当我运行清单3中的代码时, 它会产生结果2中显示的结果。

 

OrderYear YearType

 

----------- --------

 

2012年3

 

2012年3

      

2014年1

 

2014年1

 

2013年2

 

2009 NULL

 

2013年2

 

2010 NULL

 

2011 NULL

 

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

 

通过查看结果2中的输出, 您可以看到, 当MyOrder表中的OrderDT年份不符合任何条件时, SQL Server 将为该行的 YearType 值显示 "NULL"。

 

使用搜索的 case 表达式

 

在简单的 CASE 表达式中, 表达式是基于相等运算符进行计算的。通过搜索的 case 表达式, 您可以使用其他运算符, 并且 case 表达式语法略有不同。为了演示这一点, 让我们看一下清单4中的代码。

 

选择年份 (OrderDT) 作为 OrderYear,

 

情况

 

当年 (OrderDT) = 2014 然后 ' 年 1 '

 

当年 (OrderDT) = 2013 然后 ' 年 2 '

 

当年 (OrderDT) = 2012 然后 ' 年 3 '

 

当年 (OrderDT) < 2012 然后 ' 年4和以远 '

 

以 YearType 结束

 

从 MyOrder;

 

清单 4: 搜索的 case 表达式

 

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

 

当表达式计算为 TRUE 时返回的表达式为多？

 

在单个 CASE 表达式中, 当表达式计算为 TRUE 时, 可能会出现不同的情况。当发生这种情况时, 当计算结果为 true 的表达式时, SQL Server 将返回与第一个关联的成果表达式。因此, 如果子句计算为 TRUE, 则子句的顺序将控制从 CASE 表达式返回的结果。

 

为了演示这一点, 让我们使用 CASE 表达式显示 "200 美元的订单", 当OrderAmt位于 $ 200 范围内, "100 美元订单", 当OrderAmt在 $ 100 范围内和"< 100 美元订单"当OrderAmt低于100美元时OrderAmt不属于这些类别中的任何一种, 然后将该订单归类为"300 美元以上的订单"。让我们回顾一下清单5中的代码, 以演示当试图将订单分类到这些OrderAmt_Category值之一时, 当表达式计算为 TRUE 时会发生什么情况。

 

选择 OrderAmt,

 

情况

 

当 OrderAmt < 300, 然后 ' 200 美元订单 '

 

当 OrderAmt < 200, 然后 ' 100 美元订单 '

 

当 OrderAmt < 100 然后 ' < 100 美元订单 '

 

其他 "300 美元以上订单"

 

以 OrderAmt_Category 结束

 

从 MyOrder;

 

清单 5: 当表达式计算为 TRUE 时的多个示例

 

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

 

OrderAmt OrderAmt_Category

 

--------------------------------------- --------------------------

 

10.59 200 美元订购

 

200.45 200 美元订购

 

8.65 200 美元订购

 

75.38 200 美元订购

 

123.54 200 美元订购

 

99.99 200 美元订购

 

350.17 300 美元以上订单

 

180.76 200 美元订购

 

1.49 200 美元订购

 

结果 3: 运行清单5时的结果

 

通过查看结果3中的结果, 您可以看到每个订单都报告为200或300以上的订单, 我们知道这是不正确的。之所以发生这种情况, 是因为我只使用了少于 ("<") 运算符来地对在我的 CASE 表达式中计算为 TRUE 的表达式进行分类的顺序。当子句的排序不允许返回正确的表达式时。

 

通过更新我的条款, 我可以得到我想要的结果。清单6中的代码与清单5相同, 但我已经更新了何时正确分类我的订单。

 

选择 OrderAmt,

 

情况

      

当 OrderAmt < 100 然后 ' < 100 美元订单 '

 

当 OrderAmt < 200, 然后 ' 100 美元订单 '

 

当 OrderAmt < 300, 然后 ' 200 美元订单 '

 

其他 "300 美元以上订单"

 

以 OrderAmt_Category 结束

 

从 MyOrder;

 

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

 

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

 

OrderAmt OrderAmt_Category

 

--------------------------------------- --------------------------

 

10.59 < 100 美元订购

 

200.45 200 美元订购

 

8.65 < 100 美元订购

 

75.38 < 100 美元订购

 

123.54 100 美元订购

 

99.99 < 100 美元订购

 

350.17 300 美元以上订单

 

180.76 100 美元订购

 

1.49 < 100 美元订购

 

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

 

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

 

嵌套用例表达式

 

有时您可能需要进行额外的测试, 以便使用 CASE 表达式进一步对数据进行分类。当发生这种情况时, 可以使用嵌套的 CASE 表达式。清单7中的代码显示了一个嵌套 CASE 表达式的示例, 以便进一步对MyOrder表中的订单进行分类, 以确定订单超过¥200时是否使用分期值购买订单。

 

选择 OrderAmt,

 

情况

 

当 OrderAmt < 100 然后 ' < 100 美元订单 '

 

当 OrderAmt < 200, 然后 ' 100 美元订单 '

 

当 OrderAmt < 300 然后

 

情况

 

当分期 = "N"

 

然后 ' 200 美元的订单没有分期 '

 

其他的200美元的订单与分期的结束

 

其他

 

情况

 

当分期 = "N"

 

然后 ' 300 美元的订单没有分期 '

 

其他的300美元的订单与分期的结束

 

以 OrderAmt_Category 结束

 

从 MyOrder;

 

清单 7: 嵌套 CASE 语句

 

清单7中的代码与清单6中的代码类似。唯一的区别是, 我添加了一个额外的 CASE 表达式, 以查看是否使用了分期选项购买了MyOrder表中的订单, 这只允许在超过200美元的购买上进行。当您嵌套 CASE 表达式时请记住, SQL Server 只允许您拥有多达10层的嵌套。

 

可以使用案例表达式的其他地方

 

到目前为止, 我的所有示例都使用 case 表达式来创建一个结果字符串, 方法是将 case 表达式放在 TSQL select 语句的选择列表中。也可以在更新、删除和设置语句中使用 CASE 表达式。另外, CASE 表达式可以与 in、WHERE、ORDER 和 HAVING 子句结合使用。在清单8中, 我使用了一个表示 where 子句的案例。

 

选择*

 

从 MyOrder

 

凡个案年 (OrderDT)

 

当2014然后 ' 年 1 '

 

当2013然后 ' 年 2 '

 

当2012然后 ' 年 3 '

 

其他年份4和超过 ' 结束 = ' 年 1 ';

 

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

 

在清单8中, 我只想从MyOrder表中返回 "年 1" 中的行的顺序。为了实现这一点, 我将与我在 where 子句中使用的相同的 CASE 表达式放在清单2中。我将 CASE 表达式用作 WHERE 条件的 left-hand 部分, 因此它将根据OrderDT列生成不同的 "年份..." 字符串。然后, 我测试了从 CASE 表达式生成的字符串, 以查看它是否等于值 "年 1", 当它是一个行将从MyOrder表返回的行时。请记住, 我不建议使用 CASE 表达式从日期列中选择日期, 使用像 "年 1" 这样的刺痛, 当有其他更好的方法, 如使用年份函数来选择给定年份的行。我在这里只做了演示如何在 where 子句中使用 CASE 语句。

 

使用 IIF 函数快捷方式 CASE 表达式

 

随着 SQL Server 2012 的引入, Microsoft 添加了 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中的搜索用例表达式的代码。

 

选择 OrderAmt,

 

情况

 

当 OrderAmt > 200, 然后 ' 高 $ 订单 '

 

其他 "低美元订单" 结束为 OrderType

 

从 MyOrder;

 

清单 9: 简单 CASE 表达式示例

 

清单9中的代码只有一个表达式, 用于确定OrderAmt是否为高或低的美元顺序。如果表达式 "OrderAMT > 200"的计算结果为 TRUE, 则OrderType值设置为 "高 $ Order"。如果表达式的计算结果为 FALSE, 则为OrderType值设置 "低 $ Order"。

 

使用 IIF 函数而不是 CASE 表达式的重写代码可以在清单10中找到。

 

选择 OrderAmt,

 

IIF (OrderAmt > 200,

      

"高价订购"

 

"低 $ 订单") 作为 OrderType

 

从 MyOrder;

 

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

 

通过查看清单 10, 您可以看到为什么 IIF 函数被认为是 CASE 表达式的简写版本。单词大小写被替换为 "IIF (" 字符串, "然后" 子句用逗号替换, "ELSE" 子句用逗号替换, "END" 替换为 ")"。当布尔表达式 "OrderAmt > 200" 为 TRUE 时, 将显示 "高 $ Order" 值。当布尔表达式 "OrderAmt > 200" 被计算为 FALSE 时, 将显示 "低 $ Order"。如果您运行清单9和10中的代码, 您将看到它们都产生完全相同的输出。

 

嵌套 IIF 函数的示例

 

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

 

选择 OrderAmt,

 

IIF (OrderAmt < 100,

 

"< 100 美元订单",

 

(IIF (OrderAmt < 200,

 

"100 美元的订单",

 

(IIF (OrderAmt < 300,

 

(IIF (分期 = "N",

 

"200 美元的订单没有分期",

 

"200 元订单与分期"

 

                                           )

 

                                      ),

 

(IIF (分期 = "N",

 

"300 美元的订单没有分期",

 

"300 元订单与分期"

 

                                           )

 

                                )

 

                                     )

 

                                )

 

                         )

 

               )

 

) 作为 OrderAmt_Category

 

从 MyOrder;

 

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

 

在本例中, 您可以看到我多次使用 IIF 函数。每个附加的函数要么用于 "true 值", 要么用于 IIF 功能的 "false 值"。清单11中的代码等效于使用清单7中的嵌套 CASE 表达式的代码。

 

限制

 

与大多数 TSQL 功能一样, 有局限性。下面是有关案例和 IIF 构造的一些限制。

 

案例表达式限制:

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

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

      

IIF 函数限制:

•您最多只能有10级别的 IIF 子句嵌套。

 

摘要

 

CASE 表达式和 IIF 函数使您可以将表达式逻辑放在 TSQL 代码中, 以根据表达式的计算结果更改代码的结果。通过使用 IIF 函数和 CASE 表达式支持的比较表达式, 您可以根据比较表达式的计算结果为 TRUE 或 FALSE 来执行不同的代码块。CASE 表达式和 IIF 函数为您提供编程控制, 以满足您可能没有的业务需求。

 

问答

 

在本节中, 您可以通过回答以下问题来回顾使用案例和 IIF 构造的理解程度。

 

问题 1:

 

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

a.简单的 case 语法只支持相等运算符, 而搜索的 case 语法支持多个运算符

b.简单的 case 语法支持多个运算符, 而搜索的 case 语法只支持相等运算符

c.简单的事例语法有它的布尔表达式, 它在 "on" 子句之后指定, 而搜索的 case 语法在 case 语句之后具有布尔表达式的左侧, 而布尔表达式的右侧则在时子句之后。

d.简单的 case 语法在 case 语句之后具有布尔表达式的左侧, 而在布尔表达式的右侧则在 if 子句之后, 而搜索的 case 表达式在时子句后有其布尔表达式

 

问题 2:

 

如果在计算结果为 TRUE 的子句中, CASE 表达式有多个, 则执行哪个 or 子句？

a.执行计算结果为 TRUE 的最后一个当子句的表达式。

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

c.然后执行计算结果为 TRUE 的子句的所有表达式。

d.执行 ELSE 表达式

 

问题 3:

 

CASE 表达式或 IIF 函数可以有多少嵌套级别？

a.8

b.10

c.16

d.32

 

答案:

 

问题 1:

 

答案是 a 和 d。简单的 case 语句只能使用相等运算符, 而搜索的 case 表达式可以处理多个运算符和复杂的布尔表达式。另外简单的事例句法在词案件和相等操作员的右手部分以后有等式操作员权利的左手部分在词以后。搜索的 case 表达式必须完成布尔运算 (左手部分, 操作者, 右手部分) 在当从句之后

 

问题 2:

 

正确答案是 b。如果子句的计算结果为 true, 则 SQL Server 只执行第一个当值为 true 的子句的随后部分。当将被计算为 TRUE 的子句跳过时, 其他所有子句都将被忽略。

 

问题 3:

 

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

 

 

 

本文是的一部分 到 T-SQL 的楼梯: 超出基本内容  楼梯

 

注册到我们的 RSS 源, 并得到通知, 一旦我们发布一个新的水平在楼梯! Rss

 

转载地址：Stairway to T-SQL: Beyond The Basics Level 6: Using the CASE Expression and IIF Function

http://www.sqlservercentral.com/articles/Stairway+Series/108723/