SQL点滴20—T-SQL中的排名函数.docx

SQL点滴20—T-SQL中的排名函数?提到排名函数我们首先可能想到的是order by，这个是排序，不是排名，排名需要在前面加个名次序号的，order by是没有这个功能的。还可能会想到identity(1,1)，它也给了一个序号，但是不能保证给出的序号是连续升序的。除非能够保证所有的Insert语句都能够正确成功地完成，并且没有删除操作，实际的使用中大多数的表都不能保证这样。好在SQL Server中提供了一些排名函数来辅助实现这些功能。排名函数按照需要的顺序对数据进行排名，并提供一个值对数据。下面来了解一下这些排序函数功能。　　?ROW_NUMBER?ROW_NUMBER函数允许以上升，连续的顺序给每一行数据一个序号，注意ROW_NUMBER()后面一定要跟着over子句。来看语句：1use AdventureWorks2select3ROW_NUMBER() over(order by LastName) as RowNum,4FirstName+ + LastName as FullName5from HumanResources.vEmployee6where JobTitle=Production Technician - WC60这个语句对符合条件（JobTitle=Production Technician - WC60）的LastName按照升序排列，并加上排序的序号，这个序号是连续上升的。结果如下图1是部分结果。图1我们可以看到第一个人的LastName是Abercrombie，第二个人的LastName是Adams，以次类推。　　?PARTITION?如果我们想再细分一下，在一个小的分组范围内排序该怎么办呢？就是说让LastName以‘A’开头的作为第一组，在这个组内进行排序。以‘B’开头的作为第二组，在这个组内排序。以‘C’开头的作为第三组，在这个组内进行排序，如此等等。这里有一个很简单的实际例子，假如上面这些人都来参加同一场马拉松比赛，其中有男子组，女子组，男子残疾组，女子残疾组，60岁以上组等等。不管参赛者以第几位触线，名次都以他们的小组为基准。可以通过PARTITION BY选项来重新排序，给数据分区或者数据区域唯一的递增序号。来看下面的语句：[注] partition n. 划分，分开；[数] 分割；隔墙；隔离物；vt. [数] 分割；分隔；区分1select2ROW_NUMBER() over(PARTITION by substring(LastName,1,1) order by LastName) as RowNum,3FirstName+ + LastName as FullName4from HumanResources.vEmployee5where JobTitle=Production Technician - WC60这里模拟上面的情况，首先以Last Name的第一个字母作为分组，然后以第二个字母以后的字母来分组排序。来看看结果，如图2??图2?假设LastName以‘A’开头的是男子组，这个组有共有三个人，Kim Abercrombie是冠军，Jay Adams是亚军，Nancy Anderson是季军。假设LastName以‘B’开头的是女子组，这个组只有一个人Bryan Baker，无论如何她都是冠军。等等如此类推。这样一眼就能看出他们的小组名次了。这里你可能会觉得使用order by一样可以得到这样类似的结果。如下代码：1 select 2 FirstName+ + LastName as FullName3 from HumanResources.vEmployee4 where JobTitle=Production Technician - WC605 order by substring(LastName,1,1) ,LastName这个把order by放在最后，排序放在最后，首先按照LastName的首字母排序，再按照剩整个LastName排序，结果如下图3??图3?结果和上面大致相同，可是少了前面的名次序号。于是我又对她进行了修改，代码如下：1select 2ROW_NUMBER() over(order by substring(LastName,1,1),LastName) as RowNum,3FirstName+ + LastName as FullName4from HumanResources.vEmployee5where JobTitle=Production Technician - WC60这个和上面的类似，在排名函数中使用order by，并且是按照多个字段排序。来看看结果如图4??图4排序没有错误，是我们想要的分组排序，但是前