SQL Server 2012 T-SQL系列技巧之T-SQL格式转化

本系列关于SQL Server 2012 T-SQL技巧的文章共四部分，由SQL Server专家Robert Sheldon提供。第一部分讲的是“数据值的乐趣”，第二部分介绍的是“T-SQL函数”。本文是第三部分，主要探讨“T-SQL格式转化功能”。　　SQL Server现在包含有一组函数，可以基于单个整数生成日期和时间。

这些函数里，第一个就是“DATEFROMPARTS”函数，它有三个参数：年，月和日，该函数的功能是把整数参数转换为“DATE”值。　　DATEFROMPARTS, TIMEFROMPARTS, DATETIMEFROMPARTS 和 DATETIME2FROMPART……

查看全文

我们一直都在努力坚持原创.......请不要一声不吭，就悄悄拿走。

我原创，你原创，我们的内容世界才会更加精彩！

【所有原创内容版权均属TechTarget，欢迎大家转发分享。但未经授权，严禁任何媒体（平面媒体、网络媒体、自媒体等）以及微信公众号复制、转载、摘编或以其他方式进行使用。】

微信公众号

TechTarget

官方微博

TechTarget中国

取消回复

本系列关于SQL Server 2012 T-SQL技巧的文章共四部分，由SQL Server专家Robert Sheldon提供。第一部分讲的是“数据值的乐趣”，第二部分介绍的是“T-SQL函数”。本文是第三部分，主要探讨“T-SQL格式转化功能”。

　　SQL Server现在包含有一组函数，可以基于单个整数生成日期和时间。这些函数里，第一个就是“DATEFROMPARTS”函数，它有三个参数：年，月和日，该函数的功能是把整数参数转换为“DATE”值。

　　DATEFROMPARTS, TIMEFROMPARTS, DATETIMEFROMPARTS 和 DATETIME2FROMPARTS

　　例如，下面的“SELECT”语句使用“DATEFROMPARTS”函数生成“June 14, 2012”的日期值“DATE”：

　　SELECT DATEFROMPARTS(2012, 6, 14);

　　该函数的第一个参数是“2012”，表示年。第二个参数是“ 6”，表示六月;第三个参数是“14”，表示是该月的14号。

　　下一个函数是“TIMEFROMPARTS”，该函数有五个参数，分别是：小时，分钟，秒，秒小数部分和精度。最后一个参数指定了倒数第二个参数秒小数部分的精度。该函数根据指定的这五个参数生成一个“TIME”值。例如，下面的“SELECT”语句返回“23:04:18.053”的“TIME”值。

　　SELECT TIMEFROMPARTS(23, 4, 18, 53, 3);

　　在本例中，返回的“TIME”值精度为3，这是在最后一个参数中指定的。尽管第四个参数(秒的小数部分)只指定了两位整数，不过返回值仍然是三位小数位。

　　如果你想返回“DATETIME”值，你可以使用“DATETIMEFROMPARTS”函数。在这种情况下，你需要传入七个参数，前三个参数代表日期信息，后四个代表时间。“DATETIMEFROMPARTS”函数不支持精度参数。不过，精度默认总是“3”。例如，下面的“SELECT”语句使用该函数返回“2012-06-14 23:04:18.053”的“DATETIME”类型值：

　　SELECT DATETIMEFROMPARTS(2012, 6, 14, 23, 4, 18, 53);

　　你可以看到，该函数使用了七个参数。最后一个参数秒小数部分是“53”，但是返回内容是三位小数位。

　　如果你想指定精度，你可以用“DATETIME2FROMPARTS”函数返回“DATETIME2”值。与“DATETIMEFROMPARTS”函数类似，“DATETIME2FROMPARTS”函数同样需要七个必须的参数返回日期和时间，但是该函数还要求第八个参数——精度——如下面的例子所示：

　　SELECT DATETIME2FROMPARTS(2012, 6, 14, 23, 4, 18, 53, 3);

　　毫无疑问，该函数会返回“2012-06-14 23:04:18.053”的“DATETIME2”值。

　　EOMONTH函数

　　另一个与日期有关的有趣函数是“EOMONTH”，它接收一个日期表达式作为参数，返回指定表达式所在月份的最后一天。在下面的例子中，“EOMONTH”函数根据当前日期获取该月最后一天：

　　SELECT EOMONTH(GETDATE())

　　在本例中，日期表达式是“GETDATE”函数，它返回当前日期和时间。因为该日期返回的日期在2012年六月份，所以“EOMONTH”函数返回六月份最后一天，也就是“2012-06-30”。

　　“EOMONTH”函数还可选接收的第二个参数，要求是一个整数(正数负数都可以)，表示第一个参数中日期之后或之前的月数。例如，下面的“SELECT”语句返回当前月份之后一个月的最后一天：

　　SELECT EOMONTH(GETDATE(), 1)

　　请注意，第二个参数是“1”，它表示计算下一个月的最后一天。因为“GETDATE”函数返回的是六月的日期，所以“EOMONTH”函数返回的最后一天值就是“2012-07-31”。

　　如果你想找上个月的最后一天，你可以在第二个参数中使用负数，如下面例子所示：

　　SELECT EOMONTH(GETDATE(), -1)

　　本例中“EOMONTH”函数返回五月份的最后一天“2012-05-31”。

　　序列

　　从SQL Server 2012开始，你现在可以定义序列数据库对象了。序列提供了生成一组唯一数字值的机制，可以在整个数据库范围内使用，而不是仅局限于一个表，与“IDENTITY”属性的用法有点类似。尽管你可以使用“IDENTITY”属性生成在整个数据库中可用的数字值，但那个过程有点麻烦。序列功能使得一切更容易了。

　　要在数据库中创建序列，要使用“CREATE SEQUENCE”语句，如下面的例子所示：

　　CREATE SEQUENCE dbo.TestSeq AS INT
　　START WITH 101
　　INCREMENT BY 1;

　　该语句在“dbo”模式下创建名为“TestSeq”的序列。该序列生成“INT”值，从“101”开始，每次增量为“1”。也就是说，生成的第一个值是“101”，第二个是“102”，然后是“103”,以此类推。

　　要获取序列值，可以使用“NEXT VALUE FOR”语句，如下面的例子所示：

　　SELECT NEXT VALUE FOR dbo.TestSeq;

　　你可以看到，你只需要在语句中使用序列的名称。第一次运行这条“TestSeq”序列的语句，返回结果是“101”。每次从该序列取值时，该值都会自动加“1”。例如，如果上一次取到的是“1278”，那下次取值返回的就是“1279”。

　　使用结果集

　　虽然使用单个值也很方便，但是整体操作结果集也很有用处，你可以对结果集进行分页等操作，与前面行比较值或者在出错发生时返回指定结果。

　　我们来看看Transact-SQL新增强的哪些功能可以让我们更好地控制结果集。

　　OFFSET 和 FETCH NEXT

　　为了使对结果集分页更容易，T-SQL的“ ORDER BY”语句中现在支持“OFFSET”和“FETCH NEXT”选项。它们一起提供了判断从哪里开始分页以及结果集中有多少行的必要机制。

　　DECLARE @offset INT = 0
　　DECLARE @fetch INT = 10
　　SELECT ProductID, Name
　　FROM Production.Product
　　ORDER BY ProductID
　　OFFSET @offset ROWS
　　FETCH NEXT @fetch ROWS ONLY;

　　“OFFSET”语句制定了在开始行之前跳过的行数。在本例中，我们指定的是“0”(通过给“ @offset”变量赋值实现)，所以一行都不会跳过。但是，要想证明该功能的这一用途，你可以给该语句传递任意整数，这就支持你动态决定跳过的行数了。

　　这种用法对于“FETCH NEXT”语句也是成立的，它决定了返回的行数。在这个例子中，指定数字是“10”(通过“ @fetch”变量设置)，当然你可以指定任何实际数字，而且该数字也可以动态指定。这就意味着你可以让它随着变量值的变化而改变。下表展示了该“SELECT”语句返回的结果。

　　你可以看到，返回了十行。因为“OFFSET”语句指定一行都不跳过，所以这里显示的就是“Product”表的前十行。

　　FIRST_VALUE 和 LAST_VALUE

　　“FIRST_VALUE”函数返回排序列集的第一个值，“LAST_VALUE”返回最后一个值。该函数提供了一种在指定列评估值判断那个值是第一个哪个是最后一个，以及这些值如何排序的途径。然而，该顺序与评估列有关，与整个结果集顺序没什么关系。

　　我们再来看一个例子，更好地理解一下这些函数的用法。下面的“SELECT”语句从“Product”表提取数据，并使用“FIRST_VALUE”和“LAST_VALUE”函数来比较每个产品值与标准成本的关系。

　　SELECT ProductNumber, Name, StandardCost,
　　FIRST_VALUE(StandardCost) OVER(ORDER BY StandardCost) AS LowestCost,
　　LAST_VALUE(StandardCost) OVER(ORDER BY StandardCost
　　RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS HighestCost
　　FROM Production.Product
　　WHERE ProductNumber LIKE 'bk-M%48';

　　“SELECT”列表中的前三项是“ProductNumber，Name 和StandardCost”列。第四项是一个列表达式，使用“FIRST_VALUE”函数判断哪个“StandardCost”量是最低的。该函数有一个参数，在本例中使用的是“StandardCost”列，意思上来自该列的值将显示在结果集中。“FIRST_VALUE”函数还需要“OVER”语句指定待评估列以及该列的值顺序。换句话说，该函数将对“StandardCost”列进行排序(默认是升序)，并提取第一个值，也就是最低值。

　　“LAST_VALUE”函数的用法相同，只是它获取的是最后一个值，升序排列时也就是最大值。然而，这个函数中的“OVER”语句还多需要一个参数“RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING”，为了确保最后一个值给每行都返回了。(要了解该选项的更多细节，以及这连个函数其它方面的信息，请参考SQL Server联机手册。)

　　因为“FIRST_VALUE”函数和“LAST_VALUE”函数用来定义“SELECT”列表中的列表达式，针对结果集包含的列，每个行都列出了最小值和最大值，以及常规列信息。下表展示了该“SELECT”语句返回的结果。