SQL點滴20—T-SQL中的排名函數

提到排名函數我們首先可能想到的是order by，這個是排序，不是排名，排名需要在前面加個名次序號的，order by是沒有這個功能的。還可能會想到identity(1,1)，它也給了一個序號，但是不能保證給出的序號是連續升序的。除非能夠保證所有的Insert語句都能夠正確成功地完成，并且沒有刪除操作，實際的使用中大多數的表都不能保證這樣。

好在SQL Server中提供了一些排名函數來輔助實現這些功能。排名函數按照需要的順序對數據進行排名，并提供一個值對數據。下面來了解一下這些排序函數功能。

ROW_NUMBER

ROW_NUMBER函數允許以上升，連續的順序給每一行數據一個序號，注意ROW_NUMBER()后面一定要跟著over子句。來看語句：

1use AdventureWorks
2select
3ROW_NUMBER() over(order by LastName) as RowNum,
4FirstName+' '+ LastName as FullName
5from HumanResources.vEmployee
6where 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. [數] 分割；分隔；區分

1select
2ROW_NUMBER() over(PARTITION by substring(LastName,1,1) order by LastName) as RowNum,
3FirstName+' '+ LastName as FullName
4from HumanResources.vEmployee
5where 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 FullName
3 from HumanResources.vEmployee
4 where JobTitle='Production Technician - WC60'
5 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 FullName
4from HumanResources.vEmployee
5where JobTitle='Production Technician - WC60'