该系列
本文是“Stairway系列:T-SQL的基石:超越基础”的一部分
从他的Stairway到T-SQL DML之后,Gregory Larsen涵盖了T-SQL语言的更多高级方面,例如子查询。
有时您需要编写一个TSQL语句,该语句能够根据另一个表达式的求值返回不同的TSQL表达式。 当您需要这种功能时,您可以使用CASE表达式或IIF函数来满足此要求。 在本文中,我将回顾CASE和IIF语法,并向您展示CASE表达式和IIF函数的示例。
理解CASE表达式
Transact-SQL CASE表达式允许您在TSQL代码中放置条件逻辑。 这种条件逻辑为您提供了一种方法,可以根据条件逻辑的TRUE或FALSE评估来执行TSQL语句中可以执行的不同代码块。 您可以在单个CASE表达式中放置多个条件表达式。 当您的CASE子句中有多个条件表达式时,第一个表达式的值为TRUE将是由您的TSQL语句评估的代码块。 为了更好地理解CASE表达式的工作原理,我将回顾CASE表达式的语法,然后通过一些不同的例子。
CASE表达式语法
CASE表达式有两种不同的格式:Simple和Searched。 每种类型的格式都略有不同,如图1所示。
图1:CASE表达式语法
通过查看图1中CASE表达式的两种不同格式,您可以看到每种格式如何提供一种不同的方式来标识决定CASE表达式结果的多个表达式之一。对于两种类型的CASE,都会为每个WHEN子句执行布尔测试。使用简单CASE表达式,布尔测试的左手侧出现在CASE单词的后面,称为“输入表达式”,布尔测试的右侧恰好在WHEN之后,称为“when表达式”。使用Simple CASE表达式,“input_expression”和“when_expression”之间的运算符总是等于运算符。而搜索CASE表达式的每个WHEN子句将包含一个“布尔表达式”。该“布尔表达式”可以是具有单个运算符的简单布尔表达式,也可以是具有许多不同条件的复杂布尔表达式。另外,搜索到的CASE表达式可以使用全套布尔运算符。
无论使用哪种CASE格式,每个WHEN子句按其出现的顺序进行比较。 CASE表达式的结果将基于评估为TRUE的第一个WHEN子句。如果没有WHEN子句评估为TRUE,则返回ELSE表达式。当ELSE子句被省略并且WHEN子句的计算结果为TRUE时,则返回NULL值。
示例的示例数据
为了让一个表使用CASE表达式演示,我将使用清单1中的脚本创建一个名为MyOrder的示例表。 如果您想跟随我的示例并在SQL Server实例上运行它们,则可以在您选择的数据库中创建此表。
清单1:创建示例表MyOrder
在WHEN和ELSE表达式中使用简单的CASE表达式
为了演示简单的CASE表达式格式如何工作,让我运行清单2中的代码。
清单2:使用ELSE表达式的简单CASE表达式
让我先来谈谈为什么这是一个简单的CASE表达式。如果您查看清单2中的代码,您可以在CASE之后指定表达式“YEAR(OrderDT)”后面看到,然后我从三个不同的WHEN表达式开始,每个表达式都指定了不同的年份,从2014年开始。因为我指定CASE和第一个WHEN关键字之间的表达式,这告诉SQL Server这是一个简单的CASE表达式。
当我的简单CASE表达式被评估时,它使用“YEAR(OrderDate)”值和不同WHEN表达式之间的等号运算符(“=”)。因此,清单1中的代码将针对OrderDT年值为“2014”的行的YearType列显示“Year 1”,或者它将显示OrderDT年为“2013”的行的“Year 2”或它将显示OrderDT年份为“2012”的行的“Year 3”。如果OrderDT的年份与任何WHEN表达式不匹配,则ELSE条件将显示“第4年及以后”。
当我运行清单2中的代码时,得到了Result 1中显示的输出
使用没有ELSE表达式的简单CASE表达式
让我运行清单3中的代码,该代码将显示简单CASE表达式没有ELSE子句时会发生什么。
清单3:没有ELSE子句的简单CASE表达式
清单3中的代码就像清单2中的代码一样,但没有ELSE子句。 当我运行清单3中的代码时,它会生成结果2中显示的结果。
结果2:运行清单3时的结果
通过查看Result 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将返回与计算结果为true的第一个WHEN表达式关联的结果表达式。因此,如果多个WHEN子句评估为TRUE,那么WHEN子句的顺序将控制从CASE表达式返回的结果。
为了演示这一点,让我们使用CASE表达式来显示OrderAmt在200美元范围内时的“200美元订单”,当OrderAmt在100美元范围内时显示为“100美元订单”,当订单金额小于100美元时显示“<100美元订单”当OrderAmt不属于任何这些类别时,则将订单归类为“300美元及以上订单”。让我们回顾清单5中的代码,以演示当尝试将订单分类到其中一个OrderAmt_Category值时,多个WHEN表达式计算为TRUE时会发生什么情况
清单5:多个WHEN表达式评估为TRUE的示例
当我运行清单5中的代码时,得到Result 3中的输出。
结果3:运行清单5时的结果
通过查看结果3中的结果,您可以看到每个订单都被报告为200或300以上的订单,并且我们知道这是不正确的。发生这种情况是因为我只使用小于(“<”)运算符来简化Orders,将CASE表达式中的多个WHEN表达式求值为TRUE。 WHEN子句的排序不允许返回正确的表达式。
通过重新排列我的WHEN子句,我可以得到我想要的结果。清单6中的代码与清单5中的代码相同,但是我重新命令了WHEN子句来正确分类我的订单。
清单6:与清单5类似的代码,但WHEN子句的顺序不同
当我运行清单5中的代码时,得到Result 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表达式生成的字符串,看它是否等于值“年1”,当它是从MyOrder表返回的行时。请记住,如果还有其他更好的方法,例如使用YEAR函数为给定年份选择行,我不建议使用CASE表达式使用类似“Year 1”的sting从日期列中选择日期。我只是在这里演示了如何在WHERE子句中使用CASE语句。
使用IIF函数快速切换CASE表达式
随着SQL Server 2012的推出,微软添加了IIF功能。 IIF功能可被视为CASE声明的捷径。在图2中,您可以找到IIF函数的语法。
图2:IIF功能的语法
“布尔表达式”是一个有效的布尔表达式,等同于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时,则显示”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表达式不能用于控制TSQL语句的执行流程。
IIF功能限制:
•您最多只能有10层嵌套IIF条款。
概要
CASE表达式和IIF函数允许您将表达式逻辑放置在TSQL代码中,这将根据表达式的评估结果改变代码的结果。 通过使用IIF函数和CASE表达式支持的比较表达式,您可以根据比较表达式计算结果为TRUE还是FALSE来执行不同的代码块。 CASE表达式和IIF函数为您提供程序控制,以满足您可能不具备的业务需求。
问题和答案
在本节中,您可以通过回答以下问题来复习使用CASE和IIF构造的理解程度。
问题1:
CASE表达式有两种不同的语法变体:Simple和Searched。 以下哪两条语句最好地描述了简单搜索CASE表达式(选择两个)之间的区别。
一个。 简单CASE语法只支持相等运算符,而Searched CASE语法支持多个运算符
湾 简单CASE语法支持多个运算符,而Searched CASE语法仅支持相等运算符
C。 简单CASE语法在WHEN子句后面指定了其布尔表达式,而搜索CASE语法在CASE语句后面有布尔表达式的左侧,在WHEN子句后面布尔表达式的右侧。
d。 简单CASE语法在CASE语句后面布尔表达式的左侧,在WHEN子句后面布尔表达式的右侧,而搜索CASE表达式在WHEN子句后面具有布尔表达式
问题2:
如果CASE表达式具有多个评估为TRUE的WHEN子句,哪个THEN / ELSE子句被执行?
一个。 执行最后一个计算为TRUE的WHEN子句的THEN表达式。
湾 执行第一个计算结果为TRUE的WHEN子句的THEN表达式。
C。 执行所有THEN表达式的WHEN子句,其值为TRUE。
d。 ELSE表达式被执行
问题3:
CASE表达式或IIF函数有多少嵌套层次?
一个。8
湾10
C。16
d。32
回答:
问题1:
答案是a和d。 一个简单的CASE语句只能使用相等运算符,而Searled CASE表达式可以处理多个运算符以及复杂的布尔表达式。 另外,简单CASE语法在单词CASE之后有相等运算符的左边部分,在WHEN之后有相等运算符的右边部分。 搜索的CASE表达式必须在WHEN子句之后立即完成布尔运算(左边部分,运算符,右边部分)
问题2:
正确答案是b。 如果多个WHEN子句评估为TRUE,那么SQL Server仅执行第一个WHEN子句的THEN部分,其结果为TRUE。 所有其他THEN子句的任何其他WHEN子句评估为TRUE都会被跳过。
问题3:
正确答案是b。 CASE表达式和IIF函数最多只支持10个嵌套级别。
翻译自《DATABASE SYSTEM CONCEPTS》 Abraham Silberschatz Henry F.Korth S.Sudarshan