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