你该知道的-SQL里的这些新语法-函数

2021-08-22 21:55:15

前言

　　最近帮客户做数据库优化的时候发现客户系统使用了很多函数，自己竟然不知道是干啥的，好歹做过好几年开发的我必然不能忍！于是翻了翻资料自己学习了一下随便也分享给群友。

　　巧用函数的霸气作用———我做开发的时候就深深的体会到知道一个简单的函数要省去多少代码量，让功能很简单的就能实现。

　　注：以下提及的方法执行环境需要SQL2012及以上版本

--------------博客地址---------------------------------------------------------------------------------------

原文地址: http://www.cnblogs.com/double-K/

如有转载请保留原文地址!

废话不多说，直接开整-----------------------------------------------------------------------------------------

NO.1 PARSE 和 TRY_PARSE

　　PARSE 函数的功能是把字符串值转换成指定类型，这个记得以前只在写程序的时候用，现在数据库也能用了

    SELECT PARSE('2016/12/07' AS datetime2 USING 'zh-CN' ) AS Result;   

    DECLARE @date1 VARCHAR(8);

    SET @date1 = CONVERT(VARCHAR(17), GETDATE(), 22);

    SELECT PARSE(@date1 AS DATEtime using  'en-GB');

　 TRY_PARSE 如果强制转换失败，则返回 Null。 TRY_PARSE 仅用于从字符串转换为日期/时间和数字类型。

　　注：默写情况因为你不能把字符串转换为“DATE”数据类型，所以这条“SELECT”语句就会报错。但是 T-SQL 现在支持“TRY_PARSE”函数，顼名思义就是支持我们做转换测试的。

　　TRY_PARSE 如果强制转换失败，则返回 Null。

　　另外还有TRY_CONVERT 、TRY_CAST函数功能都类似。

　　具体请参见： https://msdn.microsoft.com/zh-cn/library/hh213316.aspx

　　　　　　　　 https://msdn.microsoft.com/zh-cn/library/hh213126.aspx

NO.2 CHOOSE 函数

　　CHOOSE 函数的功能是从两个或多个值的列表中返回一个值，根据指定索引值进行判断。索引值是从“1”计起的整数，也就是该函数的第一个参数。该参数后面跟着就是值列表。

　　也可以结合业务这样玩

　　还可以这样玩

　　具体请参见：https://msdn.microsoft.com/zh-cn/library/hh213019

NO.3 IIF 函数

　　IIF 函数支持测试表达式，基于测试结果返回特定值。“IIF”函数有三个参数：有效的布尔表达式，如果表达式为真返回的值和如果表达式为假返回的值。(你可以把“IIF”函数看作是“CASE”诧句的简写版。)

　　也可以结合业务这样玩

NO.4 CONCAT 函数

　　CONCAT 采用可变数量的字符串参数，并将它们串联成单个字符串。它需要至少两个输入值；否则将引发错误。所有参数都隐式转换为字符串类型，然后串联在一起。 Null 值被隐式转换为空字符串。如果所有参数都为 Null，则返回 varchar(1) 类型的空字符串。隐式转换为字符串的过程遵循现有的数据类型转换规则。

　　直接使用字符串 “+”拼接

---会返回NULL

declare @a char(10)

set @a = null

select @a + 'a'

---会报错

declare @b int

set @b = 1

select @b + 'a'

　　contact可以这么玩（所有参数都隐式转换为字符串类型，这里的int 类型 11）

SELECT CONCAT ( 'Happy ', 'Birthday ', 11, '/', '' ) AS Result;

　　也可以这么玩

SELECT CONCAT(Name, ' (', ProductNumber, ')') AS NewName

FROM Production.Product

WHERE ProductID = 970;

具体请参见：https://msdn.microsoft.com/zh-cn/library/hh231515.aspx

NO.5 DATEFROMPARTS、TIMEFROMPARTS、DATETIMEFROMPARTS 和 DATETIME2FROMPARTS

　　略去概念描述，一看就懂　　

 SELECT DATEFROMPARTS(2016, 12, 7);  --年，月，日

 SELECT TIMEFROMPARTS(23, 4, 18, 53, 3);  --时，分，秒，秒的小数部分，精度

 SELECT DATETIMEFROMPARTS(2016, 12, 7, 23, 4, 18, 53); --年，月，日，时，分，秒，秒的小数部分，默认3位精度

 SELECT DATETIME2FROMPARTS(2016, 12, 7, 23, 4, 18, 53, 7); --年，月，日，时，分，秒，秒的小数部分，可指定精度

NO.6 EOMONTH 函数

　　一个有趣的函数，返回包含指定日期的月份的最后一天（具有可选偏移量）。

　　具体请参见：https://technet.microsoft.com/zh-cn/library/hh213020.aspx

NO.7 LAG 与 LEAD

　　访问相同结果集中先前行的数据，而用不使用自联接。 LAG 以当前行之前的给定物理偏移量来提供对行的访问。在 SELECT 语句中使用此分析函数可将当前行中的值与先前行中的值进行比较。

概念比较模糊上例子一看就知道了

 WITH test

 as

 (

     select NULL as score

     UNION ALL

     select 10

     UNION ALL

     select 20

     UNION ALL

     select 30

     UNION ALL

     select 40

     UNION ALL

     select 50

 )

 select ROW_NUMBER() over(order by score) as rownum

 ,score

 ,LEAD(score) over(order by score) as nextscore1

 ,LEAD(score,1) over(order by score) as nextscore2

 ,LEAD(score,1,0) over(order by score) as nextscore3

 ,LEAD(score,2) over(order by score) as nextscore4

 ,LAG(score) over(order by score) as previousscore1

 ,LAG(score,1) over(order by score) as previousscore2

 ,LAG(score,1,0) over(order by score) as previousscore3

 ,LAG(score,2) over(order by score) as previousscore4

 from test

　　具体请参见：https://msdn.microsoft.com/zh-cn/library/hh231256.aspx

　　　　　　　　https://msdn.microsoft.com/zh-cn/library/hh213125.aspx

NO.8 序列 SEQUENCE

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

具体参见：https://msdn.microsoft.com/zh-cn/library/ff878091.aspx

　　　　　http://www.cnblogs.com/CareySon/archive/2012/03/12/2391581.html 　　　

NO.9 使用结果集 2012分页方法增强

　　2012分页方法增强不仅使得分页变得更容易，也在效率上有了一定的提升。

具体参见：http://www.cnblogs.com/CareySon/archive/2012/03/09/2387825.html

NO.10 drop table if exists

　　在我们写T-SQL要删除某个对象（表、存储过程等）时，一般会习惯先用IF语句判断该对象是否存在，然后DROP，比如：

create table DropIFExists(a int) 

--老写法

IF OBJECT_ID('dbo.DropIFExists','U') IS NOT NULL

DROP TABLE DropIFExists

--或

IF EXISTS (SELECT * FROM sys.objects where name = 'DropIFExists')

DROP TABLE DropIFExists  

--SQL2016中新增

drop table if exists DropIFExists

NO.11 RESULT SETS

　　WITH RESULT SETS可以重新定义从存储过程中返回结果的字段名和数据类型。这会使得向拥有特定字段名和数据类型的临时表的结果集中插入记录将变得非常容易，并且不需要依赖存储过程返回了哪些内容。

　　WITH RESULT SETS子句同样可以使用在存储过程中，存储过程会返回大量结果集，并且每个结果集都可以自定义各自的字段名和数据类型。

 CREATE PROCEDURE GetPerson

 AS

 BEGIN

 SELECT TOP 10 BUSINESSENTITYID,CONCAT( FirstName ,' ',MiddleName,' ' , LastName) AS Name

 FROM [Person].[Person]

 END

 GO

 EXECUTE   GetPerson

 GO

 EXECUTE   GetPerson

 WITH RESULT SETS

 (

        (

        PersonId INT,

        PersonName VARCHAR(150)

        )

 )

 GO

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

注：此文章为原创，欢迎转载，请在文章页面明显位置给出此文链接！
若您觉得这篇文章还不错请点击下右下角的推荐，非常感谢！

码农公寓

前言