經(jīng)常寫SQL腳本的朋友，對查詢的多樣化要求可能會經(jīng)常頭疼。數(shù)據(jù)庫SQL的語法是固定的、但應用要求卻是千差萬別的。依靠我們所掌握的知識，大部分的查詢需求我們還是有辦法的解決的，但總有那么一些要求，把我們搞的非常被動。
今天我們就談一個會讓我們頭疼的問題：如何對查詢的結果進行排名。沒錯，是排名，不是排序，跟Order By有點關系，但可以認為是另一個問題。排名函數(shù)不但可以實現(xiàn)排序，還能夠生成排序的排名序列。
演示數(shù)據(jù)準備
今天我們就以SQL Server為例，結合四大排名函數(shù)，詳細講解一下如何使用四大排名函數(shù)實現(xiàn)查詢結果的排名。MySQL也有類似的排名函數(shù)，使用方法與SQL Server大同小異。
為了演示需要，我們需要先創(chuàng)建一個表變量作為銷售表，記錄不同區(qū)域、城市、年月的銷售金額，然后再對表中的數(shù)據(jù)進行排名處理。演示數(shù)據(jù)創(chuàng)建腳本如下：
declare @sale table(
FName nvarchar(50),
FDistrict nvarchar(50),
FYear smallint,
FMonth smallint,
FAmount decimal(28,10)
);
insert into @sale
values
('張三','北京',2019,4,20000),
('張三','深圳',2019,4,40000),
('李四','北京',2019,4,30000),
('李四','深圳',2019,4,40000),
('王二','北京',2019,4,70000),
('王二','深圳',2019,4,60000),
('馬六','北京',2019,4,80000),
('馬六','深圳',2019,4,70000);
運行結果請參考下圖：
有了演示數(shù)據(jù)，我們就把四個排名函數(shù)的應用和區(qū)別挨個理一理。
ROW_NUMBER，簡單方便又強大
row_number是最常用、最簡單的排名函數(shù)，其語法格式如下：
row_number() over(order by field列表 asc|desc)
語法格式看上去有點怪怪的，前半截row_number()是排名函數(shù)，緊接其后的over子句則是指定排序的規(guī)則。其它三個排名函數(shù)也有over子句，用途和語法也是一樣的。整個函數(shù)作為一個整體，其返回值就是排名序列號，序列號從1開始依次累加。
比如我們要按照銷售金額進行排名，語法格式如下：
select ROW_NUMBER() over(order by FAmount desc) as FRank,* from @sale;
運行效果參考下圖：
通過上圖的查詢結果可以看出，F(xiàn)Rank字段就是返回的排名字段。根據(jù)over子句，可以定義任何自己需要的排名規(guī)則。
如果您認為row_number函數(shù)只是能實現(xiàn)如此排名，那您就想的太簡單了。row_number函數(shù)還有一個很重要的擴展用途，可以實現(xiàn)查詢分頁，我們舉一個例子來說明如何使用row_number實現(xiàn)查詢分頁。
為了使演示效果更明顯，我們使用公用表表達式返回排名序列值和數(shù)據(jù)，根據(jù)要求的每頁行數(shù)和第幾頁，通過where子句限定排名序列的起點和終點。
declare @pagesize int =4;--每頁記錄數(shù) declare @pagenum int =1;--第幾頁 with cte as ( select row_number() over(order by FAmount desc) as FRank,* from @sale ) select * from cte where FRank between @pagesize*(@pagenum-1)+1 and @pagenum*@pagesize;
運行效果如下圖所示：
這種寫法基本是通用的，您可以比葫蘆畫瓢稍加改造，就可以用在您的分頁腳本中。
如果在查詢中使用order by子句，要注意order by子句最好與排名中over子句的order by一致，如果不一致，可能導致返回排名序列是不連續(xù)的，但這并不影響數(shù)據(jù)頁的正確性。
還有一種可以實現(xiàn)分頁寫法，使用offset進行分頁，這里我就不再贅述了。如果您想要更進一步了解分頁語法，可以參考我之前寫過的文章《如何在SQLServer查詢中實現(xiàn)高效分頁》。
RANK，相同值共用排名值，跳著排
RANK函數(shù)與ROW_NUMBER函數(shù)的語法和效果類似，最大的區(qū)別在于，如果碰到相同的字段值，會使用相同的排名序列值，后續(xù)的序列值則會跳過共用序列值。
其語法格式如下：
rank() over(order by field列表 asc|desc)
下面我們就實戰(zhàn)一下：
select RANK() over(order by FAmount desc) as FRank,* from @sale;
運行效果如下圖所示：
我們從上圖可以很明顯看出來，第2、3條記錄因值相同，使用了相同的序列值“2”，到了第四條，排名序列值直接使用了“4”而跳過了“3”。
DENSE_RANK，相同值共用排名值，接著排
DENSE_RANK函數(shù)與RANK函數(shù)的語法和效果類似，區(qū)別就像函數(shù)名稱中的DENSE（緊密的）含義一樣，如果碰到相同的字段值，雖然都會使用相同的排名序列值，但序列值是連續(xù)的。
其語法格式如下：
dense_rank() over(order by field列表 asc|desc)
下面我們就實戰(zhàn)一下：
select DENSE_RANK() over(order by FAmount desc) as FRank,* from @sale;
運行效果如下圖所示：
可以看出RANK和DENSE_RANK確實有共同點，但也有區(qū)別。RANK排名值如果碰到相同字段值，則會使用相同的排名值，后續(xù)會跳過斷開使用新的排名值；DENSE_RANK碰到相同字段值同樣使用相同的排名值，但是接著排下來，不會斷開。
NTILE，根據(jù)約定分組
NTILE函數(shù)的語法和用途上面三個明顯不同，其功能主要用來實現(xiàn)對記錄進行分組。根據(jù)NTILE約定分組的組數(shù)，將查詢結果按照over子句的排序標準進行分組，分組組號按照序列排列，同一個組使用同一個組號。
其語法格式如下：
ntile(組數(shù)) over(order by field列表 asc|desc)
如果指定的組數(shù)剛好實現(xiàn)均分，則每一組的記錄數(shù)是相同的，比如我們將8條記錄分成四組，則會有四個組，組號分別是1、2、3、4，每一組都會有兩個記錄，舉例如下：
select NTILE(4) over(order by FAmount desc) as FRank,* from @sale;
運行效果如下：
如果分成五組，明顯無法均分，那如何分呢？
當然是有一種邏輯存在的。這種邏輯可以這樣理解，從第一組開始，使用總記錄數(shù)除以組數(shù)，獲取等于或最大于相除結果的最小整數(shù)，作為第一組的記錄條數(shù)；剩余的記錄條數(shù)按照相同算法依次類推。通俗來講，NTITLE在優(yōu)先配足靠前的分組的記錄數(shù)的前提下，盡量進行均分。
下面我們我實戰(zhàn)將以上記錄分成五組的執(zhí)行情況：
select NTILE(5) over(order by FAmount desc) as FRank,* from @sale;
運行效果如下：
從上圖可以看出，記錄條數(shù)是8條，分成5組，我們用8除以5，最接近且大于等于的整數(shù)是2，第一組占用了兩條記錄，這時候還剩余6條記錄分4組；依次類推，6除以4，最接近且大于等于的整數(shù)是2，第二組還是2條記錄；此時剩余4條記錄要分3組，4除以3，最接近且大于等于的整數(shù)還是2；再往下，剩余兩條分兩組，這時候就剛好均分了。
需要明確的是，NTILE也可用來分頁，但因分組邏輯的原因，效果就不如ROW_NUMBER理想了。
通過上述分析，我們對排名函數(shù)就有了充分的認識，您大可根據(jù)需要使用。希望對您有所幫助！
