SQL 11 (代号 Denali) 入门 - 第六部分 (CTP 3 中的 T-Sql 功能)
4.68/5 (12投票s)
本文将从 T-Sql 的角度探讨 Denali CTP 3 为我们提供的新功能。
目录
- 引言
- 背景
- TSql 新特性和增强功能
- 结论
引言
微软在技术领域最热门、最令人惊叹的开发之一是于 2010 年 11 月 8 日发布的 **SQL Server 2011（代号 Denali）** 的社区技术预览版 1 (CTP 1)。CTP1 提供 32 位和 64 位版本。正如预期的那样，Denali 为 SQL Server 爱好者带来了许多新功能，无论是开发人员、管理员还是商业智能 (BI) 专业人士。 第一部分 描述了 SSMS 中的功能和增强。 第二部分 重点介绍了 T-SQL 方面的新开发和增强功能。 第三部分 从 SSIS 的角度审视了所做的增强，而 第四部分 讨论了容器数据库。
但这并不是 Denali 的终点。这是一个持续的开发过程，在 CTP1 取得巨大成功后，现在是 2011 年 7 月 12 日发布的 **CTP 3**。正如预期的那样，Denali 正在在各个领域扩展，无论是 T-SQL 方面的增强，全新的界面-Juneau，使用 Project Crescent 的新报表工具，还是通过列存储索引等增强数据仓库过程的检索。Denali 为 SQL Server 数据库带来了新的维度，本系列将从 T-SQL 角度探索 CTP 3 提供的新功能和增强功能。
背景
在过去的几年里，微软为开发人员带来了许多新技术。随着 SQL Server 2005（代号 **Yukon**）的出现，SQL Server 的术语发生了巨大变化，在随后的 SQL Server 2008（代号 **Katmai**）和 SQL 11（代号 **Denali**）等版本中，通过引入新功能、增强功能和改进，保持了相同的步伐。在本文中，我们将探讨 Denali CTP 3 从 T-SQL 角度已经提供的一些新功能。之前我们已经看过 Denali CTP1 在同一主题上的增强。后续文章将重点介绍其他领域所做的增强。
您可以通过以下方式阅读我关于 Denali 的其他文章：
- SQL 11 (代号 Denali) 入门 - 第一部分 (CTP 1 中的 SSMS 功能)
- SQL 11 (代号 Denali) 入门 - 第二部分 (CTP 1 中的 T-Sql 功能)
- SQL 11 (代号 Denali) 入门 - 第三部分 (CTP 1 中的 SSIS 功能)
- SQL 11 (代号 Denali) 入门 - 第四部分 (CTP 1 中的独立数据库)
- SQL 11 (代号 Denali) 入门 - 第五部分 (CTP 3 中的 SSIS 功能)
TSql 新特性和增强功能
数学 Log 函数得到了一些小的增强。
嗯，我们已经有了这个函数很长时间了。但是 Log 函数这次被重载了，增加了一个基数参数。
语法： Log (Float 表达式 [, base])
示例
;With CTE AS ( Select Base = 2 Union All Select Base+1 from CTE Where Base < 10 ) Select Base, Result = LOG (10, Base) from CTE /* Base Result ---- ------ 2 3.32192809488736 3 2.09590327428938 4 1.66096404744368 5 1.43067655807339 6 1.28509720893847 7 1.18329466245494 8 1.10730936496245 9 1.04795163714469 10 1 */
逻辑函数是基于某些布尔条件来确定结果的函数。Denali CTP 3 为我们提供了两个这样的函数，我们现在将进行讨论。
目的：给定一个值列表和一个位置，此函数将返回指定位置的值。
语法： Choose ([Position], [Value1],[Value2],…,[ValueN])
其中，
Position => 要返回的值的位置编号。位置编号从 1 开始
Value1..ValueN => 值列表。
示例 1：正确的位置编号
Select Choose (1,'Simple Choose Demo', 'This is an example') As [Choose Demo] /* Choose Demo ------------ Simple Choose Demo */
解释
在此示例中，我们将位置指定为 1，因此在两个值中，第一个值是结果。
示例 2：位置编号 < 1
Select Choose (0,'Simple Choose Demo', 'This is an example') As [Choose Demo] --OR Select Choose (-1,'Simple Choose Demo', 'This is an example') As [Choose Demo] /* Choose Demo ------------ NULL */
解释
如果位置小于 1，则结果为 null。
示例 3：位置编号 > 值数量
Select Choose (3,'Simple Choose Demo', 'This is an example') As [Choose Demo] /* Choose Demo ------------ NULL */
解释
如果位置大于值数量，则结果为 null。
示例 4：小数位置编号
它甚至接受小数。例如：
Select Choose (2.1,'Simple Choose Demo', 'This is an example') As [Choose Demo] --OR Select Choose (2.99,'Simple Choose Demo', 'This is an example') As [Choose Demo] --OR Select Choose (.5/.25,'Simple Choose Demo', 'This is an example') As [Choose Demo] /* Choose Demo ------------ This is an example */
解释
在这种情况下，它会被四舍五入为整数。
示例 5：Choose 与表达式
假设我们有一个表，如下所示：
Declare @t table(Position int) Insert into @t values(1),(2),(3),(4)
现在如果我们写下面的查询：
Select Choose ((Select Top 1 Position from @t),'Apple','Banana') As [Choose Demo]
我们将收到一个错误。但是，我们可以按以下方式使用它：
Declare @FirstPosition int = (Select Top 1 Position from @t) Select Choose (@FirstPosition,'Apple','Banana') As [Choose Demo]
示例 6：一个更可行的示例
假设我们正在准备一个有四个选项的问答游戏（请忽略表的结构设计。关注 Choose 函数的用法）。现在，如果我们想知道用户选择了哪个选项，我们可以使用此函数来获取答案，如下所示：
Declare @tblQuiz table
(
[QuestionID] int identity
,[UserName] Varchar(100)
,[Question] Varchar(100)
,[AnswerID] int
,[Option1] varchar(50)
,[Option2] varchar(50)
,[Option3] varchar(50)
,[Option4] varchar(50)
)
Insert into @tblQuiz Values
('Deepak','Who was the Indian Skipper when India bagged the Second World Cup Cricket Tournament?',2,'K.Dev','M.S.Dhoni','M.Singh','S.Gavaskar')
,('Deepak','Which nation got the Cricket World Cup 2011?',4,'Srilanka','England','Bangladesh','India')
,('Deepak','Who was the Man of the Tournament in Cricket World Cup 2011?',4,'R.Singh','S.Tendulkar','Y.Singh','Z.Khan')
,('Deepak','Between which two nation the Cricket World Cup 2011 final match was played?',1,'India and Australia','India and Srilanka','WestIndies and Pakistan','Srilanka and South Africa')
Select * from @tblQuiz
/* Results truncated for spacing
QuestionID UserName Question AnsID Opt1 Opt2 Opt3 Opt4
-------- -------- ---- --- ---- --- -- ----
1 Deepak Who ... 2 K.Dev M.S.Dho M.Sin S.Gav
2 Deepak Which... 4 Sri Eng Bang India
3 Deepak Who ... 4 R.Singh S.Ten Y.Singh Z.Khan
4 Deepak Bet ... 1 Ind&Aus Ind&Sri WI&Pak Sri&SA
*/
Select
[UserName]
,[Question]
,[Answer Given] = Choose([AnswerID],[Option1],[Option2],[Option3],[Option4])
from @tblQuiz
/* Output (Results truncated for spacing)
UserName Question Answer Given
-------- -------- ------------
Deepak Who was the.. M.S.Dhoni
Deepak Which nation.. India
Deepak Who was the Man.. Z.Khan
Deepak Between which.. India and Australia
*/
因此，根据 [AnswerID] 列，我们可以轻松找出用户给出的答案。
我们已经熟悉 IF..Else 语句，它在评估布尔值后执行。现在从 Sql 11 Denali CTP 3 开始，我们有了 IIF() 函数。
目的：根据指定的布尔条件返回一个值。
语法： IIF([Condition],[True Value],[ False Value])
其中，
Condition => 任何有效的布尔表达式。
True Value => 如果 Condition 的计算结果为 True，则返回该值。
False Value => 如果 Condition 的计算结果为 False，则返回该值。
注意：它可以被视为 Case 语句的简写版本。
让我们来看一个实际的例子：
示例 1：简单的 IIF()
Select Result = IIF(1=1,'OK','Not OK') /* Result ------ OK */
使用 case 语句可以得到相同的结果，如下所示：
Select Result = Case When 1 = 1 Then 'OK' Else 'Not OK' End
或者使用 IF..Else 块，如下所示：
If(1=1) Print 'OK' Else Print 'Not OK'
示例 2：嵌套的 IIF()。找出两个数字中较大的那个。
Declare @Num1 As Int = 1 Declare @Num2 As Int = 2 Select Result = IIF(@Num1 > @Num2, 'First Number is bigger', IIF(@Num2 > @Num1,'Second number is bigger','Numbers are equal')) /* Result ------- Second number is bigger */
然而，等效的 case 语句将是：
Declare @Num1 As Int = 1 Declare @Num2 As Int = 2 Select Result = Case When @Num1 > @Num2 Then 'First Number is bigger' When @Num2 > @Num1 Then 'Second number is bigger' Else 'Numbers are equal' End
示例 3：在 IIF() 中指定多个条件。找出三个数字中最大的那个。
我们可以始终使用任何条件运算符或逻辑运算符在布尔表达式中指定多个条件，如下所示：
Declare @Num1 As Int = 10
Declare @Num2 As Int = 50
Declare @Num3 As Int = 30
Select Result = IIF(@Num1 > @Num2 And @Num1 > @Num3, 'First Number is biggest',
IIF(@Num2 > @Num1 And @Num2 > @Num3,'Second number is biggest',
'Third Number is biggest'))
等效的 case 语句将是：
Declare @Num1 As Int = 10 Declare @Num2 As Int = 50 Declare @Num3 As Int = 30 Select Result = Case When @Num1 > @Num2 And @Num1 > @Num3 Then 'First Number is bigger' When @Num2 > @Num1 And @Num2 > @Num3 Then 'Second number is bigger' Else 'Third Number is biggest' End
示例 4：一个错误的 IIF()
Select IIF('a' > 'b', Null,Null)
消息 8133，级别 16，状态 1，行 2
在 case 规范中，至少有一个结果表达式必须是 NULL 常量以外的表达式。
但是，以下是可行的：
Declare @null sql_variant
Select IIF('a' = 'b', @null, @null)
将导致 Null
这是因为布尔表达式的两个选项都导致了 @null 变量，该变量的值为 null。
案例 5：IIF() 和 Choose() 函数
让我们再次回到我们在 Choose 函数的最后一个示例中创建的 Quiz 表。
以及那个表，让我们添加一个 Answer 表，如下所示：
Declare @tblAnswer table([QuestionID] int identity, [Correct Answer] varchar(50))
Insert into @tblAnswer Values('M.S.Dhoni'),('India'),('Y.Singh'),('India and Australia')
现在执行以下查询：
;With Cte As( Select [UserName] ,[Question] ,[QuestionID] ,[Answer Given] = Choose([AnswerID],[Option1],[Option2],[Option3],[Option4]) from @tblQuiz ) Select c.*,Result = IIF(c.[Answer Given] = a.[Correct Answer] ,'Correct Answer','Wrong Answer') from Cte c Join @tblAnswer a on a.QuestionID = c.QuestionID /* Results truncated for spacing UserName Question Answer Given Result -------- -------- ------------ ------ Deepak Who was the.. M.S.Dhoni Correct Answer Deepak Which nation.. India Correct Answer Deepak Who was the Man.. Z.Khan Wrong Answer Deepak Between which.. India and Aus Correct Answer */
注意：到目前为止，我希望我们已经看到了一些关于新引入的逻辑函数（IIF 和 Choose）的示例。现在让我们看看 SQL 引擎是如何处理它们的。
为此，我们将创建如下测试环境：
IF EXISTS (SELECT * FROM sys.objects WHERE name = N'tbl_Test' AND type = 'U')
DROP TABLE tbl_Test
GO
SET ANSI_NULLS ON
GO
--Create the table
CREATE TABLE [dbo].[tbl_Test](
[PlayerId] [INT] NOT NULL,
[PlayerName] [VARCHAR](50) NOT NULL,
[BelongsTo] [SMALLINT] NOT NULL,
[PhoneNumber] [INT] NULL,
CONSTRAINT [PK_tbl_Test] PRIMARY KEY CLUSTERED
(
[PlayerId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
--Populate the Cte with some records
;With Cte(Id,Name,BelongsTo,PhoneNo) As (
Select
Id = 1
,Name='Name' + CAST( 1 As Varchar(50))
,BelongsTo = 1
, PhoneNo=12345678
Union All
Select
Id+1
,Name= 'Name' + CAST( Id+1 As Varchar(50))
,BelongsTo = Case When ID <= 250 Then 1
When ID > 250 And ID <= 500 Then 2
When ID > 500 And ID <= 750 Then 3
Else 4
End
, PhoneNo = Case When ID % 2 = 0 Then Null
Else 12345678
End
From Cte
Where Id <100000
)
--Insert the records into the table
Insert Into dbo.tbl_test
Select * From Cte
Option( Maxrecursion 0)
--Display the records
Select *
From tbl_Test
/* Partial output
PlayerId PlayerName BelongsTo PhoneNumber
1 Name1 1 12345678
2 Name2 1 12345678
3 Name3 1 NULL
4 Name4 1 12345678
5 Name5 1 NULL
6 Name6 1 12345678
7 Name7 1 NULL
8 Name8 1 12345678
9 Name9 1 NULL
10 Name10 1 12345678
*/
IIF 在 SQL 引擎中的视图
考虑语句：
Select PlayerId ,PlayerName , PhoneStatus = IIF(PhoneNumber Is Not Null,'Person has a personal phone','Yet to buy a phone') From tbl_Test /* Partial output PlayerId PlayerName PhoneStatus 1 Name1 Person has a personal phone 2 Name2 Person has a personal phone 3 Name3 Yet to buy a phone 4 Name4 Person has a personal phone */
生成的执行计划如下：
可以看出来，IIF 被展开为相应的 Case 语句，这就是操作的进行方式。
一个等效的 case 语句将产生相同的执行计划。
Select PlayerId ,PlayerName , PhoneStatus = Case When PhoneNumber Is Not Null Then 'Person has a personal phone' Else 'Yet to buy a phone' End From tbl_Test
我们甚至可以对这两个函数进行简单的性能测试，结果如下：
| 函数 | CPU 时间 | 已用时间 |
| IIF | 31 毫秒 | 994 毫秒 |
| 情况 | 46 毫秒 | 986 毫秒 |
可以看出，使用新的 IIF 函数并没有带来多少性能提升。请注意，值在其他系统上可能会有所不同，但总体收益不会显著。
Choose 在 SQL 引擎中的视图
考虑以下语句：
Select PlayerId ,PlayerName ,BelongsTo ,PlayerType = CHOOSE(BelongsTo,'Indian Player','USA Player','Japanese Player','Australian Player') From tbl_Test /* Partial output PlayerId PlayerName BelongsTo PlayerType 1 Name1 1 Indian Player 2 Name2 1 Indian Player 3 Name3 1 Indian Player 4 Name4 1 Indian Player */
生成的执行计划如下：
可以看出，与 IIF 一样，Choose 被展开为相应的 Case 语句，这就是操作的进行方式。
一个等效的 case 语句将产生相同的执行计划。
Select PlayerId ,PlayerName ,BelongsTo ,PlayerType = Case When BelongsTo = 1 Then 'Indian Player' When BelongsTo = 2 Then 'USA Player' When BelongsTo = 3 Then 'Japanese Player' When BelongsTo = 4 Then 'Australian Player' End From tbl_Test
我们甚至可以对这两个函数进行简单的性能测试，结果如下：
| 函数 | CPU 时间 | 已用时间 |
| Choose | 141 毫秒 | 1011 毫秒 |
| 情况 | 78 毫秒 | 1048 毫秒 |
可以看出，至少在本次实验中，Case 提供了更好的 CPU 时间（执行查询所用时间），但已用时间（查询运行所需时间）几乎相同。请注意，值在其他系统上可能会有所不同，但总体收益不会显著。
在此版本中引入了两个新的字符串函数：Concat 和 Format，我们将在下面介绍。
顾名思义，它连接字符串。在早期版本的 SQL Server 中，我们可以使用 '+' 符号进行字符串连接。但其缺点是，如果参与连接的类型不是 varchar 类型，则必须进行显式转换，否则会导致错误。然而，新的 Concat() 函数可以处理这种显式转换。
目的：连接可变数量的字符串参数并返回单个字符串。
语法： Concat( Value1,Value2,...,ValueN)
示例 1：简单的 Concat
Select Concat('Hello',' ' ,'Concat') As [Concat]
/* Output
Concat
--------
Hello Concat
*/
示例 2：Concat 字符串与整数
Select Concat('String',10) As [Concat]
/* Output
Concat
-------
String10
*/
示例 3：Concat 与多种数据类型
Select Concat('Sql',11, Null, 'Code Name', 'Denali', 'CTP' ,3) As [MultipleField Concat]
/* Output
MultipleField Concat
---------------------
Sql11Code NameDenaliCTP3
*/
null 被转换为空字符串。而传统的 '+' 符号将返回 null。
Select 'Sql' + CAST(11 as varchar(10)) + Null + 'Code Name Denali CTP 3' As [MultipleField Concat] /* Output MultipleField Concat --------------------- NULL */
我们必须使用 ISNull 或 Coalesce 来获得所需的结果，如下所示：
Select 'Sql' + CAST(11 as varchar(10)) + Coalesce(Null,'') + 'Code Name Denali CTP 3' As [MultipleField Concat] --OR Select 'Sql' + CAST(11 as varchar(10)) + IsNull(Null,'') + 'Code Name Denali CTP 3' As [MultipleField Concat] /* Output MultipleField Concat --------------------- Sql11Code Name Denali CTP 3 */
示例 4：Concat 与表列
Declare @t table(FirstName varchar(10),LastNAme varchar(10)) Insert into @t select 'Niladri','Biswas' Union All Select 'Deepak','Goyal' Select FullName = Concat(Concat(FirstName,' ' ),LastName) from @t /* Output FullName --------- Niladri Biswas Deepak Goyal */
在本例中，我们看到了 Concat 如何与表列结合使用以及它的嵌套。
示例 5：Concat 与单个参数
Select Concat('Single argument') As [Concat]
Error（错误）
消息 189，级别 15，状态 1，行 1 concat 函数需要 2 到 254 个参数
这个函数在 .NET 中已经存在很长时间了，现在已被添加到 SQL Server 中。它会按指示的格式化值。
语法： Format (expression, format [, culture])
其中，
Expression => 要格式化的表达式
Format => 一个有效的 .NET Framework 格式模式。
Culture => 这是可选的，用于指定区域性。
示例 1：日期格式化
Declare @t table(Culture varchar(10))
Insert into @t values('en-US'),('fr')
Declare @dt Date = '06/15/2011'
Select
Culture
,Res1 = FORMAT(@dt,'d',Culture)
,Res2 = FORMAT(@dt,'yyyy/mm/dd',Culture)
From @t
/*
Culture Res1 Res2
------- ---- ----
en-US 6/15/2011 2011/00/15
fr 15/06/2011 2011/00/15
*/
示例 2：货币格式化
Declare @t table(Culture varchar(10))
Insert into @t values('en-US'),('ru'),('no')
Declare @currency int = 200
Select
Culture
,FormattedCurrency = FORMAT(@currency,'c',Culture)
From @t
/*
Culture FormattedCurrency
------- -----------------
en-US $200.00
ru 200,00p.
no kr 200,00
*/
我们还可以指定小数点后要显示的位数。让我们看一看：
Declare @t table(Culture varchar(10))
Insert into @t values('en-US'),('ru'),('no')
Declare @currency money = 10.25
Select
Culture
,Res1 = FORMAT(@currency,'C1',Culture)
,Res2 = FORMAT(@currency,'C2',Culture)
,Res3 = FORMAT(@currency,'C3',Culture)
,Res4 = FORMAT(@currency,'C4',Culture)
From @t
/*
Culture Res1 Res2 Res3 Res4
------- ----- ----- ---- ----
en-US $10.3 $10.25 $10.250 $10.2500
ru 10,3p. 10,25p. 10,250p. 10,2500p.
no kr 10,3 kr 10,25 kr 10,250 kr 10,250
*/
正如我们所注意到的，这个函数是一个很好的补充，它支持指定区域性的高级格式化，而这在使用 Cast 和 Convert 时是一个困难的选择。
Denali CTP 3 的转换函数帮助我们检查数据类型，如果可以进行转换，它就会帮助我们完成。基本上，这些函数是 CLR 函数，并且已经存在于 .NET Framework 中很长时间了。但是，我们现在也可以在 T-SQL 环境中获得相同的效果。
在 SQL Server 11（代号：Denali）CTP 2 之前的版本中，没有选项可以检查从一种数据类型到另一种数据类型的转换是否可能。考虑下面的例子，我们试图将 varchar 转换为 int。
Select CONVERT(int,'Just a string')
或者
Select CAST('Just a string' as int)
执行该语句后，我们将收到错误：
消息 245，级别 16，状态 1，行 1
将 varchar 值 'Just a string' 转换为数据类型 int 时转换失败。
如果我们能提前知道转换是否可能，然后进行转换，那不是很好吗？如果需要，那么 Try_Convert 就是解决方案。
目的：它基本上检查一种类型到另一种类型的转换是否可能，如果可能，则转换为目标类型，否则为 null。
语法： Try_Convert (data type, expression [ , style ])
其中，
Datatype => 目标数据类型
Expression => 要转换的值
Style => 这是一个可选的整数表达式。它指示函数如何翻译表达式。
示例 1：简单的 Try_Convert
Select Try_Convert(int, 'Just a string') As Result1 ,Try_Convert(int, '100') As Result2 ,Try_Convert(int,null) As Result3 ,Try_Convert(Date,'18500412') As Result4 ,Try_Convert(DateTime,'18500412') As Result5 ,Try_Convert(Numeric(10,4),'18500412') As Result6 /* Result1 Result2 Result3 Result4 Result5 Result6 NULL 100 NULL 1850-04-12 1850-04-12 00:00:00.000 NULL */
示例 2：带样式的 Try_Convert
考虑以下：
Select CONVERT(varchar(10),getdate(),101) as Result /* Result ------- 07/21/2011 */
也可以通过以下方式实现：
Select Try_Convert(varchar(10),getdate(),101) as Result
正如我们所观察到的，它是 Convert 和 Cast 函数的增强版本。这是一个已添加到 SQL Server 环境中的 CLR 函数。
我们在 .NET Framework 2.0 中遇到过这个。现在从 CTP 3 开始，Denali 中也有了。
目的：它基本上识别指定的类型是否适用于解析，并返回相应的状态。
语法： TRY_PARSE ( string_value AS data_type [ USING culture ] )
其中，
String_value => 指定的值
Data_type => string_value 将被转换成的目标类型
Culture => 如果需要为 string_value 提供任何区域性来格式化。这是可选的，如果未提供，则使用当前会话语言。
示例 1：简单的 Try_Parse
Declare @t table(Data varchar(10))
Insert into @t values('12'),('a'),('.5'),('2011-22-07')
--Select * from @t
Select
Data
,TryParseDateTime = Try_Parse(Data AS Date)
,TryParseDecimal = Try_Parse(Data AS Decimal)
,TryParseNumeric = Try_Parse(Data AS Numeric(20,10))
,TryParseWithConcat = Try_Parse(Concat(Data,'7') AS int using 'en-US')
from @t
/* Output
Data TryParseDateTime TryParseDecimal TryParseNumeric TryParseWithConcat
12 NULL 12 12.0000000000 127
a NULL NULL NULL NULL
.5 NULL 1 0.5000000000 NULL
2011-22-07 NULL NULL NULL NULL
*/
示例 2：在嵌套的 IIF 中使用 Try Parse
Declare @str as Varchar(2) = '1' Select IIF( Try_Parse(@str as int) Is Not Null ,IIF( Try_Parse(@str as decimal) Is Not Null ,'OK' ,'Conversion failed in second level' ) ,'Conversion failed in first level' ) Result /* Output Result ------ OK */
注意：此函数尚未完全稳定。
Select Try_Parse('0' AS Numeric(10,10)) As [Try_Parse_Example]
将引发错误
消息 6521，级别 16，状态 1，行 1
消息 6521，级别 16，状态 1，行 1
语句执行过程中发生了 .NET Framework 错误
System.Data.SqlTypes.SqlTypeException: 无效的数值精度/小数位数。
System.Data.SqlTypes.SqlTypeException
at System.Data.SqlTypes.SqlDecimal.CheckValidPrecScale(Byte bPrec, Byte bScale)
at System.Data.SqlTypes.SqlDecimal..ctor(Byte bPrecision, Byte bScale, Boolean fPositive, Int32 data1, Int32 data2, Int32 data3, Int32 data4)
at System.Data.SqlServer.Internal.Number.NumberBufferToSqlDecimal(NumberBuffer number, SqlDecimal& value)
at System.Data.SqlServer.Internal.Number.ParseSqlDecimal(String value, NumberStyles options, NumberFormatInfo numfmt)
at System.Data.SqlServer.Internal.SqlParseIntrinsicImpl.<>c__DisplayClass20.
但是，我们总能在这种情况下创建自己的 CLR 函数。下面是一个创建基本 TRY_PARSE 的尝试。
步骤 1
让我们在 Visual Studio 中创建一个新项目。文件->新建->项目->数据库->SQL Server 项目。
接下来，右键单击项目并选择添加->用户定义函数。
从出现的“添加新项”模板中，让我们选择 **用户定义函数**。
让我们单击“添加”按钮并编写以下代码：
using System;
using System.Collections.Generic;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
public static SqlBoolean TryParseFn(string value, string dataType)
{
bool status = TryParse(value, dataType);
return new SqlBoolean(status);
}
/// <summary>
/// Function: TryParse
/// Purpose: Converts a string value to the target type. If succeeds returns true else false
/// </summary>
/// <param name="val"></param>
/// <param name="key"></param>
/// <returns>bool</returns>
private static bool TryParse(string val, string key)
{
/* Dictionary<string, /> dict = new Dictionary<string, />();
dict.Add("int", typeof(Int32));
dict.Add("bigint", typeof(Int64));
dict.Add("datetime", typeof(DateTime));
dict.Add("numeric", typeof(Decimal));
try
{
TypeDescriptor.GetConverter(dict[key]).ConvertFromString(val);
return true;
}
catch
{
return false;
}*/
bool flag = true;
Dictionary<string, System.Type> dict = new Dictionary<string, System.Type>();
dict.Add("int", typeof(Int32));
dict.Add("bigint", typeof(Int64));
dict.Add("datetime", typeof(DateTime));
dict.Add("numeric", typeof(Decimal));
try
{
switch (dict[key].FullName)
{
case "System.Int32":Int32.Parse(val);break;
case "System.Int64":Int64.Parse(val); break;
case "System.DateTime":DateTime.Parse(val); break;
case "System.Decimal":DateTime.Parse(val);break;
}
}
catch
{
flag = !flag;
}
return flag;
}
}
然后生成解决方案。我们会找到一个名为 SqlClassLibrary.dll 的 dll。
现在打开 SSMS 并选择数据库，例如 **ExperimentDB**。
首先，我们需要启用 CLR。此后，让我们执行以下查询：
EXEC sp_configure 'show advanced options' , '1'; -- Enable Advanced option go reconfigure; EXEC sp_configure 'clr enabled' , '1' -- Enables CLR option in SQL SERVER go reconfigure; go EXEC sp_configure 'show advanced options' , '0'; -- Disable Advanced option go /* Message Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install. Configuration option 'clr enabled' changed from 1 to 1. Run the RECONFIGURE statement to install. Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install. */
下一步是在我们的数据库中注册程序集。这可以通过以下方式完成：
a) 通过发出 T-SQL 脚本
b) 无需发出 T-SQL 脚本
(A) 通过发出 T-SQL 脚本创建程序集
--Drop the function if it exists
IF EXISTS(SELECT * FROM sys.objects WHERE type = 'FS' AND schema_id = SCHEMA_ID('dbo')AND name = 'Try_Parse')
DROP FUNCTION dbo.Try_Parse
GO
--Drop the assembly if it exists
IF EXISTS (SELECT * FROM sys.assemblies a WHERE a.name = N'SqlClassLibrary' and is_user_defined = 1)
DROP ASSEMBLY [SqlClassLibrary]
GO
--Create the assembly
CREATE ASSEMBLY SqlClassLibrary
FROM 'D:\CustomTryParse\CLRTryParse\CLRTryParse\bin\Debug\SqlClassLibrary.dll'
WITH PERMISSION_SET = SAFE
GO
CREATE FUNCTION Try_Parse(@value AS NVARCHAR(255), @dataType AS NVARCHAR(255)) RETURNS Bit
AS EXTERNAL NAME SqlClassLibrary.UserDefinedFunctions.TryParseFn;
GO
(B) 无需发出 T-SQL 脚本创建程序集
展开“可编程性”节点->“程序集”->右键单击->“新建程序集”。
在“新建程序集”窗口中，将权限设置为“安全”，并指定程序集路径，然后单击“确定”。
程序集已添加到“程序集”文件夹中。也可以通过发出 T-SQL 命令来验证这一点：
SELECT * FROM sys.assemblies where is_user_defined = 1
然后发出以下命令：
USE [ExperimentDB] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Try_Parse]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) DROP FUNCTION [dbo].[Try_Parse] GO USE [ExperimentDB] GO CREATE FUNCTION [dbo].[Try_Parse](@value [nvarchar](255), @dataType [nvarchar](255)) RETURNS [bit] WITH EXECUTE AS CALLER AS EXTERNAL NAME [SqlClassLibrary].[UserDefinedFunctions].[TryParseFn] GO
这样，我们的工作就完成了。现在我们可以像这样调用我们的 Try_Parse：
Select Result = Case When dbo.Try_Parse('12/12/2011','datetime') = 1
Then 'True'
Else Null
End
/* Output
Result
------
True
*/
这只是一个演示我们自己的 Try_Parse 的小尝试。如果开发人员需要，可以根据他们的意愿对其进行增强。
注意：处理 DateTime 字段时，Try_Parse 存在一些问题。
看下面的例子：
Declare @presentDate datetime = GetDate() Select IIF(Try_Parse( @presentDate As Datetime) IS not null ,CONVERT(varchar(10),EOMonth(@presentDate),110) ,'Sorry conversion not possible' ) Result
消息 8116，级别 16，状态 1，行 3
parse 函数的参数 1 的参数数据类型 datetime 无效。
如果我们更改
Declare @presentDate datetime = GetDate()
to
Declare @presentDate varchar(10) = GetDate()
并运行相同的查询，我们将收到以下错误：
消息 6521，级别 16，状态 1，行 3
语句执行过程中发生了 .NET Framework 错误
System.Data.SqlTypes.SqlTypeException: SqlDateTime 溢出。必须介于 1753 年 1 月 1 日 12:00:00 AM 和 9999 年 12 月 31 日 11:59:59 PM 之间。
System.Data.SqlTypes.SqlTypeException
at System.Data.SqlTypes.SqlDateTime.FromTimeSpan(TimeSpan value)
at System.Data.SqlTypes.SqlDateTime.FromDateTime(DateTime value)
at System.Data.SqlServer.Internal.CXVariantBase.DateTimeToSSDate(DateTime dt)
at System.Data.SqlServer.Internal.SqlParseIntrinsicImpl.
但是，以下是解决方法：
Declare @presentDate DateTime = GetDate() Select IIF(Try_Parse(Convert(varchar(10),@presentDate,101) As Datetime) Is Not null ,CONVERT(varchar(10),EOMonth(@presentDate),110) ,'Sorry conversion not possible' ) Result /* Result ------ 08-31-2011 */
嗯，.NET 开发者不需要对它进行新的介绍，但对于第一次遇到这个函数的人来说，答案是它接受一个字符串数据类型并将其转换为请求的数据类型。
目的：它接受一个字符串数据类型并将其转换为请求的数据类型。
语法： PARSE ( string_value AS data_type [ USING culture ] )
其中，
String_value => 指定的值
Data_type => string_value 将被转换成的目标类型
Culture => 如果需要为 string_value 提供任何区域性来格式化。这是可选的，如果未提供，则使用当前会话语言。
示例 1：从 Varchar 解析到 DateTime
Select ParseExample1 =Parse(CONVERT(varchar(10),getdate(),22) as datetime using 'en-US') /* ParseExample1 ------------- 2011-07-24 00:00:00.0000000 */
示例 2：从 Varchar 解析到 Integer
Select ParseExample2 =Parse('1234' as int)
/*
ParseExample2
-------------
1234
*/
示例 3：Parse 和 Try_Parse
Declare @value as varchar(10) = '$100.00' Declare @culture as varchar(10) = 'en-US' Select Result = IIF( Try_Parse(@value AS money using @culture) Is Not Null ,Parse(@value AS money using @culture) ,'Conversion not possible' ) /* Result ------ 100.00 */
我们可以看出，我们可以使用 Try_Parse 函数来检查转换是否可能，如果可能，我们就可以继续。
Denali CTP 3 带来了一些新的 DateTime 函数，如下所示：
它表示 End Of Month（月末）。这意味着它有助于查找月份的最后一天。在此函数引入之前，我们通常使用类似以下的方法来实现：
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[EOMonth]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) DROP FUNCTION [dbo].[EOMonth] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[EOMonth](@startDate Datetime,@offset int) RETURNS DATETIME AS BEGIN Declare @EOM datetime Select @EOM = DATEADD(mm,@offset,@startDate - DAY(@startDate)+1)-1 Return @EOM END GO Declare @presentDate DateTime = GetDate() Select [PresentDate] = CONVERT(varchar(10),GetDate(),110) ,[EOPrevMonth] = CONVERT(varchar(10),dbo.EOMonth(@presentDate,0),110) ,[EOCurrMonth] = CONVERT(varchar(10),dbo.EOMonth(@presentDate,1),110) ,[EONextMonth] = CONVERT(varchar(10),dbo.EOMonth(@presentDate,2),110) /* PresentDate EOPrevMonth EOCurrMonth EONextMonth ----------- ----------- ----------- ----------- 07-24-2011 06-30-2011 07-31-2011 08-31-2011 */
但是随着这个函数的出现，它将有助于 T-SQL 开发人员轻松地执行相同的操作。
目的：此函数帮助获取给定日期的任何月份的最后一天。它还有一个可选的偏移量，可以帮助计算 N 个月后或之前的月末。
语法： EOMonth (StartDate [, Offset ] )
其中，
StartDate => 输入日期
Offset => 一个整数，表示 N 个月后或之前的月末。
示例 1：简单示例
Declare @presentDate DateTime = GetDate() Select [PresentDate] = CONVERT(varchar(10),GetDate(),110) ,[EOPrevMonth] = CONVERT(varchar(10),EOMonth(@presentDate,-1),110) ,[EOCurrMonth] = CONVERT(varchar(10),EOMonth(@presentDate),110) ,[EONextMonth] = CONVERT(varchar(10),EOMonth(@presentDate,1),110) /* PresentDate EOPrevMonth EOCurrMonth EONextMonth ----------- ----------- ----------- ----------- 07-24-2011 06-30-2011 07-31-2011 08-31-2011 */
示例 2：EOMonth 与 Try_Parse
在使用 EOMonth 函数之前，我们可能需要检查转换是否可能。为此，我们可以将 Try_Parse 与 EOMonth 结合使用。
Declare @presentDate DateTime = GetDate() Select IIF(Try_Parse(Convert(varchar(10),@presentDate,101) As Datetime) Is Not null ,CONVERT(varchar(10),EOMonth(@presentDate),110) ,'Sorry conversion not possible' ) Result /* Result ------ 07-31-2011 */
嗯，函数说明了根据年份、月份和日期等不同部分构建日期。我们已经在 .NET 中看到过这个函数。在 DateTime 结构体的各种重载构造函数中，我们有：
public DateTime(int year, int month, int day);
所以如果我们写：
Console.WriteLine(new DateTime(2011, 7, 23));
输出将是：2011 年 7 月 23 日 12:00:00 AM
现在，借助 SQL Server，我们有了同样的机会。
目的：给定年、月、日等部分，返回一个日期。
语法： DateFromParts(year,month,day)
其中，
Year => 年份值
Month => 月份值
Day => 日期值。
示例 1：简单示例
Select DateFromParts(2011,7,23) [Result] /* Result ------ 2011-07-23 */
示例 2：指定所有 null
Select DateFromParts(null,null,null) [Result] /* Result ------ Null */
示例 3：省略必填字段将引发异常
Select DateFromParts(2011,23) [Result]
消息 174，级别 15，状态 1，行 1
datefromparts 函数需要 3 个参数。
示例 4：为所有字段插入值 2
Select DateFromParts(2,2,2) [Result] /* Result ------ 0002-02-02 */
就像 DateFromParts 函数返回日期一样，TimeFromParts 函数返回时间。
语法： TimeFromParts(hour,minute,seconds,fractions,precision)
示例 1：简单示例
Select TimeFromParts(23,22,15,147,3) [Result] /* Result 23:22:15:147 */
示例 2：指定所有 null
Select TimeFromParts (null,null,null,null,null) [Result]
消息 10760，级别 16，状态 1，行 1
小数位参数无效。数据类型 time 的小数位参数的有效表达式是整型常量和整型常量表达式。
示例 3：省略必填字段将引发异常
Select TimeFromParts (23,22,15) [Result]
消息 174，级别 15，状态 1，行 1
timefromparts 函数需要 5 个参数。
它几乎与 DateFromParts 函数相同，不同之处在于它返回一个日期时间组件。我们已经在 .NET 中看到过这个函数。在 DateTime 结构体的各种重载构造函数中，我们有：
public DateTime(int year, int month, int day, int hour, int minute, int second, int millisecond);
所以如果我们写：
Console.WriteLine(new DateTime(2011, 7, 23, 22, 15, 49, 147).ToString("M/dd/yyyy h:mm:ss.fff tt"));
输出将是：2011 年 7 月 23 日 10:15:49.147 PM
语法： DateTimeFromParts(year,month,day,hour,minute,seconds,milliseconds)
示例 1：简单示例
Select [DateTimeFromPartsExample] = DateTimeFromParts(2011, 7, 23, 22, 15, 49, 147) /* DateTimeFromPartsExample ------------------------ 2011-07-23 22:15:49.147 */
示例 2：指定所有 null
Select DateTimeFromParts (null,null,null,null,null,null,null) [Result] /* Result ------ Null */
示例 3：省略必填字段将引发异常
Select DateTimeFromParts(2011, 7, 23) [Result]
消息 174，级别 15，状态 1，行 1
datetimefromparts 函数需要 7 个参数。
注意：有效日期范围应在 1753 年 1 月 1 日至 9999 年 12 月 31 日之间。
此函数与 DateTimeFromParts 函数几乎相同，只是它返回一个 datetime2 值。此外，它还需要一个精度值。
语法： DateTime2FromParts (year,month,day,hour,minute,seconds,fractions,precision)
示例 1：简单示例
Select DateTime2FromParts(2011, 7, 23,22,15,49,147,3) [Result] /* Result 2011-07-23 22:15:49.147 */
示例 2：指定所有 null
Select DateTime2FromParts (null,null,null,null,null,null,null,null) [Result]
消息 10760，级别 16，状态 1，行 1
小数位参数无效。数据类型 datetime2 的小数位参数的有效表达式是整型常量和整型常量表达式。
示例 3：省略必填字段将引发异常
Select DateTime2FromParts(2011, 7, 23,22,15,49,147) [Result]
消息 174，级别 15，状态 1，行 1
datetime2fromparts 函数需要 8 个参数。
与 DateTimeFromParts 类似，我们有 SmallDateTimeFromParts，它返回给定日期和时间的 smalldatetime 值。
语法： SmallDateTimeFromParts(year,month,day,hour,minute)
示例 1：简单示例
Select SmallDateTimeFromParts(2011, 7, 23,22,15) [Result] /* Result 2011-07-23 22:15:00 */
示例 2：指定所有 null
Select SmallDateTimeFromParts (null,null,null,null,null) [Result] /* Result Null */
示例 3：省略必填字段将引发异常
Select SmallDateTimeFromParts(2011, 7, 23) [Result]
消息 174，级别 15，状态 1，行 1
smalldatetimefromparts 函数需要 5 个参数。
此函数返回一个 datetimeoffset 值。
语法： DateTimeOffsetFromParts (year, month, day, hour, minute, seconds, fractions, houroffset, minuteoffset, precision)
示例 1：简单示例
Select DateTimeOffsetFromParts (2011,7,24,0,15,44,120,0,0,3) [Result] /* Result 2011-07-24 00:10:44.120 +00:00 */
示例 2：指定所有 null
Select DateTimeOffsetFromParts (null,null,null,null,null,null,null,null,null,null) [Result]
消息 10760，级别 16，状态 1，行 1
小数位参数无效。数据类型 datetime2 的小数位参数的有效表达式是整型常量和整型常量表达式。
示例 3：省略必填字段将引发异常
Select DateTimeOffsetFromParts (2011,7,24,0,15,44,120) [Result]
消息 174，级别 15，状态 1，行 1
datetimeoffsetfromparts 函数需要 10 个参数。
在 Denali CTP 3 中，Over 子句得到了很大的增强。让我们如下查看它们：
这些关键字指定函数将应用于窗口中用于计算函数结果的每一行。Rows 以物理单位指定窗口，而 Range 以逻辑偏移量指定。这些关键字必须与 order by 子句结合使用。
定义窗口的开始点（And 子句之前的表达式）和结束点（And 子句之后的表达式）。
它表示窗口从分区的第一行开始。
它表示窗口在分区的最后一行结束。
根据指定的 Rows 或 Range，它表示窗口从开始行或值开始。
注意：如果我们完全忽略窗口子句，则默认考虑 Range Between Unbounded Preceding and Current Row。
除非另有说明，我们将使用以下脚本来演示分析函数。我们将有两个表：a) MatchTable 和 b) PlayerTable。
为了演示 Lead、Lag、First_Value、Last Value、Percentile_Cont 和 Percentile_Disc，我们将使用 MatchTable；而对于其余的，将使用 PlayerTable。
MatchTable 生成脚本
IF EXISTS (SELECT * FROM sys.objects WHERE name = N'MatchTable' AND type = 'U')
DROP TABLE MatchTable
GO
SET ANSI_NULLS ON
GO
--Create the table
CREATE TABLE [dbo].[MatchTable](
[MatchID] [int] IDENTITY(1,1) NOT NULL,
[MatchGroup] [varchar](8) NULL,
[MatchBetween] [varchar](50) NULL,
[ScheduleDate] [date] NULL
) ON [PRIMARY]
GO
--Insert records
Insert Into MatchTable Values
('Group-A','India VS Australia','08/14/2011')
,('Group-A','India VS Pakistan','08/15/2011')
,('Group-A','India VS Newzealand','08/16/2011')
,('Group-A','Australia VS Pakistan','08/17/2011')
,('Group-A','Australia VS Newzealand','08/18/2011')
,('Group-A','Newzealand VS Pakistan','08/19/2011')
,('Group-B','USA VS WestIndies','08/20/2011')
,('Group-B','USA VS Ireland','08/21/2011')
,('Group-B','USA VS Bangaladesh','08/22/2011')
,('Group-B','WestIndies VS Ireland','08/23/2011')
,('Group-B','WestIndies VS Bangaladesh','08/24/2011')
,('Group-B','Ireland VS Bangaladesh','08/25/2011')
-- Project the records
Select * From MatchTable
/* Result
MatchID MatchGroup MatchBetween ScheduleDate
1 Group-A India VS Australia 2011-08-14
2 Group-A India VS Pakistan 2011-08-15
3 Group-A India VS Newzealand 2011-08-16
4 Group-A Australia VS Pakistan 2011-08-17
5 Group-A Australia VS Newzealand 2011-08-18
6 Group-A Newzealand VS Pakistan 2011-08-19
7 Group-B USA VS WestIndies 2011-08-20
8 Group-B USA VS Ireland 2011-08-21
9 Group-B USA VS Bangaladesh 2011-08-22
10 Group-B WestIndies VS Ireland 2011-08-23
11 Group-B WestIndies VS Bangaladesh2011-08-24
12 Group-B Ireland VS Bangaladesh 2011-08-25
*/
PlayerTable 生成脚本
IF EXISTS (SELECT * FROM sys.objects WHERE name = N'PlayerTable' AND type = 'U')
DROP TABLE PlayerTable
GO
SET ANSI_NULLS ON
GO
--Create the table
CREATE TABLE [dbo].[PlayerTable](
PlayerID INT IDENTITY(1001,1),
PlayerName VARCHAR(15),
BelongsTo VARCHAR(15),
MatchPlayed INT,
RunsMade INT,
WicketsTaken INT,
FeePerMatch NUMERIC(16,2)
) ON [PRIMARY]
GO
--Insert records
INSERT INTO PlayerTable(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('A. Won','India',10,440,10, 1000)
INSERT INTO PlayerTable(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('A. Cricket','India',10,50,17, 4000)
INSERT INTO PlayerTable(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('B. Dhanman','India',10,650,0,3600)
INSERT INTO PlayerTable(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('C. Barsat','India',10,950,0,5000)
INSERT INTO PlayerTable(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('A. Mirza','India',2,3,38, 3600)
INSERT INTO PlayerTable(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('M. Karol','US',15,44,4, 2000)
INSERT INTO PlayerTable(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('Z. Hamsa','US',3,580,0, 400)
INSERT INTO PlayerTable(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('K. Loly','US',6,500,12,8000)
INSERT INTO PlayerTable(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('S. Summer','US',87,50,8,1230)
INSERT INTO PlayerTable(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('J.June','US',12,510,9, 4988)
INSERT INTO PlayerTable(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('A.Namaki','Australia',1,4,180, 9999)
INSERT INTO PlayerTable(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('Z. Samaki','Australia',2,6,147, 8888)
INSERT INTO PlayerTable(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('MS. Kaki','Australia',40,66,0,1234)
INSERT INTO PlayerTable(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('S. Boon','Australia',170,888,10,890)
INSERT INTO PlayerTable(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('DC. Shane','Australia',28,39,338, 4444)
INSERT INTO PlayerTable(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('S. Noami','Singapore',165,484,45, 5678)
INSERT INTO PlayerTable(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('Z. Biswas','Singapore',73,51,50, 2222)
INSERT INTO PlayerTable(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('K. Dolly','Singapore',65,59,1,9999)
INSERT INTO PlayerTable(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('S. Winter','Singapore',7,50,8,128)
INSERT INTO PlayerTable(PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) VALUES('J.August','Singapore',9,99,98, 890)
-- Project the records
Select * from PlayerTable
/*Result
PlayerID PlayerName BelongsTo MatchPlayed RunsMade WicketsTaken FeePerMatch
1001 A. Won India 10 440 10 1000.00
1002 A. Cricket India 10 50 17 4000.00
1003 B. Dhanman India 10 650 0 3600.00
1004 C. Barsat India 10 950 0 5000.00
1005 A. Mirza India 2 3 38 3600.00
1006 M. Karol US 15 44 4 2000.00
1007 Z. Hamsa US 3 580 0 400.00
1008 K. Loly US 6 500 12 8000.00
1009 S. Summer US 87 50 8 1230.00
1010 J.June US 12 510 9 4988.00
1011 A.Namaki Australia 1 4 180 9999.00
1012 Z. Samaki Australia 2 6 147 8888.00
1013 MS. Kaki Australia 40 66 0 1234.00
1014 S. Boon Australia 170 888 10 890.00
1015 DC. Shane Australia 28 39 338 4444.00
1016 S. Noami Singapore 165 484 45 5678.00
1017 Z. Biswas Singapore 73 51 50 2222.00
1018 K. Dolly Singapore 65 59 1 9999.00
1019 S. Winter Singapore 7 50 8 128.00
1020 J.August Singapore 9 99 98 890.00
*/
目的：此函数返回表中下一行的结果集。
语法：Lead(expression [,offset [,default] ] ) over( [ Partition_By_clause] order by clause)
其中，
Expression => 表列或内置函数，但不能是分析函数。
Offset => 这是可选的，表示表中相对于当前行的物理偏移量。如果未指定，则默认值为 1，并且不能接受负数。
Default => 这也是可选的。如果未指定，则当偏移量超出表边界时，将返回默认的 null。
Partition_By_clause => 分区查询结果集。这也是可选的。
Order By Clause => 指示分区内数据的排序方式。
示例 1：查找下一行结果/跳过第一行
假设我们想查看下一场比赛的日期和下一场比赛的对阵双方。我们可以使用 Lead 函数轻松完成，如下所示：
Select MatchID ,MatchGroup ,MatchBetween ,ScheduleDate ,NextMatchBetween = Lead (MatchBetween) Over(Order by ScheduleDate) ,NextMatchDate = Lead (ScheduleDate) Over(Order by ScheduleDate) From MatchTable --OR Select MatchID ,MatchGroup ,MatchBetween ,ScheduleDate ,NextMatchBetween = Lead (MatchBetween,1) Over(Order by ScheduleDate) ,NextMatchDate = Lead (ScheduleDate,1) Over(Order by ScheduleDate) From MatchTable
现在让我们尝试理解 Lead 函数的行为。我们没有在此查询中指定任何偏移量，因此默认值 1 已被假定。因此，它从第二行开始。但是，如果我们显式指定偏移量为 1，它将返回相同的结果。此函数考虑 TopRowNumber 和 BottomRowNumber，并跳过指定数量的行，如下图所示：
示例 2：查找下一到下一行结果/跳过前 2 行
如果我们想跳过 2 行，我们需要在偏移量中指定 2，如下所示：
Select MatchID ,MatchGroup ,MatchBetween ,ScheduleDate ,NextMatchBetween = Lead (MatchBetween,2) Over(Order by ScheduleDate) ,NextMatchDate = Lead (ScheduleDate,2) Over(Order by ScheduleDate) From MatchTable
我们跳过了两行，如上图所示。
示例 3：指定 0 或空格作为偏移量
如果我们在偏移量中给出 0，表示我们不跳过任何行。
Select MatchID ,MatchGroup ,MatchBetween ,ScheduleDate ,NextMatchBetween = Lead (MatchBetween,0) Over(Order by ScheduleDate) ,NextMatchDate = Lead (ScheduleDate,0) Over(Order by ScheduleDate) From MatchTable /* MatchID MatchGroup MatchBetween ScheduleDate NextMatchBetween NextMatchDate 1 Group-A India VS Australia 2011-08-14 India VS Australia 2011-08-14 2 Group-A India VS Pakistan 2011-08-15 India VS Pakistan 2011-08-15 3 Group-A India VS Newzealand 2011-08-16 India VS Newzealand 2011-08-16 4 Group-A Australia VS Pakistan 2011-08-17 Australia VS Pakistan 2011-08-17 5 Group-A Australia VS Newzealand 2011-08-18 Australia VS Newzealand 2011-08-18 6 Group-A Newzealand VS Pakistan 2011-08-19 Newzealand VS Pakistan 2011-08-19 7 Group-B USA VS WestIndies 2011-08-20 USA VS WestIndies 2011-08-20 8 Group-B USA VS Ireland 2011-08-21 USA VS Ireland 2011-08-21 9 Group-B USA VS Bangaladesh 2011-08-22 USA VS Bangaladesh 2011-08-22 10 Group-B WestIndies VS Ireland 2011-08-23 WestIndies VS Ireland 2011-08-23 11 Group-B WestIndies VS Bangaladesh2011-08-24 WestIndies VS Bangaladesh2011-08-24 12 Group-B Ireland VS Bangaladesh 2011-08-25 Ireland VS Bangaladesh 2011-08-25 */
如果指定空格，也可以实现相同的结果，如下所示：
Select MatchID ,MatchGroup ,MatchBetween ,ScheduleDate ,NextMatchBetween = Lead (MatchBetween,'') Over(Order by ScheduleDate) ,NextMatchDate = Lead (ScheduleDate,'') Over(Order by ScheduleDate) From MatchTable
示例 4：指定负偏移量
Lead 函数不能接受负偏移量。
Select MatchID ,MatchGroup ,MatchBetween ,ScheduleDate ,NextMatchBetween = Lead (MatchBetween,-1) Over(Order by ScheduleDate) ,NextMatchDate = Lead (ScheduleDate,-1) Over(Order by ScheduleDate) From MatchTable /* Msg 8730, Level 16, State 1, Line 43 Offset parameter for Lag and Lead functions cannot be a negative value. */
示例 5：指定 null 值作为偏移量会导致 null
将 null 值作为偏移量会导致 null。
Select MatchID ,MatchGroup ,MatchBetween ,ScheduleDate ,NextMatchBetween = Lead (MatchBetween,null) Over(Order by ScheduleDate) ,NextMatchDate = Lead (ScheduleDate,null) Over(Order by ScheduleDate) From MatchTable
示例 6：处理带小数部分的 Lead 函数
我们甚至可以在 Lead 函数的偏移量子句中使用小数。让我们看下面的例子：
Select MatchID ,MatchGroup ,MatchBetween ,ScheduleDate ,NextMatchBetween = Lead (MatchBetween,12/10) Over(Order by ScheduleDate) ,NextMatchDate = Lead (ScheduleDate,12/10) Over(Order by ScheduleDate) From MatchTable
示例 7：使用日历表进行 Lead 演示
它可以与通用表表达式结合使用，如下所示：
;With DtCalender As( Select Dt = Convert(Date,'8/1/2011') Union All Select DATEADD(dd,1,Dt) From DtCalender Where Dt < Convert(Date,'8/31/2011') ) Select MatchID ,MatchBetween ,Lead(c.Dt,1) Over(Order by c.Dt) As [Lead Example] From MatchTable m join DtCalender c on c.Dt = m.ScheduleDate option (maxrecursion 0) /* Result MatchID MatchBetween Lead Example 1 India VS Australia 2011-08-15 2 India VS Pakistan 2011-08-16 3 India VS Newzealand 2011-08-17 4 Australia VS Pakistan 2011-08-18 5 Australia VS Newzealand 2011-08-19 6 Newzealand VS Pakistan 2011-08-20 7 USA VS WestIndies 2011-08-21 8 USA VS Ireland 2011-08-22 9 USA VS Bangaladesh 2011-08-23 10 WestIndies VS Ireland 2011-08-24 11 WestIndies VS Bangaladesh2011-08-25 12 Ireland VS Bangaladesh NULL */
查询很简单。我们即时创建了一个日历表（2011 年 8 月），然后将其仅用于与 match date 列匹配的日期。
示例 8：处理标量表达式或函数
我们甚至可以在表达式字段中使用标量表达式或函数，如下所示：
Select MatchID ,MatchGroup ,MatchBetween ,ScheduleDate ,NextMatchBetween = Lead ((Select Top 1 MatchGroup from MatchTable),1) Over(Order by ScheduleDate) ,NextMatchDate = Lead (ScheduleDate,1) Over(Order by ScheduleDate) From MatchTable /* Result MatchID MatchGroup MatchBetween ScheduleDate NextMatchBetween NextMatchDate 1 Group-A India VS Australia 2011-08-14 Group-A 2011-08-15 2 Group-A India VS Pakistan 2011-08-15 Group-A 2011-08-16 3 Group-A India VS Newzealand 2011-08-16 Group-A 2011-08-17 4 Group-A Australia VS Pakistan 2011-08-17 Group-A 2011-08-18 5 Group-A Australia VS Newzealand 2011-08-18 Group-A 2011-08-19 6 Group-A Newzealand VS Pakistan 2011-08-19 Group-A 2011-08-20 7 Group-B USA VS WestIndies 2011-08-20 Group-A 2011-08-21 8 Group-B USA VS Ireland 2011-08-21 Group-A 2011-08-22 9 Group-B USA VS Bangaladesh 2011-08-22 Group-A 2011-08-23 10 Group-B WestIndies VS Ireland 2011-08-23 Group-A 2011-08-24 11 Group-B WestIndies VS Bangaladesh2011-08-24 Group-A 2011-08-25 12 Group-B Ireland VS Bangaladesh 2011-08-25 NULL NULL */
示例 9：使用 Lead 函数的默认值
这也是一个可选参数。如果未指定，则将隐式 NULL 值视为默认值，这在前面的示例中已见过。但是，我们可以指定自己的默认值。
Select MatchID ,MatchGroup ,MatchBetween ,ScheduleDate ,NextMatchBetween = Lead (MatchBetween,1,'No more Match') Over(Order by ScheduleDate) ,NextMatchDate = Lead (ScheduleDate,1) Over(Order by ScheduleDate) From MatchTable /* MatchID MatchGroup MatchBetween ScheduleDate NextMatchBetween NextMatchDate 1 Group-A India VS Australia 2011-08-14 India VS Pakistan 2011-08-15 2 Group-A India VS Pakistan 2011-08-15 India VS Newzealand 2011-08-16 3 Group-A India VS Newzealand 2011-08-16 Australia VS Pakistan 2011-08-17 4 Group-A Australia VS Pakistan 2011-08-17 Australia VS Newzealand 2011-08-18 5 Group-A Australia VS Newzealand 2011-08-18 Newzealand VS Pakistan 2011-08-19 6 Group-A Newzealand VS Pakistan 2011-08-19 USA VS WestIndies 2011-08-20 7 Group-B USA VS WestIndies 2011-08-20 USA VS Ireland 2011-08-21 8 Group-B USA VS Ireland 2011-08-21 USA VS Bangaladesh 2011-08-22 9 Group-B USA VS Bangaladesh 2011-08-22 WestIndies VS Ireland 2011-08-23 10 Group-B WestIndies VS Ireland 2011-08-23 WestIndies VS Bangaladesh 2011-08-24 11 Group-B WestIndies VS Bangaladesh2011-08-24 Ireland VS Bangaladesh 2011-08-25 12 Group-B Ireland VS Bangaladesh 2011-08-25 No more Match<- Default value NULL */
默认日期已标记以便于理解。如果我们查看图形执行计划，可以发现它调用了 Convert_Implicit 方法来转换指定的默认值。
然而，默认值的（数据）类型应该能够被引擎根据标量表达式的数据类型转换。因此，如果指定的任何值导致 Convert_Implicit 方法转换失败，则将导致异常。
Select MatchID ,MatchGroup ,MatchBetween ,ScheduleDate ,NextMatchBetween = Lead (MatchBetween,1,'No more Match') Over(Order by ScheduleDate) ,NextMatchDate = Lead (ScheduleDate,1,'No more Match') Over(Order by ScheduleDate) From MatchTable /* Msg 241, Level 16, State 1, Line 45 Conversion failed when converting date and/or time from character string. */
此语句失败是因为默认值应该是日期数据类型，但我们传递的是 varchar 类型。为了避免这种情况，我们可以像下面这样将 try parse 与 IIF 结合使用：
Declare @defaultValue VARCHAR(20) = 'No more Match' Select MatchID ,MatchGroup ,MatchBetween ,ScheduleDate ,NextMatchBetween = Lead (MatchBetween,1,@defaultValue) Over(Order by ScheduleDate) ,NextMatchDate = Lead ( ScheduleDate ,1 ,IIF( Try_Parse(@defaultValue as Date) Is Not Null ,@defaultValue ,Null ) ) Over(Order by ScheduleDate) From MatchTable
示例 10：与 Partition by 子句一起使用
它可以与 partition by 子句顺利配合，如下所示：
Select MatchID ,MatchGroup ,MatchBetween ,ScheduleDate ,NextMatchBetween = Lead (MatchBetween,1,'No more Match in this group') Over(Partition By MatchGroup Order by ScheduleDate) ,NextMatchDate = Lead (ScheduleDate,1) Over(Partition By MatchGroup Order by ScheduleDate) From MatchTable
示例 11：使用 partition by 子句限制行的 Lead 演示
我们甚至可以像下面这样限制行：
Select MatchID ,MatchGroup ,MatchBetween ,ScheduleDate ,NextMatchBetween = Lead (MatchBetween,1,'No more Match in this group') Over(Partition By MatchGroup Order by ScheduleDate) ,NextMatchDate = Lead (ScheduleDate,1) Over(Partition By MatchGroup Order by ScheduleDate) From MatchTable Where MatchGroup = 'Group-A' /* Result MatchID MatchGroup MatchBetween ScheduleDate NextMatchBetween NextMatchDate 1 Group-A India VS Australia 2011-08-14 India VS Pakistan 2011-08-15 2 Group-A India VS Pakistan 2011-08-15 India VS Newzealand 2011-08-16 3 Group-A India VS Newzealand 2011-08-16 Australia VS Pakistan 2011-08-17 4 Group-A Australia VS Pakistan 2011-08-17 Australia VS Newzealand 2011-08-18 5 Group-A Australia VS Newzealand 2011-08-18 Newzealand VS Pakistan 2011-08-19 6 Group-A Newzealand VS Pakistan 2011-08-19 No more Match in this group NULL */
我相信我们已经看到了 Lead 函数的许多示例，并且已经理解了它的行为。所以我们可以继续下一个函数 Lag，它是 Lead 的对应函数。
目的：此函数返回表中上一行的结果集。语法：Lag(expression [,offset [,default] ] ) over( [ Partition_By_clause] order by clause)
其中，
Expression => 表列或内置函数，但不能是分析函数。
Offset => 这是可选的，表示表中相对于当前行的物理偏移量。如果未指定，则默认值为 1，并且不能接受负数。
Default => 这也是可选的。如果未指定，则当偏移量超出表边界时，将返回默认的 null。
Partition_By_clause => 分区查询结果集。这也是可选的。
Order By Clause => 指示分区内数据的排序方式。
注意：Lag 和 Lead 是一枚硬币的两面，因此我们为 Lead 看到的任何示例都适用于 Lag。因此，在这里重复相同的场景是不明智的。尽管如此，我们将看到一些 Lag 的示例。
示例 1：查找上一场比赛的日期和上一场比赛的对阵双方
Select MatchID ,MatchGroup ,MatchBetween ,ScheduleDate ,PrevMatchBetween = Lag (MatchBetween) Over(Order by ScheduleDate) ,PrevMatchDate = Lag (ScheduleDate) Over(Order by ScheduleDate) From MatchTable --OR Select MatchID ,MatchGroup ,MatchBetween ,ScheduleDate ,PrevMatchBetween = Lag (MatchBetween,1) Over(Order by ScheduleDate) ,PrevMatchDate = Lag (ScheduleDate,1) Over(Order by ScheduleDate) From MatchTable /* Result MatchID MatchGroup MatchBetween ScheduleDate PrevMatchBetween PrevMatchDate 1 Group-A India VS Australia 2011-08-14 NULL NULL 2 Group-A India VS Pakistan 2011-08-15 India VS Australia 2011-08-14 3 Group-A India VS Newzealand 2011-08-16 India VS Pakistan 2011-08-15 4 Group-A Australia VS Pakistan 2011-08-17 India VS Newzealand 2011-08-16 5 Group-A Australia VS Newzealand 2011-08-18 Australia VS Pakistan 2011-08-17 6 Group-A Newzealand VS Pakistan 2011-08-19 Australia VS Newzealand 2011-08-18 7 Group-B USA VS WestIndies 2011-08-20 Newzealand VS Pakistan 2011-08-19 8 Group-B USA VS Ireland 2011-08-21 USA VS WestIndies 2011-08-20 9 Group-B USA VS Bangaladesh 2011-08-22 USA VS Ireland 2011-08-21 10 Group-B WestIndies VS Ireland 2011-08-23 USA VS Bangaladesh 2011-08-22 11 Group-B WestIndies VS Bangaladesh2011-08-24 WestIndies VS Ireland 2011-08-23 12 Group-B Ireland VS Bangaladesh 2011-08-25 WestIndies VS Bangaladesh2011-08-24 */
现在让我们尝试理解 Lag 函数的行为。我们没有在此查询中指定任何偏移量，因此默认值 1 已被假定。因此，它结束于从底部数起的 N-1 行。但是，如果我们显式指定偏移量为 1，它将返回相同的结果。此函数考虑 TopRowNumber 和 BottomRowNumber（与 Lead 一样），并跳过从底部数的行数，如下图所示：
如前所述，Lead 的所有后续示例都适用于 Lag。因此，此处不重复场景，我们继续下一个函数。
目的：它返回排序值集中的第一个值。
语法：First_Value(expression) over( [ Partition_By_clause] order by clause [rows_range_clause])
其中，
Expression => 表列或内置函数，但不能是分析函数。
Rows_range_clause => 它有助于进一步限制分析函数的效果。
如前所述，First_Value 函数返回排序值集中的第一个值。如果第一个值是 null，则函数返回 null。让我们来看一下这个函数的实际应用。
示例 1：简单的 First_Value 演示，用于获取第一场和最后一场比赛
假设我们需要找出第一场和最后一场比赛。我们可以像下面这样实现：
Select MatchBetween ,ScheduleDate ,First_Value(MatchBetween) Over(Order By ScheduleDate Desc) As [Last Match] ,First_Value(MatchBetween) Over(Order By ScheduleDate ) As [First Match] From MatchTable /* Result MatchBetween ScheduleDate Last Match First Match India VS Australia 2011-08-14 Ireland VS Bangaladesh India VS Australia India VS Pakistan 2011-08-15 Ireland VS Bangaladesh India VS Australia India VS Newzealand 2011-08-16 Ireland VS Bangaladesh India VS Australia Australia VS Pakistan 2011-08-17 Ireland VS Bangaladesh India VS Australia Australia VS Newzealand 2011-08-18 Ireland VS Bangaladesh India VS Australia Newzealand VS Pakistan 2011-08-19 Ireland VS Bangaladesh India VS Australia USA VS WestIndies 2011-08-20 Ireland VS Bangaladesh India VS Australia USA VS Ireland 2011-08-21 Ireland VS Bangaladesh India VS Australia USA VS Bangaladesh 2011-08-22 Ireland VS Bangaladesh India VS Australia WestIndies VS Ireland 2011-08-23 Ireland VS Bangaladesh India VS Australia WestIndies VS Bangaladesh2011-08-24 Ireland VS Bangaladesh India VS Australia Ireland VS Bangaladesh 2011-08-25 Ireland VS Bangaladesh India VS Australia */
嗯，通过查看结果集，任何人都可以争辩说，这可以通过以下方式实现：
a) Row_Number 方法，如下所示：
;With Cte AS( Select RowID = ROW_NUMBER() Over(Order by (select 1)) ,MatchBetween ,ScheduleDate From MatchTable ) Select MatchBetween ,ScheduleDate , [Last Match] = (Select MatchBetween from Cte where RowID = 12) , [First Match] = (Select MatchBetween from Cte where RowID = 1) from Cte
注意：我故意使用了 Row_Number() 函数而不是 [MatchID] 列，只是为了以 Row_Number() 的方式完成工作。
b) Max/Min 方法，如下所示：
Select MatchBetween ,ScheduleDate , [Last Match] = (Select MatchBetween from MatchTable where ScheduleDate = (Select MAX(ScheduleDate) from MatchTable)) , [First Match] = (Select MatchBetween from MatchTable where ScheduleDate = (Select MIN(ScheduleDate) from MatchTable)) from MatchTable
而且这个论点是有效的。但是等等，First_Value() 函数的唯一目的是这个吗？让我们看更多例子。
示例 2：使用 Partition By 子句的简单 First_Value 演示
Select MatchBetween ,ScheduleDate ,MatchGroup ,First_Value(MatchBetween) Over(Partition By MatchGroup Order By ScheduleDate Desc) As LastMatch ,First_Value(MatchBetween) Over(Partition By MatchGroup Order By ScheduleDate) As FirstMatch From MatchTable /* Result MatchBetween ScheduleDate MatchGroup LastMatch FirstMatch India VS Australia 2011-08-14 Group-A Newzealand VS Pakistan India VS Australia India VS Pakistan 2011-08-15 Group-A Newzealand VS Pakistan India VS Australia India VS Newzealand 2011-08-16 Group-A Newzealand VS Pakistan India VS Australia Australia VS Pakistan 2011-08-17 Group-A Newzealand VS Pakistan India VS Australia Australia VS Newzealand 2011-08-18 Group-A Newzealand VS Pakistan India VS Australia Newzealand VS Pakistan 2011-08-19 Group-A Newzealand VS Pakistan India VS Australia USA VS WestIndies 2011-08-20 Group-B Ireland VS Bangaladesh USA VS WestIndies USA VS Ireland 2011-08-21 Group-B Ireland VS Bangaladesh USA VS WestIndies USA VS Bangaladesh 2011-08-22 Group-B Ireland VS Bangaladesh USA VS WestIndies WestIndies VS Ireland 2011-08-23 Group-B Ireland VS Bangaladesh USA VS WestIndies WestIndies VS Bangaladesh2011-08-24 Group-B Ireland VS Bangaladesh USA VS WestIndies Ireland VS Bangaladesh 2011-08-25 Group-B Ireland VS Bangaladesh USA VS WestIndies */
可以看出，在这种情况下，函数是基于创建的分区工作的。
示例 3：First_Value 与新的窗口子句
Select MatchBetween ,ScheduleDate ,First_Value(MatchBetween) Over(Order By ScheduleDate Range Unbounded Preceding) As Rng_UPrec ,First_Value(MatchBetween) Over(Order By ScheduleDate Rows Unbounded Preceding) As Row_UPrec ,First_Value(MatchBetween) Over(Order By ScheduleDate Range Current Row) As Rng_Curr ,First_Value(MatchBetween) Over(Order By ScheduleDate Rows Current Row) As Row_Curr ,First_Value(MatchBetween) Over(Order By ScheduleDate Range Between Unbounded Preceding And Current Row) As Rng_UPrec_Curr ,First_Value(MatchBetween) Over(Order By ScheduleDate Rows Between Unbounded Preceding And Current Row) As Row_UPrec_Curr ,First_Value(MatchBetween) Over(Order By ScheduleDate Range Between Unbounded Preceding And Unbounded Following) As Rng_UPrec_UFoll ,First_Value(MatchBetween) Over(Order By ScheduleDate Rows Between Unbounded Preceding And Unbounded Following) As Row_UPrec_UFoll ,First_Value(MatchBetween) Over(Order By ScheduleDate Range Between Current Row And Unbounded Following) As Rng_Curr_UFoll ,First_Value(MatchBetween) Over(Order By ScheduleDate Rows Between Current Row And Unbounded Following) As Row_Curr_UFoll From MatchTable
目的：返回排序值集中的最后一个值。
语法： Last_Value(expression) over( [ Partition_By_clause] order by clause [rows_range_clause])
其中，
Expression => 表列或内置函数，但不能是分析函数。
Rows_range_clause => 它有助于进一步限制分析函数的效果。
如前所述，Last_Value 函数返回排序值集中的最后一个值。如果最后一个值是 null，则函数返回 null。
让我们来看一下这个函数的实际应用。
示例 1：简单的 Last_Value 演示
假设我们需要找出每个组中最后一场比赛。我们可以像下面这样实现：
Select MatchBetween ,MatchGroup ,Last_Value(MatchBetween) Over(Partition By MatchGroup Order By MatchGroup) As [Last Match In Group] From MatchTable /* Result MatchBetween MatchGroup Last Match In Group ------------ --------- --------------------- India VS Australia Group-A Newzealand VS Pakistan India VS Pakistan Group-A Newzealand VS Pakistan India VS Newzealand Group-A Newzealand VS Pakistan Australia VS Pakistan Group-A Newzealand VS Pakistan Australia VS Newzealand Group-A Newzealand VS Pakistan Newzealand VS Pakistan Group-A Newzealand VS Pakistan USA VS WestIndies Group-B Ireland VS Bangaladesh USA VS Ireland Group-B Ireland VS Bangaladesh USA VS Bangaladesh Group-B Ireland VS Bangaladesh WestIndies VS Ireland Group-B Ireland VS Bangaladesh WestIndies VS BangaladeshGroup-B Ireland VS Bangaladesh Ireland VS Bangaladesh Group-B Ireland VS Bangaladesh */
示例 2：Last_Value 与新的窗口子句
Select MatchBetween ,ScheduleDate ,Last_Value(MatchBetween) Over(Order By ScheduleDate Range Unbounded Preceding) As Rng_UPrec ,Last_Value(MatchBetween) Over(Order By ScheduleDate Rows Unbounded Preceding) As Row_UPrec ,Last_Value(MatchBetween) Over(Order By ScheduleDate Range Current Row) As Rng_Curr ,Last_Value(MatchBetween) Over(Order By ScheduleDate Rows Current Row) As Row_Curr ,Last_Value(MatchBetween) Over(Order By ScheduleDate Range Between Unbounded Preceding And Current Row) As Rng_UPrec_Curr ,Last_Value(MatchBetween) Over(Order By ScheduleDate Rows Between Unbounded Preceding And Current Row) As Row_UPrec_Curr ,Last_Value(MatchBetween) Over(Order By ScheduleDate Range Between Unbounded Preceding And Unbounded Following) As Rng_UPrec_UFoll ,Last_Value(MatchBetween) Over(Order By ScheduleDate Rows Between Unbounded Preceding And Unbounded Following) As Row_UPrec_UFoll ,Last_Value(MatchBetween) Over(Order By ScheduleDate Range Between Current Row And Unbounded Following) As Rng_Curr_UFoll ,Last_Value(MatchBetween) Over(Order By ScheduleDate Rows Between Current Row And Unbounded Following) As Row_Curr_UFoll From MatchTable
目的：在一组行中，它计算行的相对排名。
语法： Percent_Rank () Over ( [partition_by_clause] order_by_clause)
公式
Percent_Rank = (R -1)/(N-1)
其中，
R => 需要查找排名的行
N => 总行数或记录数。
例如，我们有一个包含 10 个元素的集合。我们需要找到第 7 行的相对排名。因此，将值应用于上述公式，我们得到：
R = 7, N = 10
所以，Percent_Rank = (7-1)/(10-1) = 0.6666666666666667
此函数返回的值范围是 0 到 1（包括）。返回的数据类型始终是正数。
示例
Select PlayerID ,PlayerName ,BelongsTo ,FeePerMatch ,Percent_Rank() Over(Partition By BelongsTo Order By BelongsTo,FeePerMatch) As PercentRank From PlayerTable /* Result PlayerID PlayerName BelongsTo FeePerMatch PercentRank 1014 S. Boon Australia 890.00 0 1013 MS. Kaki Australia 1234.00 0.25 1015 DC. Shane Australia 4444.00 0.5 1012 Z. Samaki Australia 8888.00 0.75 1011 A.Namaki Australia 9999.00 1 1001 A. Won India 1000.00 0 1003 B. Dhanman India 3600.00 0.25 1005 A. Mirza India 3600.00 0.25 1002 A. Cricket India 4000.00 0.75 1004 C. Barsat India 5000.00 1 1019 S. Winter Singapore 128.00 0 1020 J.August Singapore 890.00 0.25 1017 Z. Biswas Singapore 2222.00 0.5 1016 S. Noami Singapore 5678.00 0.75 1018 K. Dolly Singapore 9999.00 1 1007 Z. Hamsa US 400.00 0 1009 S. Summer US 1230.00 0.25 1006 M. Karol US 2000.00 0.5 1010 J.June US 4988.00 0.75 1008 K. Loly US 8000.00 1 */
现在让我们了解一下 [Fee Per Match] 列的相对排名是如何生成的。
以 Player ID 为 1015 的行为为例，即从第 3 行开始。
对于 [Belongs to] 字段“Australia”，总共有 5 行。
所以，我们有 R = 3 且 N = 5。
将值代入我们的公式得出：
Percent_Rank = (3 -1)/(5-1) = 2/4 = 0.5
目的：在一组行中，它计算值在值组中的累积分布。
语法： Cume_Dist() Over ([partition_by_clause] order_by_clause)
公式
Cumulative_Distribution = (R)/(N)
其中，
R => 需要查找排名的行
N => 总行数或记录数。
例如，我们有一个包含 10 个元素的集合。我们需要找到第 7 行的累积分布。因此，将值应用于上述公式，我们得到：
R = 7, N = 10
所以，Cumulative_Distribution = (7)/(10) = 0.7
此函数返回的值范围是 0 到 1（包括）。返回的数据类型始终是正数。
示例
Select PlayerID ,PlayerName ,BelongsTo ,FeePerMatch ,Cume_Dist() Over(Partition By BelongsTo Order By BelongsTo,FeePerMatch) As CumilativeDistribution From PlayerTable /* Result (Partial) PlayerID PlayerName BelongsTo FeePerMatch CumilativeDistribution 1014 S. Boon Australia 890.00 0.2 1013 MS. Kaki Australia 1234.00 0.4 1015 DC. Shane Australia 4444.00 0.6 1012 Z. Samaki Australia 8888.00 0.8 1011 A.Namaki Australia 9999.00 1 1001 A. Won India 1000.00 0.2 1003 B. Dhanman India 3600.00 0.6 1005 A. Mirza India 3600.00 0.6 1002 A. Cricket India 4000.00 0.8 1004 C. Barsat India 5000.00 1 */
现在让我们了解一下 [Fee Per Match] 列的相对排名是如何生成的。
以 Player ID 为 1015 的行为为例，即从第 3 行开始。
对于 [Belongs to] 字段“Australia”，总共有 5 行。
所以，我们有 R = 3 且 N = 5。
将值代入我们的公式得出：
Cumulative_Distribution = (3)/(5) = 0.6
目的：一个逆分布函数，它接受一个百分位数和一个排序规范，并返回一个在该百分位数处的值，该值相对于排序规范。它接受任何数值数据类型或任何可以隐式转换为数值数据类型的非数值数据类型作为其参数，并返回与参数的数值数据类型相同的数据类型。
语法：Percentile_Cont ( numeric_literal ) within group(Order by sort_expression [asc|desc]) OVER ([partition_by_clause])
示例
SELECT [MatchID]
,[MatchGroup]
,[MatchBetween]
,[ScheduleDate]
,Percentile_Cont =PERCENTILE_Cont(.6) WITHIN GROUP (ORDER BY MatchID)OVER(PARTITION BY [MatchGroup])
FROM MatchTable
/*Result
MatchID MatchGroup MatchBetween ScheduleDate Percentile_Cont
1 Group-A India VS Australia 2011-08-14 4
2 Group-A India VS Pakistan 2011-08-15 4
3 Group-A India VS Newzealand 2011-08-16 4
4 Group-A Australia VS Pakistan 2011-08-17 4
5 Group-A Australia VS Newzealand 2011-08-18 4
6 Group-A Newzealand VS Pakistan 2011-08-19 4
7 Group-B USA VS WestIndies 2011-08-20 10
8 Group-B USA VS Ireland 2011-08-21 10
9 Group-B USA VS Bangaladesh 2011-08-22 10
10 Group-B WestIndies VS Ireland 2011-08-23 10
11 Group-B WestIndies VS Bangaladesh2011-08-24 10
12 Group-B Ireland VS Bangaladesh 2011-08-25 10
*/
目的：一个逆分布函数，它接受一个百分位数和一个排序规范，并返回集合中的一个元素。它接受任何数值数据类型或任何可以隐式转换为数值数据类型的非数值数据类型作为其参数，并返回与参数的数值数据类型相同的数据类型。
语法：Percentile_Disc ( numeric_literal ) within group(Order by sort_expression [asc|desc]) OVER ([partition_by_clause])
公式
Percentile_Disc = Cumulative_Distribution * N
其中，
N => 总行数或记录数。
例如，我们有一个包含 10 个元素的集合。我们需要找到第 7 行的 Percentile_Disc。让我们先计算 Cumulative_distribution，如下所示：
所以，Cumulative_Distribution = (7)/(10) = 0.7
所以，Percentile_Disc = Cumulative_Distribution * N = 0.7 * 10 = 7
此函数返回的值范围是 0 到 1（包括）。返回的数据类型始终是正数。
示例
SELECT [MatchID]
,[MatchGroup]
,[MatchBetween]
,[ScheduleDate]
,PercentileDisc =PERCENTILE_DISC(.6) WITHIN GROUP (ORDER BY MatchID)OVER(PARTITION BY [MatchGroup])
,Cume_Dist() Over(Partition By [MatchGroup] Order By MatchID) As CumilativeDistribution
,PercentileDiscByFormula = Cume_Dist() Over(Partition By [MatchGroup] Order By MatchID) * (6)
FROM MatchTable
/* Result
MatchID MatchGroup MatchBetween ScheduleDate PercentileDisc CumilativeDistribution PercentileDiscByFormula
1 Group-A India VS Australia 2011-08-14 4 0.166666666666667 1
2 Group-A India VS Pakistan 2011-08-15 4 0.333333333333333 2
3 Group-A India VS Newzealand 2011-08-16 4 0.5 3
4 Group-A Australia VS Pakistan 2011-08-17 4 0.666666666666667 4
5 Group-A Australia VS Newzealand 2011-08-18 4 0.833333333333333 5
6 Group-A Newzealand VS Pakistan 2011-08-19 4 1 6
7 Group-B USA VS WestIndies 2011-08-20 10 0.166666666666667 1
8 Group-B USA VS Ireland 2011-08-21 10 0.333333333333333 2
9 Group-B USA VS Bangaladesh 2011-08-22 10 0.5 3
10 Group-B WestIndies VS Ireland 2011-08-23 10 0.666666666666667 4
11 Group-B WestIndies VS Bangaladesh2011-08-24 10 0.833333333333333 5
12 Group-B Ireland VS Bangaladesh 2011-08-25 10 1 6
*/
结论
在本文中，我们从 T-SQL 角度审视了 Denali CTP 3 提供的新功能和增强功能。这是一个很好的函数列表，可以帮助开发人员轻松地编写 T-SQL 程序。在下一篇文章中，我们将探讨列存储索引，这也是一个新概念。
我希望本文能帮助我们了解并以多种方式应用新的 T-SQL 功能。请分享您对此的宝贵反馈，并继续关注下一篇文章。