?
提到排名函數我們首先可能想到的是order by，這個是排序，不是排名，排名需要在前面加個名次序號的，order by是沒有這個功能的。還可能會想到identity(1,1)，它也給了一個序號，但是不能保證給出的序號是連續升序的。除非能夠保證所有的Insert語句都能夠正確成功地完成，并且沒有刪除操作，實際的使用中大多數的表都不能保證這樣。
好在SQL Server中提供了一些排名函數來輔助實現這些功能。排名函數按照需要的順序對數據進行排名，并提供一個值對數據。下面來了解一下這些排序函數功能。
?
ROW_NUMBER
?
ROW_NUMBER函數允許以上升，連續的順序給每一行數據一個序號，注意ROW_NUMBER()后面一定要跟著over子句。來看語句：
1
use
AdventureWorks
2
select
3
ROW_NUMBER()
over
(
order
by
LastName)
as
RowNum,
4
FirstName
+
'
'
+
LastName
as
FullName
5
from
HumanResources.vEmployee
6
where
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. [數] 分割；分隔；區分
1
select
2
ROW_NUMBER()
over
(PARTITION
by
substring
(LastName,
1
,
1
)
order
by
LastName)
as
RowNum,
3
FirstName
+
'
'
+
LastName
as
FullName
4
from
HumanResources.vEmployee
5
where
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
? 結果和上面大致相同，可是少了前面的名次序號。于是我又對她進行了修改，代碼如下：
1
select
2
ROW_NUMBER()
over
(
order
by
substring
(LastName,
1
,
1
),LastName)
as
RowNum,
3
FirstName
+
'
'
+
LastName
as
FullName
4
from
HumanResources.vEmployee
5
where
JobTitle
=
'
Production Technician - WC60
'
?
圖4
排序沒有錯誤，是我們想要的分組排序，但是前面的名次沒有分組區分，和圖1沒有什么差別。可見圖3和圖4的做法完全是多余，純屬臆造，其實只要order by LastName都能得到正確的排序，只有partition by才是正解。通過上面的例子也可以對排序，排名這二者之間的區別有一個認識，他們雖然有相似之處，但是排名始終會產生一個名次序號，排序只要得到正確的順序就好。
?
RANK
?
還是拿馬拉松比賽來說事，如果有同時撞線的情況發生應該怎么計名次呢？例如A第一個撞線，B和C同時第二個撞線，D第三個撞線，如果我們想把D的名次計為第4名應該怎么處理呢？就是說不計順序名次，只計人數。這時就可以使用RANK函數了。
[注] rank n. 等級；隊列；排；軍銜vt. 排列；把…分等vi. 列隊；列為
在order by子句中定義的列上，如果返回一行數據與另一行具有相同的值，rank函數將給這些行賦予相同的排名數值。在排名的過程中，保持一個內部計數值，當值有所改變時，排名序號將有一個跳躍。
來看下面的語句：
1
select
2
ROW_NUMBER()
over
(
order
by
Department)
as
RowNum,
3
RANK()
over
(
order
by
Department)
as
Ranking,
4
FirstName
+
'
'
+
LastName
as
FullName,
5
Department
6
from
HumanResources.vEmployeeDepartment
7
order
by
RowNum
rank() 函數右面也要跟上一個 over 子句。為了看到效果我們以 Department 作為排序字段，可以看到 RowNum 作為升序連續排名， Ranking 作為計同排名，當 Department 的值相同時， Ranking 中的值保持不變，當 Ranking 中的值發生變化時， Ranking 列中的值將跳躍到正確的排名數值。來看結果：
?
? 圖5
? 從這個結果中我們可以說這次馬拉松賽跑的排名是：Tengiz Kharatishvili，Zainal Arifin，Sean Chai，Karen Berge，Chris Norred并列第1，Michael Sullivan，Sharon Salavaria，Roberto Tamburello，Gail Erickson，Jossef Goldberg并列第6，如此等等。
?
?
DENSE_RANK
?
在上面的例子中，A第一個撞線，B和C同時第二個撞線，D第三個撞線，如果我們想把B和C的名次計位第2名，D的名次計為第3名應該怎么處理呢？就是說考慮并列名次。這里使用DENSE_RANK函數，來看下面的代碼。 ?
?
1
select
2
ROW_NUMBER()
over
(
order
by
Department)
as
RowNum,
3
DENSE_RANK()
over
(
order
by
Department)
as
Ranking,
4
FirstName
+
'
'
+
LastName
as
FullName,
5
Department
6
from
HumanResources.vEmployeeDepartment
7
order
by
RowNum
?結果如下：
?
? 圖6
?
按照這個結果，我們可以說這次馬拉松賽跑的排名是： Tengiz Kharatishvili ， Zainal Arifin ， Sean Chai ， Karen Berge ， Chris Norred 并列第 1 ， Michael Sullivan ， Sharon Salavaria ， Roberto Tamburello ， Gail Erickson ， Jossef Goldberg ， Terri Duffy 并列第 2 ，等等如此。
?
?
在開始這個之前，先來一段小插曲。梭羅是鉛筆的發明者，不過他沒有申請專利。據說他天賦異稟，在父親的鉛筆廠里面打包鉛筆的時候，從一堆鉛筆里面抓取一把，每次都能精確地抓到一打 12 支。他在森林中目測兩顆樹之間的距離，和護林員用卷尺測量的結果相差無幾。現在如果我們想從一張表中抓取多比數據，每一筆都是相同的數目，并且標明第幾組該怎么辦呢？NTILE函數提供了這個功能，他能。來看代碼：
1
select
2
NTILE(
30
)
over
(
order
by
Department)
as
NTiles,
3
FirstName
+
'
'
+
LastName
as
FullName,
4
Department
5
from
HumanResources.vEmployeeDepartment
現在我們要抓取30個組的數據，并保證盡可能的保證每組數目相同。結果如下，
?
?圖7
? 這個視圖中共290條數據，290/30=9.7約等于10，所以每組10條數據，如圖每一條數據都有一個組號。這個結果要比索羅精確。
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
