T-SQL进阶之CASE与IIF-优快云博客

本文深入探讨了T-SQL中的CASE表达式和IIF函数，包括它们的语法、应用场景以及如何根据条件返回不同的结果。通过具体示例，读者可以了解如何在SQL查询中有效使用这些特性。

T-SQL的阶梯：超越基础6级：使用case表达式和IIF函数

该系列：本文是阶梯系列的一部分：通往T-SQL的阶梯：超越基础

从他的阶梯到T-SQL DML之后，Gregory Larsen涵盖了T-SQL语言的更高级的方面，如子查询。

有时需要编写一个能够根据另一个表达式的评价返回不同TSQL表达式的单个TSQL语句。当你需要这种功能时，可以使用case表达式或IIF函数来满足这一要求。在本文中，我将回顾案例和IIF语法，并向你展示案例表达式和IIF函数的示例。

理解案例表达

Transact-SQLcase表达式允许你在TSQL代码中放置条件逻辑。此条件逻辑为你提供了一种在TSQL语句中放置不同代码块的方法，这些代码块可以根据条件逻辑的真值或假值来执行。可以将多个条件表达式放置在单个情况表达式中。当在case子句中有多个条件表达式时，计算为true的第一个表达式将是由TSQL语句评估的代码块。为了更好地理解case表达式是如何工作的，我将回顾case表达式的语法，然后通过许多不同的例子。

实例表达式语法

case表达式有两种不同的格式：简单和搜索。每种类型的格式略有不同，如图1所示。

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表达式的两种不同格式，你可以看到每个格式如何提供一种不同的方式来识别确定表达式的结果的多个表达式中的一个。对于这两种情况，都为每个WHON子句执行布尔测试。用简单的case表达式，布尔测试的左手边恰好出现在case字之后，被称为“输入表达式”，而布尔测试的右手边正好在Word之后，并被称为“When表达式”。在简单的case表达式中，“输入表达式”和“WHI表达式”之间的运算符总是相等运算符。而在搜索子句表达式时，子句将包含“Boulangi表达式”。这个“BooLoNuy表达式”可以是一个带有单个运算符的简单布尔表达式，或者是一个具有许多不同条件的复杂布尔表达式。此外，所搜索的case表达式可以使用布尔算子的全集合。

不管使用哪种情况下的格式，每个WITH子句都按其出现的顺序进行比较。case表达式的结果将基于计算为true的第一个WHON子句。如果否，则当子句求值为真时，返回该表达式。当省略了其他子句，并且当子句求值为true时，则返回空值。

示例数据示例

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

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：创建订单表

使用一个简单的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;

清单2：使用其他表达式的简单case表达式

让我先来讨论一下为什么这是一个简单的案例表达式。如果你查看清单2中的代码，你可以看到，在Word case之后，我指定了表达式“年（OrdDT）”，然后跟着三个不同的表达式，每个表达式都指定了不同的年份，从2014开始。因为我在case和第一个关键字之间指定了这个表达式，所以这个告诉SQL Server这是一个简单的case表达式。

当我的简单case表达式被评估时，它在“年（OrdDATE）”值和不同的表达式之间使用相等运算符（“=”）。因此，清单1中的代码将显示具有“2014”的OrdDT年值的行的“年份”列的“年1”，或将显示具有“2013”的OrdDT年份的行的“2年”，或将显示具有“2012”的OrdDT年的行的“年3”。如果OrdDT的一年与任何一个表达式不匹配，则其他条件将显示“4年及以后”。

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

OrderYear   YearType

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

2012        Year 3

2012        Year 3

2014        Year 1

2014        Year 1

2013        Year 2

2009        Year 4 and beyond

2013        Year 2

2010        Year 4 and beyond

2011        Year 4 and beyond

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

使用没有表达式的简单case表达式

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

SELECT YEAR(OrderDT) AS OrderYear,

       CASE YEAR(OrderDT)

                 WHEN 2014 THEN 'Year 1'

                 WHEN 2013 THEN 'Year 2'

                 WHEN 2012 THEN 'Year 3' END AS YearType

FROM MyOrder;

清单3：没有其他子句的简单case表达式

清单3中的代码与清单2中的代码一样，但没有一个其他子句。当我运行清单3中的代码时，它会产生结果2中所示的结果。

OrderYear   YearType

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

2012        Year 3

2012        Year 3

2014        Year 1

2014        Year 1

2013        Year 2

2009        NULL

2013        Year 2

2010        NULL

2011        NULL

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

通过检查结果2中的输出，可以看到，当MyOrrar表中的OrdDT年不满足任何WHERE子句条件时，SQLServer为该行的YaType值显示“NULL”。

使用搜索的实例表达式

在简单的case表达式中，基于相等算子来计算表达式。使用已搜索的case表达式，可以使用其他运算符，并且case表达式语法稍有不同。为了演示这一点，让我们看看清单4中的代码。

SELECT YEAR(OrderDT) AS OrderYear,

       CASE

                 WHEN YEAR(OrderDT) = 2014 THEN 'Year 1'

                 WHEN YEAR(OrderDT) = 2013 THEN 'Year 2'

                 WHEN YEAR(OrderDT) = 2012 THEN 'Year 3'

                 WHEN YEAR(OrderDT) < 2012 THEN 'Year 4 and beyond'

                       END AS YearType

FROM MyOrder;

清单4：搜索案例表达式

如果查看清单4中的代码，可以看到WITH子句紧跟在两个子句之间没有文本的case子句之后。这告诉SQLServer这是一个已搜索的case表达式。还要注意每一个WHON子句之后的布尔表达式。正如你所看到的，并非所有布尔表达式都使用相等运算符，最后表达式使用小于（“<”）运算符。清单4中的case表达式在逻辑上与清单2中的case表达式相同。因此，当我运行清单4中的代码时，它产生的结果与结果1中所示的结果相同。

当表达式求值为真时，如果返回多个表达式，将返回什么表达式？

在单个情况表达式中，当表达式求真时可能存在不同的情况。当发生这种情况时，SQLServer将返回与表达式为true的表达式中第一个关联的结果表达式。因此，如果子句求值为true，则WHO子句的顺序将控制从case表达式返回的结果。

为了演示这一点，让我们使用case表达式来显示当OrderAmt位于200美元范围内时，“200美元订单”，当OrdAMT在100美元范围内时，“100美元订单”，当OrdAAMT不低于$100时，OrdAAMT不属于任何一个。这些类别然后将订单分类为“300美元以上订单”。让我们回顾清单5中的代码，演示当试图将订单分类为这些OrthAMTTHORE类别值中的一个时，当多个表达式求真时会发生什么。

SELECT OrderAmt,

       CASE

                 WHEN OrderAmt < 300 THEN '200 Dollar Order'

                 WHEN OrderAmt < 200 THEN '100 Dollar Order'

                 WHEN OrderAmt < 100 THEN '< 100 Dollar Order'

                 ELSE  '300 Dollar and above Order'

                     END AS OrderAmt_Category

FROM MyOrder;

清单5：多个表达式何时为true的示例

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

OrderAmt                                OrderAmt_Category

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

10.59                                   200 Dollar Order

200.45                                  200 Dollar Order

8.65                                    200 Dollar Order

75.38                                   200 Dollar Order

123.54                                  200 Dollar Order

99.99                                   200 Dollar Order

350.17                                  300 Dollar and above Order

180.76                                  200 Dollar Order

1.49                                    200 Dollar Order

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

通过检查结果3中的结果，你可以看到每个订单都被报告为200或300以上的订单，并且我们知道这是不正确的。这是因为我只使用了小于（“<”）运算符来简单地对订单进行排序，这导致在我的case表达式中当表达式求真时出现多个。THON子句的排序不允许返回正确表达式。

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

SELECT OrderAmt,

       CASE

                 WHEN OrderAmt < 100 THEN '< 100 Dollar Order'

                 WHEN OrderAmt < 200 THEN '100 Dollar Order'

                 WHEN OrderAmt < 300 THEN '200 Dollar Order'

                 ELSE  '300 Dollar and above Order'

                     END AS OrderAmt_Category

FROM MyOrder;

清单6：与清单5类似的代码，但WITH子句的顺序不同

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

OrderAmt                                OrderAmt_Category

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

10.59                                   < 100 Dollar Order

200.45                                  200 Dollar Order

8.65                                    < 100 Dollar Order

75.38                                   < 100 Dollar Order

123.54                                  100 Dollar Order

99.99                                   < 100 Dollar Order

350.17                                  300 Dollar and above Order

180.76                                  100 Dollar Order

1.49                                    < 100 Dollar Order

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

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

嵌套实例表达式

有时，你可能需要使用附加的测试来进一步使用case表达式对数据进行分类。当发生这种情况时，可以使用嵌套的case表达式。清单7中的代码展示了嵌套case表达式以进一步对MyRead表中的订单进行分类的示例，以确定当订单超过200美元时，是否使用了临时值来购买订单。

SELECT OrderAmt,

       CASE

                 WHEN OrderAmt < 100 THEN '< 100 Dollar Order'

                 WHEN OrderAmt < 200 THEN '100 Dollar Order'

                 WHEN OrderAmt < 300 THEN

                        CASE

                              WHEN Layaway = 'N'

                                  THEN '200 Dollar Order without Layaway'

                                  ELSE '200 Dollar Order with Layaway' END

                 ELSE

                        CASE

                              WHEN Layaway = 'N'

                                  THEN '300 Dollar Order without Layaway'

                                  ELSE '300 Dollar Order with Layaway' END

                     END AS OrderAmt_Category

FROM MyOrder;

清单7：嵌套语句

清单7中的代码类似于清单6中的代码。唯一的区别是，我添加了一个附加的case表达式来查看MyOrrad表中的订单是否使用LayWORD选项购买，该选项只允许购买超过200美元。记住，当你嵌套case表达式时，SQL Server只允许你最多有10个嵌套级别。

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

到目前为止，我所有的示例都使用case表达式来创建结果字符串，将case表达式放在TSQL select语句的select列表中。你还可以在更新、删除和SET语句中使用case表达式。此外，case表达式可以与In，其中，ORE和BY子句结合使用。在清单8中，我使用一个表示WHERE子句的实例。

SELECT *

FROM MyOrder

WHERE CASE YEAR(OrderDT)

               WHEN 2014 THEN 'Year 1'

               WHEN 2013 THEN 'Year 2'

               WHEN 2012 THEN 'Year 3'

               ELSE 'Year 4 and beyond' END = 'Year 1';

清单8：在WHERE子句中使用case表达式

在清单8中，我只想从MyOrror表中返回“1年”中的行。为了实现这一点，我在WHERE子句中放置了与清单2中所用的相同的case表达式。我使用case表达式作为WHERE条件的左边部分，因此它将基于OrdDT列产生不同的“年…”字符串。然后，我测试了从case表达式生成的字符串，看看它是否等于“年1”的值，当一行将从MyRead表返回。请记住，我不建议使用一个case表达式从一个日期栏中使用St蜇样的“年1”来选择日期，当还有其他更好的方法，如使用年函数来为给定的一年选择行。我只是在这里演示了如何在WHERE子句中使用case语句。

使用IIF函数快捷剪切case表达式

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

IIF ( boolean_expression, true_value, false_value )

图2：IIF函数的语法

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

使用IIF的例子

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

SELECT OrderAmt,

       CASE

                 WHEN OrderAmt > 200 THEN 'High $ Order'

                 ELSE 'Low $ Order' END AS OrderType

FROM MyOrder;

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

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

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

SELECT OrderAmt,

          IIF(OrderAmt > 200,

                'High $ Order',

                'Low $ Order') AS OrderType

FROM MyOrder;

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

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

嵌套IIF函数的示例

就像case表达式SQLServer允许你嵌套IIF函数。清单11是嵌套IIF函数的一个例子。

SELECT OrderAmt,

       IIF (OrderAmt < 100,

                '< 100 Dollar Order',

                (IIF (OrderAmt < 200,

                        '100 Dollar Order',

                      (IIF (OrderAmt < 300,

                             (IIF (Layaway = 'N',

                                          '200 Dollar Order without Layaway',

                                          '200 Dollar Order with Layaway'

),

                                     (IIF (Layaway = 'N',

                                           '300 Dollar Order without Layaway',

                                           '300 Dollar Order with Layaway'

               ) AS OrderAmt_Category

FROM MyOrder;

清单11：一个IIF函数嵌套的例子

在这个示例中，你可以看到我多次使用IIF函数。每个额外的一个都用于IIF函数的“真值”或“假值”。清单11中的代码相当于使用清单7中嵌套的case表达式的代码。

局限性

与大多数TSQL功能一样，存在局限性。以下是关于病例和IIF结构的一些限制。

病例表达限制：

在表达式的情况下，最多只能嵌套10个层次。

无法使用case表达式来控制TSQL语句的执行流程。

IIF功能限制：

IIF子句的嵌套最多只能有10个层次。

总结

case表达式和IIF函数允许你将表达式逻辑放在TSQL代码中，该代码将基于表达式的评估结果来更改代码的结果。通过使用IIF函数和case表达式支持的比较表达式，可以根据比较表达式是否计算为true或false来执行不同的代码块。case表达式和IIF函数为你提供编程控制，以满足你可能不具备的业务需求。

问答

在这一节中，你可以通过回答下列问题来查看如何理解用例和IIF构造。

问题1：

有两种不同的语法变化的情况下表达：简单和搜索。下面这两个语句最好地描述了一个简单的和搜索的表达式（选择二）之间的区别。

简单实例语法仅支持相等运算符，而搜索的实例语法支持多个运算符。

简单实例语法支持多个运算符，而搜索实例语法仅支持相等运算符。

简单的case语法具有在WHON子句之后指定的布尔表达式，而被搜索的case语法在case语句之后有布尔表达式的左侧，并且在WHON子句后面有布尔表达式的右侧。

简单的case语法在布尔语句的右侧语句后面和布尔表达式右边的WHON子句之后有布尔表达式的左边，而搜索的case表达式在WHON子句后面有布尔表达式。

问题2：

如果case表达式有多个赋值为true的子句，那么执行/thor子句？

执行最后一个WAN子句的表达式，然后计算为true。

执行第一个WITH子句的表达式，然后计算为true。