T-SQL的基础:超越基础6级:使用CASE表达式和IIF函数
作者:Gregory Larsen,2016/04/20(第一版:2014/04/09)
该系列
本文是“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所示。
简单的CASE表达式:
CASE input_expression
WHEN when_expression THEN result_expression [... n]
[ELSE else_result_expression]
结束
搜索CASE表达式:
案件
WHEN布尔表达式THEN result_expression [... n]
[ELSE else_result_expression]
结束
图1:CASE表达式语法
通过查看图1中CASE表达式的两种不同格式,可以看到每种格式如何提供一种不同的方法来识别决定CASE表达式结果的多个表达式之一。对于两种类型的CASE,都会为每个WHEN子句执行布尔测试。使用简单的CASE表达式,布尔检验的左边出现在CASE字的后面,称为“输入表达式”,布尔检验的右边紧跟在WHEN之后,称为“when表达式”。对于简单CASE表达式,“input_expression”和“when_expression”之间的运算符总是等于运算符。而搜索的CASE表达式中,每个WHEN子句将包含一个“Boolean_expression”。这个“布尔表达式”可以是一个具有单一运算符的简单布尔表达式,也可以是具有许多不同条件的复杂布尔表达式。另外,搜索到的CASE表达式可以使用全套布尔运算符。
无论使用哪种CASE格式,每个WHEN子句按其出现的顺序进行比较。 CASE表达式的结果将基于评估为TRUE的第一个WHEN子句。如果没有WHEN子句求值为TRUE,则返回ELSE表达式。当ELSE子句被省略,WHEN子句的计算结果为TRUE时,则返回NULL值。
示例的示例数据
为了让一个表使用CASE表达式进行演示,我将使用清单1中的脚本来创建一个名为MyOrder的示例表。如果您想跟随我的示例并在您的SQL Server实例上运行它们,您可以在您选择的数据库中创建此表。
CREATE TABLE MyOrder(
ID int身份,
OrderDT日期,
OrderAmt decimal(10,2),
Layaway char(1));
INSERT到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
在WHEN和ELSE表达式中使用简单的CASE表达式
为了演示简单的CASE表达式格式如何工作,让我运行清单2中的代码。
SELECT YEAR(OrderDT)AS OrderYear,
CASE年(OrderDT)
当2014年那么'第一年'
当2013年那么'第二年'
当2012年那么'3年'
ELSE“第四年及以后”年份类型
FROM MyOrder;
清单2:使用ELSE表达式的简单CASE表达式
让我先来谈谈为什么这是一个简单的CASE表达式。如果您查看清单2中的代码,您可以看到在CASE之后指定了“YEAR(OrderDT)”这个表达式,然后我从三个不同的WHEN表达式中选择了三个不同的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中显示的输出。
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子句时会发生什么情况。
SELECT YEAR(OrderDT)AS OrderYear,
CASE年(OrderDT)
当2014年那么'第一年'
当2013年那么'第二年'
当2012年那么'年3'结束年份类型
FROM 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时的结果
通过查看Result 2中的输出,可以看到MyOrder表中的OrderDT的年份不符合WHEN子句条件时,SQL Server显示该行的YearType值为“NULL”。
使用搜索CASE表达式
在简单的CASE表达式中,WHEN表达式是基于等式运算符来评估的。通过搜索CASE表达式,您可以使用其他运算符,CASE表达式语法稍有不同。为了演示这个,我们来看看清单4中的代码。
SELECT YEAR(OrderDT)AS OrderYear,
案件
当年(OrderDT)= 2014那么“第一年”
当年(OrderDT)= 2013 THEN'2年'
年(OrderDT)= 2012 THEN'3年'
年(OrderDT)<2012 THEN'4年及以后'
END AS YearType
FROM MyOrder;
清单4:搜索CASE表达式
如果查看清单4中的代码,则可以看到WHEN子句紧跟在CASE子句之后,两个子句之间没有文本。这告诉SQL Server这个搜索的CASE表达式。还要注意每个WHEN子句后面的布尔表达式。正如你所看到的,并不是所有的布尔表达式都使用了相等运算符,最后一个WHEN表达式使用了小于(“<”)的运算符。清单4中的CASE表达式在逻辑上与清单2中的CASE表达式相同。因此,当我运行清单4中的代码时,它将产生与Result 1中所示相同的结果。
如果多个WHEN表达式求值为TRUE,那么返回什么表达式?
在单个CASE表达式中,可能会出现不同的WHEN表达式计算为TRUE的情况。发生这种情况时,SQL Server将返回与计算结果为true的第一个WHEN表达式关联的结果表达式。因此,如果多个WHEN子句的值为TRUE,则WHEN子句的顺序将控制从CASE表达式返回的结果。
为了演示这一点,让我们使用CASE表达式,当OrderAmt在$ 200范围内时显示“200美元订单”,当OrderAmt在$ 100范围内时显示“100美元订单”,当OrderAmt小于100美元时显示“<100美元订单”当OrderAmt不属于这些类别时,则将订单归类为“300美元及以上订单”。让我们回顾清单5中的代码,以演示当尝试将订单分类到其中一个OrderAmt_Category值时,多个WHEN表达式求值为TRUE时会发生什么情况。
SELECT OrderAmt,
案件
当订单金额<300那么'200美元订单'
当OrderAmt <200那么'100美元订单'
当OrderAmt <100 THEN'<100美元订单'
ELSE'300美元及以上订单'
END作为OrderAmt_Category
FROM MyOrder;
清单5:多个WHEN表达式求值为TRUE的示例
当我运行清单5中的代码时,v得到Result 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表达式中导致多个WHEN表达式求值为TRUE的Orders。 WHEN子句的排序不允许返回正确的表达式。
通过重新排序我的WHEN子句,我可以得到我想要的结果。清单6中的代码与清单5中的代码相同,但是我重新命令了WHEN子句来正确地分类我的订单。
SELECT OrderAmt,
案件
当OrderAmt <100 THEN'<100美元订单'
当OrderAmt <200那么'100美元订单'
当订单金额<300那么'200美元订单'
ELSE'300美元及以上订单'
END作为OrderAmt_Category
FROM MyOrder;
清单6:与清单5类似的代码,但WHEN子句的顺序不同
当我运行清单5中的代码时,得到Result 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中的输出,您可以看到,通过更改WHEN表达式的顺序,我得到了每个订单的正确结果。
嵌套CASE表达式
有时您可能需要进行额外的测试,以使用CASE表达式进一步对数据进行分类。发生这种情况时,可以使用嵌套的CASE表达式。清单7中的代码显示了一个嵌套CASE表达式的例子,以进一步对MyOrder表中的订单进行分类,以确定当订单超过200美元时是否使用Layaway值购买订单。
SELECT OrderAmt,
案件
当OrderAmt <100 THEN'<100美元订单'
当OrderAmt <200那么'100美元订单'
当OrderAmt <300那么
案件
当Layaway ='N'
那么'没有Layaway的200美元订单'
ELSE'200美元订单与LAYWAY'结束
其他
案件
当Layaway ='N'
那么'没有Layaway的300美元订单'
ELSE'300美元订单与LAYWAY'结束
END作为OrderAmt_Category
FROM MyOrder;
清单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。
选择 *
从MyOrder
WHERE CASE年(OrderDT)
当2014年那么'第一年'
当2013年那么'第二年'
当2012年那么'3年'
ELSE“第四年及以后”END =“第一年”;
清单8:在WHERE子句中使用CASE表达式
在清单8中,我只想从“MyOrder”表中返回“Year 1”中的行的订单。为了实现这个,我在WHERE子句中放置了与清单2中使用的相同的CASE表达式。我使用CASE表达式作为WHERE条件的左边部分,因此它会根据OrderDT列产生不同的“Year ...”字符串。然后,我测试了从CASE表达式产生的字符串,看它是否等于“Year 1”的值,当它是从MyOrder表返回的时候。请记住,如果还有其他更好的方法,例如使用YEAR函数选择给定年份的行,我不建议使用CASE表达式来使用类似“Year 1”的sting从日期列中选择日期。我只在这里做了演示如何在WHERE子句中使用CASE语句。
使用IIF函数快速切换CASE表达式
随着SQL Server 2012的推出,微软添加了IIF功能。 IIF功能可被视为CASE声明的捷径。在图2中,您可以找到IIF函数的语法。
IIF(布尔表达式,true_value,false_value)
图2:IIF函数的语法
“布尔表达式”是一个有效的布尔表达式,等同于TRUE或FALSE。当布尔表达式等同于TRUE值时,执行“true_value”表达式。如果布尔表达式等于FALSE,则执行“false_value”。就像CASE表达式一样,IIF函数可以嵌套到10个级别。
使用IIF的例子
为了演示如何使用IIF函数替换CASE表达式,让我们回顾一下清单9中使用搜索的CASE表达式的代码。
SELECT OrderAmt,
案件
当OrderAmt> 200 THEN'高$订单'
ELSE'Low $ Order'END AS OrderType
FROM MyOrder;
清单9:简单CASE表达式示例
清单9中的代码只有一个WHEN表达式,用于确定OrderAmt是高位还是低位。如果WHEN表达式“OrderAMT> 200”评估为TRUE,则OrderType值设置为“High $ Order”。如果WHEN表达式的计算结果为FALSE,则为OrderType值设置“Low $ Order”。
使用IIF函数而不是CASE表达式的重写代码可以在清单10中找到。
SELECT OrderAmt,
IIF(OrderAmt> 200,
'高$订单',
'低$订单')AS OrderType
FROM MyOrder;
清单10:使用IIF函数的例子
通过查看清单10,您可以看到为什么IIF函数被认为是CASE表达式的简写版本。用“IIF(”字符串,“THEN”子句替换为逗号,“ELSE”子句替换为逗号,“END”替换为“)”替换CASE一词。当布尔表达式“OrderAmt> 200”为TRUE时,显示值“High $ Order”。当布尔表达式“OrderAmt> 200”被评估为FALSE时,则显示“Low $ Order”。如果运行清单9和10中的代码,您将看到它们都产生完全相同的输出。
嵌套IIF功能的示例
就像CASE表达式SQL Server允许你嵌套IIF函数。在清单11中是嵌套IIF函数的一个例子。
SELECT OrderAmt,
IIF(OrderAmt <100,
'<100美元订单',
(IIF(OrderAmt <200,
'100美元订单',
(IIF(OrderAmt <300,
(IIF(Layaway ='N',
“没有LAYWAY的200美元订单”,
“与Layaway的200美元订单”
)
)
(IIF(Layaway ='N',
“没有Layaway的300美元订单”,
'与Layaway 300美元订单'
)
)
)
)
)
)
)AS OrderAmt_Category
FROM MyOrder;
清单11:嵌套IIF函数的例子
在这个例子中,你可以看到我已经多次使用了IIF函数。每个额外的一个用于IIF功能的“真实值”或“假值”。清单11中的代码与清单7中使用嵌套CASE表达式的代码相同。
限制
与大多数TSQL功能一样,这是有限制的。以下是有关CASE和IIF结构的一些限制。
CASE表达式限制:
CASE表达式中最多只能有10层嵌套。
CASE表达式不能用来控制TSQL语句的执行流程。
IIF功能限制:
IIF条款最多只能有10层嵌套。
概要
CASE表达式和IIF函数允许您将表达式逻辑放置在TSQL代码中,这将根据表达式的评估结果更改代码的结果。通过使用IIF函数和CASE表达式支持的比较表达式,您可以根据比较表达式计算结果为TRUE还是FALSE来执行不同的代码块。 CASE表达式和IIF函数为您提供程序控制,以满足您可能不具备的业务需求。
问题和答案
在本节中,您可以通过回答以下问题来查看使用CASE和IIF构造理解的情况。
问题1:
CASE表达式有两种不同的语法变体:Simple和Searched。以下哪两条语句最好地描述了简单搜索CASE表达式(选择两个)之间的区别。
简单CASE语法仅支持相等运算符,而搜索CASE语法支持多个运算符
简单CASE语法支持多个运算符,而Searled CASE语法仅支持相等运算符
简单CASE语法在WHEN子句之后指定了其布尔表达式,而搜索CASE语法在CASE语句之后有布尔表达式的左侧,在WHEN子句之后布尔表达式的右侧有布尔表达式的右侧。
简单CASE语法在CASE语句后面布尔表达式的左侧,在WHEN子句后面布尔表达式的右侧,而搜索CASE表达式在WHEN子句后面有布尔表达式
问题2:
如果CASE表达式有多个THEN / ELSE子句执行的WHEN子句计算为TRUE,
执行最后一个计算为TRUE的WHEN子句的THEN表达式。
执行第一个WHEN子句的THEN表达式,其值为TRUE。
执行所有THEN表达式的WHEN子句,其值为TRUE。
ELSE表达式被执行
问题3:
CASE表达式或IIF函数有多少个嵌套级别?
8
10
16
32
回答:
问题1:
答案是a和d。一个简单的CASE语句只能使用相等运算符,而Searled CASE表达式可以处理多个运算符以及复杂的布尔表达式。另外,简单CASE语法在单词CASE之后有相等操作符的左边部分,在WHEN之后有相等操作符的右边部分。 Searled CASE表达式必须在WHEN子句之后完成布尔运算(左边部分,运算符,右边部分)
问题2:
正确答案是b。如果多个WHEN子句评估为TRUE,则SQL Server仅执行第一个WHEN子句的THEN部分,其值为TRUE。所有其他THEN子句的任何其他WHEN子句被评估为TRUE被跳过。
问题3:
正确答案是b。 CASE表达式和IIF函数最多只支持10个嵌套层次。
本文是T-SQL的基础:超越基础的阶梯