第九周翻译
t - sql的阶梯:超越基础水平6:使用表达式和IIF函数
通过格雷戈里·拉森,2016/04/20(第一次出版:2014/04/09)
该系列
本文是楼梯系列的一部分:楼梯t - sql:除了基础知识
从他的t - sql DML楼梯后,格雷戈里·拉森涵盖了更高级的子查询等方面的t - sql语言。
有时,你需要写一个TSQL语句能够返回不同TSQL表达式基于另一个表达式的评价。 当你需要这种功能可以使用表达式或IIF函数来达到这个要求。 在本文中,我将回顾这一案件,IIF语法和显示你如何表达和IIF函数的例子。
理解这样表达
transact - sql例表达式允许你在TSQL放置条件逻辑代码。 这条件逻辑提供了一种不同的可执行代码块在你TSQL语句根据条件逻辑或真或假的评价。 您可以将多个条件表达式的一个表达式。 当你在条款中,有多个条件表达式值为TRUE的第一个表达式将评估你的TSQL语句的代码块。 为了更好地理解如何表达作品我将审查情况下表达的语法,然后经过许多不同的例子。
情况下表达式语法
这样表达有两个不同的格式:简单搜索。 每一种类型都有一个稍微不同的格式如图1所示。
简单的案例表达:案例input_expression
当when_expression result_expression[… n]
(其他else_result_expression)
结束搜索案例表达:情况下
当Boolean_expression result_expression[… n]
(其他else_result_expression)
结束
图1:表达式语法
通过回顾情况下表达的两种不同的格式在图1中可以看到每个格式提供了一种不同的方式来识别一个多个表达式,确定情况下表达的结果。 有两种类型的情况下,一个布尔测试时为每个条款执行。 对这个简单的例子表达左手边的布尔测试后出现的话,叫做“input_expression”,和右手站后的布尔测试是正确的单词时,称为“当表达式”。 对这个简单的例子表达之间的运算符“input_expression”和“when_expression”总是相等操作符。 而搜索情况下表达每个当条款将包含一个“Boolean_expression”。 这种“Boolean_expression”可以是一个简单的布尔表达式用一个运营商,与许多不同的条件或一个复杂的布尔表达式。 此外,搜索情况下表达式可以使用布尔操作符的全套。
不管这种情况下使用格式,每个条款的顺序比较的时候出现。 案件的结果表达式时将根据第一条款评估为TRUE。 如果没有当其他子句的计算结果为真时,则返回表达式。 时省略其他条款和条款评估为TRUE,然后返回一个NULL值。
样本数据的例子
为了有一个表来演示使用情况下表达我将使用清单1中的脚本创建一个示例表命名MyOrder。 如果你想跟随我的例子,在您的SQL服务器实例上运行它们可以创建这个表在数据库中。
创建表MyOrder(
int ID身份,
OrderDT日期,
OrderAmt小数(10,2),
分期预付char(1));
插入MyOrder值
(' 12-11-2012 ',10.59,NULL),
200.45(' 10-11-2012 ',' 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),
350.17(' 10-08-2013 ',' N '),
(' 04-05-2010 ',180.76,NULL),
(' 03-27-2011 ',1.49,NULL);
清单1:MyOrder创建示例表
用一个简单的案例表达和其他表情
演示如何简单的案例表达格式工作让我运行清单2中的代码。
选择一年(OrderDT)OrderYear,
例年(OrderDT)
当2014年“第一年”
当2013年之后的第二年
当2012年“三年级”
其他4年和超越YearType结束
从MyOrder;
清单2:简单的表达式与其他表达式
让我先谈谈为什么这是一个简单的表达式。 如果你回顾清单2中的代码后你可以看到我指定表达式”这个词(OrderDT)”,然后我跟着,通过三种不同当表达式指定每一个拥有不同的一年,从2014年开始。 因为我指定的表达式之间的情况,第一个当关键字告诉SQL服务器,这是一个简单的表达式。
当我的简单的情况下计算表达式它使用相等操作符(" = ")之间的“年(向数据库)“价值和不同的表达式。 因此,清单1中的代码将显示”第一年”YearType行与列OrderDT年的价值”2014年”,或者它将显示“第二年“行了OrderDT年的“2013年”或它将显示”三年级“行了OrderDT年的“2012”。 如果今年的OrderDT不匹配的任何表达式然后其他条件时将显示“年4和超越”。
当我运行清单2中的代码1所示的输出结果。
OrderYear YearType
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
2012年3
2012年3
2014年1
2014年1
2013年2
2009年及以后
2013年2
2010年及以后
2011年及以后
结果1:当运行清单2所示的结果
使用一个简单的表达式没有其他表达式
让我运行清单3中的代码将显示一个简单的案例表达时会发生什么没有一个ELSE子句。
选择一年(OrderDT)OrderYear,
例年(OrderDT)
当2014年“第一年”
当2013年之后的第二年
当2012年“三年级”作为YearType结束
从MyOrder;
清单3:简单的案例表达没有其他条款
清单3中的代码就像清单2中代码但没有一个ELSE子句。 当我运行清单3中的代码产生结果2所示的结果。
OrderYear YearType
- - - - - - - - - - - - - - - - - - - - -
2012年3
2012年3
2014年1
2014年1
2013年2
2009年零
2013年2
2010年零
2011年零
结果2:当运行清单3的结果
通过评审的输出结果2中可以看到,当今年的OrderDT在MyOrder表不符合条款条件时的任何SQL Server为YearType值显示“零”这一行。
使用一个搜索表达式
在简单的情况下表达基于相等操作符表达式进行评估。 的搜索表达式可以使用其他运营商,和表达式语法有点不同。 证明这个让我们来看看清单4中的代码。
选择一年(OrderDT)OrderYear,
情况下
当(OrderDT)= 2014年的第一年
当(OrderDT)= 2013年的第二年
当(OrderDT)= 2012年“三年级”
当一年(OrderDT)< 2012年4和超越的
作为YearType结束
从MyOrder;
清单4:搜索表达式
如果你看看清单4中的代码时,你可以看到情况后直接遵循条款没有文本之间的两个条款。 这告诉SQL Server这个搜索表达式。 还要注意每个当条款后的布尔表达式。 正如你所看到的并不是所有的这些布尔表达式使用相等操作符,最后当表达式使用小于(<)算子。 清单4中的情况下表达逻辑与清单2中的表达情况。 因此当我运行清单4中的代码会产生相同的结果,结果1所示。
如果多个表达式返回当表达式计算为TRUE ?
可能会有不同的情况下当表达式计算为TRUE在单一情况下的表达式。 这时SQL服务器将返回结果表达式与第一个当表达式计算为true。 因此当从句的顺序将控制你会返回什么结果你的案子表达式时如果多个条款评估为TRUE。
展示我们用这样的表情来显示”200美元的订单“当OrderAmt在200美元的范围之内。”100美元的订单“当OrderAmt是100美元的范围内“< 100美元的订单“当OrderAmt小于100美元当一个OrderAmt不属于任何的这些类别分类的顺序为“300美元以上的订单”。 让我们回顾一下清单5中的代码演示当多个当表达式计算为TRUE时向其中一个订单进行分类OrderAmt_Category值。
选择OrderAmt,
情况下
当OrderAmt < 300 300美元订单
当OrderAmt < 200 200美元订单
当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或200以上的订单,我们知道这是不正确的。 这是因为我只用不到(“<”)操作符简单地分类订单导致多个当表达式计算为TRUE在我的例子中表达。 当从句的顺序不允许返回正确的表达式。
通过重建创造条件条款时我可以得到我想要的结果。 清单6中的代码清单5是一样的但我有重新定购商品当条款正确分类我的命令。
选择OrderAmt,
情况下
当OrderAmt < 100 < 100美元订单
当OrderAmt < 200 200美元订单
当OrderAmt < 300 300美元订单
其他的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中,您可以看到,通过改变当表达式的顺序为每个订单我得到了正确的结果。
嵌套情况下表达式
偶尔你可能会需要额外的测试使用情况进一步分类数据的表达式。 当你可以用一个嵌套的情况下发生的表达式。 清单7中的代码显示了一个示例的嵌套表达式进一步分类订单MyOrder表来确定订单购买使用分期预付当订单价值超过200美元。
选择OrderAmt,
情况下
当OrderAmt < 100 < 100美元订单
当OrderAmt < 200 200美元订单
当OrderAmt < 300
情况下
当礼物= ' N '
然后200美元订单没有礼物的
其他与分期预付200美元订单结束
其他的
情况下
当礼物= ' N '
然后300美元订单没有礼物的
其他与分期预付300美元订单结束
作为OrderAmt_Category结束
从MyOrder;
清单7:嵌套语句
清单7中的代码类似于清单6中的代码。 唯一的区别是我添加了一个额外的情况下表达是否有秩序MyOrder表购买使用礼物选项,只允许购买超过200美元。 记住当你窝案表达式SQL服务器只允许有10的嵌套级别。
可以使用其他地方的表达
到目前为止,我所有的示例使用表达式创建一个结果字符串通过将选择列表的情况下表达TSQL select语句。 您还可以使用一个案例表达在一个更新,删除和设置语句。 另外这样表达可以结合使用,在那里,ORDER BY和条款。 在清单8中,我使用一个案例表达一个WHERE子句。
SELECT *
从MyOrder
情况下一年(OrderDT)
当2014年“第一年”
当2013年之后的第二年
当2012年“三年级”
其他4和超越的结束=“1年”;
清单8:在WHERE子句中使用情况下表达
在清单8中我只想返回的订单MyOrder表中的行“第一年”。 为此我把相同的情况下表达我在WHERE子句中使用清单2中。 我用条件表达式的左边部分,所以它会产生不同的基于“…年”字符串OrderDT列。 然后我测试产生这样的字符串表达式是否等于“1年”的价值,一行的时候将会回来MyOrder表。 记住我不建议使用案例表达选择日期的日期列使用“1年”这样的刺痛,当有其他更好的方法,如使用一年对于一个给定的函数选择行。 我只有这样做来演示如何使用一个CASE语句的WHERE子句。
简化表达式使用IIF函数
与SQL Server 2012的引入,微软添加IIF函数。 IIF函数可以被认为是一个快捷方式到CASE语句。 在图2中您可以找到IIF的语法功能。
国际金融协会(boolean_expression true_value false_value)
图2:IIF的语法功能
“Boolean_expression”是一个有效的布尔表达式,相当于真或假。 当布尔表达式等同于真实价值然后执行“true_value”表达式。 如果布尔表达式相当于假“false_value”执行。 就像这样表达IIF函数可以嵌套10水平。
使用IIF的例子
演示如何使用IIF函数替换表达式中,让我们来回顾一下的代码使用一个搜索表达式在清单9中。
选择OrderAmt,
情况下
当OrderAmt > 200美元高阶的
其他低美元订单的最终订单类型
从MyOrder;
清单9:简单表达式的例子
清单9中的代码时只有一个表达式,用于确定OrderAmt要么是高或低美元的订单。 如果当表达“OrderAMT > 200”真的,那么评估订单类型值设置为“高美元的秩序”。 如果当表达式的求值结果为FALSE,那么“低美元秩序”设置的订单类型价值。
重写代码使用一个IIF函数而不是表达在清单10中可以找到。
选择OrderAmt,
国际金融协会(OrderAmt > 200年,
“高美元订单”,
“低美元订单”)作为订单类型
从MyOrder;
清单10:使用IIF函数示例
清单10通过观察可以看到为什么IIF函数被认为是速记版本的情况下表达。 情况下被替换为”一词IIF(”字符串,“然后”条款被替换为一个逗号,“其他”条款被替换为一个逗号和“结束”被替换为“)”。 当布尔表达式”OrderAmt > 200“真值”显示高美元的秩序”。 当布尔表达式的OrderAmt > 200“假然后评估”显示低美元的秩序”。 如果你运行清单9和10中的代码你会看到他们都产生相同的输出。
IIF嵌套函数的例子
就像这样表达SQL Server允许您巢IIF功能。 在清单11中是IIF嵌套函数的一个例子。
选择OrderAmt,
国际金融协会(OrderAmt < 100
< 100美元的订单,
(IIF(OrderAmt < 200,
100美元的订单,
(IIF(OrderAmt < 300,
(IIF(分期预付= ' N ',
200美元的订单没有礼物,
200美元订单预约购物
)
),
(IIF(分期预付= ' N ',
300美元的订单没有礼物,
300美元订单预约购物
)
)
)
)
)
)
)作为OrderAmt_Category
从MyOrder;
清单11:嵌套一个IIF函数的例子
在这个例子中你可以看到,我已经多次IIF函数使用。 每增加一个是用于“真实价值”或“假价值”的国际金融功能。 清单11中的代码相当于使用嵌套的情况下表达式的代码如清单7所示。
限制
如同大多数TSQL功能有限制。 下面是一些限制要注意关于此案,IIF构造。
案例表达的局限性:
- 你可以只有10水平的嵌套表达式。
- 情况下表达式不能用于控制流TSQL语句的执行。
IIF功能限制:
- 你只能有10水平的嵌套IIF条款。
总结
表达和IIF功能允许您将表达逻辑TSQL代码中,将改变您的代码的结果基于一个表达式的计算结果。 通过比较表达支持的国际金融功能和表达你可以有不同的代码块执行取决于比较表达式的求值结果为TRUE或FALSE。 表达和IIF函数提供你编程控制以满足业务需求,否则你可能不会有。
问题和答案
在本节中,您可以检查你懂了如何使用案例和IIF构造通过回答下列问题。
问题1:
有两种不同的语法差异的案例表达:简单搜索。 这两个语句下面最好的描述一个简单的区别和搜索表达式(选择两个)。
- 简单的例子中语法只支持相等操作符,而搜索语法支持多个运营商
- 简单的语法支持多个运营商,而搜索语法只支持相等操作符
- 简单的例子中语法WHEN子句有其指定的布尔表达式后,而搜索语法有布尔表达式的左边CASE语句后,和布尔表达式的右边WHEN子句之后。
- 简单的语法有左边的布尔表达式的CASE语句和右侧后WHEN子句后的布尔表达式,而搜索情况下表达WHEN子句后的布尔表达式
问题2:
如果表达有多个条款时,评估为TRUE,然后执行/ ELSE子句吗?
- 然后表达式的最后当条款执行评估为TRUE。
- 然后表达式的第一个值为TRUE的条款执行。
- 所有表达式的值为TRUE的条款执行。
- 执行其他表达式
问题3:
多少个嵌套级别情况下表达或IIF功能有吗?
- 8
- 10
- 16
- 32
答案:
问题1:
答案是a和d。一个简单的CASE语句只能使用相等操作符,而搜索情况下表达可以处理多个运营商和以及复杂的布尔表达式。 另外简单的语法有左手相等操作符的一部分后,右手一词的一部分相等操作符后当这个词。 搜索案例表达完成布尔运算(左手部分运营商的右手部分)后WHEN子句
问题2:
正确的答案是b。如果多个条款评估为TRUE时然后SQL Server只有执行的第一条款部分评估为TRUE。 所有其他条款对任何其他条款,评估为TRUE时跳过。
问题3:
正确的答案是b。这样表达和IIF函数只支持10嵌套的水平。
