排名函數(shù)是Sql Server2005新增的功能，下面簡(jiǎn)單介紹一下他們各自的用法和區(qū)別。我們新建一張Order表并添加一些初始數(shù)據(jù)方便我們查看效果。

?

CREATE?TABLE?[dbo].[Order](
????[ID]?[int]?IDENTITY(1,1)?NOT?NULL,
????[UserId]?[int]?NOT?NULL,
????[TotalPrice]?[int]?NOT?NULL,
????[SubTime]?[datetime]?NOT?NULL,
?CONSTRAINT?[PK_Order]?PRIMARY?KEY?CLUSTERED?
(
????[ID]?ASC
)WITH?(PAD_INDEX?=?OFF,?STATISTICS_NORECOMPUTE?=?OFF,?IGNORE_DUP_KEY?=?OFF,?ALLOW_ROW_LOCKS?=?ON,?ALLOW_PAGE_LOCKS?=?ON)?ON?[PRIMARY]
)?ON?[PRIMARY]

GO
SET?IDENTITY_INSERT?[dbo].[Order]?ON?

GO
INSERT?[dbo].[Order]?([ID],?[UserId],?[TotalPrice],?[SubTime])?VALUES?(1,?1,?100,?CAST(0x0000A419011D32AF?AS?DateTime))
GO
INSERT?[dbo].[Order]?([ID],?[UserId],?[TotalPrice],?[SubTime])?VALUES?(2,?2,?500,?CAST(0x0000A419011D40BA?AS?DateTime))
GO
INSERT?[dbo].[Order]?([ID],?[UserId],?[TotalPrice],?[SubTime])?VALUES?(3,?3,?300,?CAST(0x0000A419011D4641?AS?DateTime))
GO
INSERT?[dbo].[Order]?([ID],?[UserId],?[TotalPrice],?[SubTime])?VALUES?(4,?2,?1000,?CAST(0x0000A419011D4B72?AS?DateTime))
GO
INSERT?[dbo].[Order]?([ID],?[UserId],?[TotalPrice],?[SubTime])?VALUES?(5,?1,?520,?CAST(0x0000A419011D50F3?AS?DateTime))
GO
INSERT?[dbo].[Order]?([ID],?[UserId],?[TotalPrice],?[SubTime])?VALUES?(6,?2,?2000,?CAST(0x0000A419011E50C9?AS?DateTime))
GO
SET?IDENTITY_INSERT?[dbo].[Order]?OFF
GO
ALTER?TABLE?[dbo].[Order]?ADD??CONSTRAINT?[DF_Order_SubTime]??DEFAULT?(getdate())?FOR?[SubTime]
GO

?

?

　　附上表結(jié)構(gòu)和初始數(shù)據(jù)圖：

?

一、ROW_NUMBER

　　row_number的用途的非常廣泛，排序最好用他，一般可以用來(lái)實(shí)現(xiàn)web程序的分頁(yè)，他會(huì)為查詢出來(lái)的每一行記錄生成一個(gè)序號(hào)，依次排序且不會(huì)重復(fù)，注意使用row_number函數(shù)時(shí)必須要用over子句選擇對(duì)某一列進(jìn)行排序才能生成序號(hào)。row_number用法實(shí)例:

?

select?ROW_NUMBER()?OVER(order?by?[SubTime]?desc)?as?row_num,*?from?[Order]

?

　　查詢結(jié)果如下圖所示：

　　圖中的row_num列就是row_number函數(shù)生成的序號(hào)列，其基本原理是先使用over子句中的排序語(yǔ)句對(duì)記錄進(jìn)行排序，然后按照這個(gè)順序生成序號(hào)。over子句中的order by子句與SQL語(yǔ)句中的order by子句沒(méi)有任何關(guān)系，這兩處的order by 可以完全不同，如以下sql，over子句中根據(jù)SubTime降序排列，Sql語(yǔ)句中則按TotalPrice降序排列。

select?ROW_NUMBER()?OVER(order?by?[SubTime]?desc)?as?row_num,*?from?[Order]?order?by?[TotalPrice]?desc

　　查詢結(jié)果如下圖所示：

　　利用row_number可以實(shí)現(xiàn)web程序的分頁(yè)，我們來(lái)查詢指定范圍的表數(shù)據(jù)。例：根據(jù)訂單提交時(shí)間倒序排列獲取第三至第五條數(shù)據(jù)。

with?orderSection?as
(
????select?ROW_NUMBER()?OVER(order?by?[SubTime]?desc)?rownum,*?from?[Order]
)
select?*?from?[orderSection]?where?rownum?between?3?and?5?order?by?[SubTime]?desc

　　查詢結(jié)果如下圖所示：

　　注意：在使用row_number實(shí)現(xiàn)分頁(yè)時(shí)需要特別注意一點(diǎn)，over子句中的order by 要與Sql排序記錄中的order by 保持一致，否則得到的序號(hào)可能不是連續(xù)的。下面我們寫一個(gè)例子來(lái)證實(shí)這一點(diǎn)，將上面Sql語(yǔ)句中的排序字段由SubTime改為TotalPrice。另外提一下，對(duì)于帶有子查詢和CTE的查詢，子查詢和CTE查詢有序并不代表整個(gè)查詢有序，除非顯示指定了order by。

with?orderSection?as
(
????select?ROW_NUMBER()?OVER(order?by?[SubTime]?desc)?rownum,*?from?[Order]
)
select?*?from?[orderSection]?where?rownum?between?3?and?5?order?by?[TotalPrice]?desc

　　查詢結(jié)果如下圖所示：

二、RANK

　　rank函數(shù)用于返回結(jié)果集的分區(qū)內(nèi)每行的排名，?行的排名是相關(guān)行之前的排名數(shù)加一。簡(jiǎn)單來(lái)說(shuō)rank函數(shù)就是對(duì)查詢出來(lái)的記錄進(jìn)行排名，與row_number函數(shù)不同的是，rank函數(shù)考慮到了over子句中排序字段值相同的情況，如果使用rank函數(shù)來(lái)生成序號(hào)，over子句中排序字段值相同的序號(hào)是一樣的，后面字段值不相同的序號(hào)將跳過(guò)相同的排名號(hào)排下一個(gè)，也就是相關(guān)行之前的排名數(shù)加一，可以理解為根據(jù)當(dāng)前的記錄數(shù)生成序號(hào)，后面的記錄依此類推。可能我描述的比較蒼白，理解起來(lái)也比較吃力，我們直接上代碼，rank函數(shù)的使用方法與row_number函數(shù)完全相同。

select?RANK()?OVER(order?by?[UserId])?as?rank,*?from?[Order]

　　查詢結(jié)果如下圖所示：

　　由上圖可以看出，rank函數(shù)在進(jìn)行排名時(shí)，同一組的序號(hào)是一樣的，而后面的則是根據(jù)當(dāng)前的記錄數(shù)依次類推，圖中第一、二條記錄的用戶Id相同，所以他們的序號(hào)是一樣的，第三條記錄的序號(hào)則是3?！　?/p>

?

三、DENSE_RANK

　　dense_rank函數(shù)的功能與rank函數(shù)類似，dense_rank函數(shù)在生成序號(hào)時(shí)是連續(xù)的，而rank函數(shù)生成的序號(hào)有可能不連續(xù)。dense_rank函數(shù)出現(xiàn)相同排名時(shí)，將不跳過(guò)相同排名號(hào)，rank值緊接上一次的rank值。在各個(gè)分組內(nèi)，rank()是跳躍排序，有兩個(gè)第一名時(shí)接下來(lái)就是第四名，dense_rank()是連續(xù)排序，有兩個(gè)第一名時(shí)仍然跟著第二名。將上面的Sql語(yǔ)句改由dense_rank函數(shù)來(lái)實(shí)現(xiàn)。

select?DENSE_RANK()?OVER(order?by?[UserId])?as?den_rank,*?from?[Order]

　　查詢結(jié)果如下圖所示：

　　圖中第一、二條記錄的用戶Id相同，所以他們的序號(hào)是一樣的，第三條記錄的序號(hào)緊接上一個(gè)的序號(hào)，所以為2不為3，后面的依此類推。

四、NTILE

　　ntile函數(shù)可以對(duì)序號(hào)進(jìn)行分組處理，將有序分區(qū)中的行分發(fā)到指定數(shù)目的組中。?各個(gè)組有編號(hào)，編號(hào)從一開始。?對(duì)于每一個(gè)行，ntile 將返回此行所屬的組的編號(hào)。這就相當(dāng)于將查詢出來(lái)的記錄集放到指定長(zhǎng)度的數(shù)組中，每一個(gè)數(shù)組元素存放一定數(shù)量的記錄。ntile函數(shù)為每條記錄生成的序號(hào)就是這條記錄所有的數(shù)組元素的索引（從1開始）。也可以將每一個(gè)分配記錄的數(shù)組元素稱為“桶”。ntile函數(shù)有一個(gè)參數(shù)，用來(lái)指定桶數(shù)。下面的SQL語(yǔ)句使用ntile函數(shù)對(duì)Order表進(jìn)行了裝桶處理：

select?NTILE(4)?OVER(order?by?[SubTime]?desc)?as?ntile,*?from?[Order]

　　查詢結(jié)果如下圖所示：

　　Order表的總記錄數(shù)是6條，而上面的Sql語(yǔ)句ntile函數(shù)指定的組數(shù)是4，那么Sql Server2005是怎么來(lái)決定每一組應(yīng)該分多少條記錄呢？這里我們就需要了解ntile函數(shù)的分組依據(jù)（約定）。

　　ntile函數(shù)的分組依據(jù)（約定）：

　　1、每組的記錄數(shù)不能大于它上一組的記錄數(shù)，即編號(hào)小的桶放的記錄數(shù)不能小于編號(hào)大的桶。也就是說(shuō)，第1組中的記錄數(shù)只能大于等于第2組及以后各組中的記錄數(shù)。

　　2、所有組中的記錄數(shù)要么都相同，要么從某一個(gè)記錄較少的組（命名為X）開始后面所有組的記錄數(shù)都與該組（X組）的記錄數(shù)相同。也就是說(shuō)，如果有個(gè)組，前三組的記錄數(shù)都是9，而第四組的記錄數(shù)是8，那么第五組和第六組的記錄數(shù)也必須是8。

　　這里對(duì)約定2進(jìn)行詳細(xì)說(shuō)明一下，以便于更好的理解。

　　首先系統(tǒng)會(huì)去檢查能不能對(duì)所有滿足條件的記錄進(jìn)行平均分組，若能則直接平均分配就完成分組了；若不能，則會(huì)先分出一個(gè)組，這個(gè)組分多少條記錄呢？就是 （總記錄數(shù)/總組數(shù)）+1 條，之所以分配 （總記錄數(shù)/總組數(shù)）+1 條是因?yàn)楫?dāng)不能進(jìn)行平均分組時(shí)，總記錄數(shù)%總組數(shù)肯定是有余的，又因?yàn)榉纸M約定1，所以先分出去的組需要+1條。

　　分完之后系統(tǒng)會(huì)繼續(xù)去比較余下的記錄數(shù)和未分配的組數(shù)能不能進(jìn)行平均分配，若能，則平均分配余下的記錄；若不能，則再分出去一組，這個(gè)組的記錄數(shù)也是（總記錄數(shù)/總組數(shù)）+1條。

　　然后系統(tǒng)繼續(xù)去比較余下的記錄數(shù)和未分配的組數(shù)能不能進(jìn)行平均分配，若能，則平均分配余下的記錄；若還是不能，則再分配出去一組，繼續(xù)比較余下的......這樣一直進(jìn)行下去，直至分組完成。

　　舉個(gè)例子，將51條記錄分配成5組，51%5==1不能平均分配，則先分出去一組（51/5）+1=11條記錄，然后比較余下的 51-11=40 條記錄能否平均分配給未分配的4組，能平均分配，則剩下的4組，每組各40/4=10 條記錄，分配完成，分配結(jié)果為：11，10，10，10，10，曉菜鳥我開始就錯(cuò)誤的以為他會(huì)分配成 11，11，11，11，7。

　　根據(jù)上面的兩個(gè)約定，可以得出如下的算法：

?

//mod表示取余，div表示取整.
if(記錄總數(shù)?mod?桶數(shù)==0)
{
　　recordCount=記錄總數(shù)?div?桶數(shù);
　　//將每桶的記錄數(shù)都設(shè)為recordCount.
}
else
{
　　recordCount1=記錄總數(shù)?div?桶數(shù)+1;
　　int?n=1;//n表示桶中記錄數(shù)為recordCount1的最大桶數(shù).
　　m=recordCount1*n;
　　while(((記錄總數(shù)-m)　mod　(桶數(shù)-　n))　!=0)
　　{
　　　　n++;
　　　　m=recordCount1*n;
　　}
　　recordCount2=(記錄總數(shù)-m)?div　(桶數(shù)-n);
　　//將前n個(gè)桶的記錄數(shù)設(shè)為recordCount1.
　　//將n+1個(gè)至后面所有桶的記錄數(shù)設(shè)為recordCount2.
}

?

?NTILE()函數(shù)算法實(shí)現(xiàn)代碼

　　根據(jù)上面的算法，如果總記錄數(shù)為59，總組數(shù)為5，則 n=4 , recordCount1=12 , recordCount2=11，分組結(jié)果為 ：12，12，12，12，11。

　　如果總記錄數(shù)為53，總組數(shù)為5，則 n=3?, recordCount1=11 , recordCount2=10，分組結(jié)果為：11，11，11，10，10。

　　就拿上面的例子來(lái)說(shuō)，總記錄數(shù)為6，總組數(shù)為4，通過(guò)算法得到?n=2 , recordCount1=2 , recordCount2=1，分組結(jié)果為：2，2，1，1。

?

select?ntile,COUNT([ID])?recordCount?from?
(
????select?NTILE(4)?OVER(order?by?[SubTime]?desc)?as?ntile,*?from?[Order]
)?as?t
group?by?t.ntile

?

　　運(yùn)行Sql，分組結(jié)果如圖：

　　比對(duì)算法與Sql Server的分組結(jié)果是一致的，說(shuō)明算法沒(méi)錯(cuò)。:)

?

總結(jié)：

在使用排名函數(shù)的時(shí)候需要注意以下三點(diǎn)：

　　1、排名函數(shù)必須有?OVER?子句。

　　2、排名函數(shù)必須有包含 ORDER BY 的 OVER 子句。

　　3、分組內(nèi)從1開始排序。