亚洲视频二区_亚洲欧洲日本天天堂在线观看_日韩一区二区在线观看_中文字幕不卡一区

公告：魔扣目錄網為廣大站長提供免費收錄網站服務，提交前請做好本站友鏈：【網站目錄：http://www.430618.com 】，免友鏈快審服務（50元/站），

網站：51998
待審：31
小程序：12
文章：1030137
會員：747

首頁 > 新聞資訊 > IT業界 >正文

善用SQL排名函數，讓您的查詢飛的更精彩演示數據準備ROW_NUMBER，簡單方便又強大RANK，相同值共用排名值，跳著排DENSE_RANK，相同值共用排名值，接著排NTILE，根據約定分組

發布時間：2023-07-03 17:56:41 作者：網友整理

經常寫SQL腳本的朋友，對查詢的多樣化要求可能會經常頭疼。數據庫SQL的語法是固定的、但應用要求卻是千差萬別的。依靠我們所掌握的知識，大部分的查詢需求我們還是有辦法的解決的，但總有那么一些要求，把我們搞的非常被動。

今天我們就談一個會讓我們頭疼的問題：如何對查詢的結果進行排名。沒錯，是排名，不是排序，跟Order By有點關系，但可以認為是另一個問題。排名函數不但可以實現排序，還能夠生成排序的排名序列。

演示數據準備

今天我們就以SQL Server為例，結合四大排名函數，詳細講解一下如何使用四大排名函數實現查詢結果的排名。MySQL也有類似的排名函數，使用方法與SQL Server大同小異。

為了演示需要，我們需要先創建一個表變量作為銷售表，記錄不同區域、城市、年月的銷售金額，然后再對表中的數據進行排名處理。演示數據創建腳本如下：

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);

運行結果請參考下圖：

有了演示數據，我們就把四個排名函數的應用和區別挨個理一理。

ROW_NUMBER，簡單方便又強大

row_number是最常用、最簡單的排名函數，其語法格式如下：

row_number() over(order by field列表 asc|desc)

語法格式看上去有點怪怪的，前半截row_number()是排名函數，緊接其后的over子句則是指定排序的規則。其它三個排名函數也有over子句，用途和語法也是一樣的。整個函數作為一個整體，其返回值就是排名序列號，序列號從1開始依次累加。

比如我們要按照銷售金額進行排名，語法格式如下：

select ROW_NUMBER() over(order by FAmount desc) as FRank,* from @sale;

運行效果參考下圖：

通過上圖的查詢結果可以看出，FRank字段就是返回的排名字段。根據over子句，可以定義任何自己需要的排名規則。

如果您認為row_number函數只是能實現如此排名，那您就想的太簡單了。row_number函數還有一個很重要的擴展用途，可以實現查詢分頁，我們舉一個例子來說明如何使用row_number實現查詢分頁。

為了使演示效果更明顯，我們使用公用表表達式返回排名序列值和數據，根據要求的每頁行數和第幾頁，通過where子句限定排名序列的起點和終點。

declare @pagesize int =4;--每頁記錄數
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一致，如果不一致，可能導致返回排名序列是不連續的，但這并不影響數據頁的正確性。

還有一種可以實現分頁寫法，使用offset進行分頁，這里我就不再贅述了。如果您想要更進一步了解分頁語法，可以參考我之前寫過的文章《如何在SQLServer查詢中實現高效分頁》。

RANK，相同值共用排名值，跳著排

RANK函數與ROW_NUMBER函數的語法和效果類似，最大的區別在于，如果碰到相同的字段值，會使用相同的排名序列值，后續的序列值則會跳過共用序列值。

其語法格式如下：

rank() over(order by field列表 asc|desc)

下面我們就實戰一下：

select RANK() over(order by FAmount desc) as FRank,* from @sale;

運行效果如下圖所示：

我們從上圖可以很明顯看出來，第2、3條記錄因值相同，使用了相同的序列值“2”，到了第四條，排名序列值直接使用了“4”而跳過了“3”。

DENSE_RANK，相同值共用排名值，接著排

DENSE_RANK函數與RANK函數的語法和效果類似，區別就像函數名稱中的DENSE（緊密的）含義一樣，如果碰到相同的字段值，雖然都會使用相同的排名序列值，但序列值是連續的。

其語法格式如下：

dense_rank() over(order by field列表 asc|desc)

下面我們就實戰一下：

select DENSE_RANK() over(order by FAmount desc) as FRank,* from @sale;

運行效果如下圖所示：

可以看出RANK和DENSE_RANK確實有共同點，但也有區別。RANK排名值如果碰到相同字段值，則會使用相同的排名值，后續會跳過斷開使用新的排名值；DENSE_RANK碰到相同字段值同樣使用相同的排名值，但是接著排下來，不會斷開。

NTILE，根據約定分組

NTILE函數的語法和用途上面三個明顯不同，其功能主要用來實現對記錄進行分組。根據NTILE約定分組的組數，將查詢結果按照over子句的排序標準進行分組，分組組號按照序列排列，同一個組使用同一個組號。

其語法格式如下：

ntile(組數) over(order by field列表 asc|desc)

如果指定的組數剛好實現均分，則每一組的記錄數是相同的，比如我們將8條記錄分成四組，則會有四個組，組號分別是1、2、3、4，每一組都會有兩個記錄，舉例如下：

select NTILE(4) over(order by FAmount desc) as FRank,* from @sale;

運行效果如下：

如果分成五組，明顯無法均分，那如何分呢？

當然是有一種邏輯存在的。這種邏輯可以這樣理解，從第一組開始，使用總記錄數除以組數，獲取等于或最大于相除結果的最小整數，作為第一組的記錄條數；剩余的記錄條數按照相同算法依次類推。通俗來講，NTITLE在優先配足靠前的分組的記錄數的前提下，盡量進行均分。

下面我們我實戰將以上記錄分成五組的執行情況：

select NTILE(5) over(order by FAmount desc) as FRank,* from @sale;

運行效果如下：

從上圖可以看出，記錄條數是8條，分成5組，我們用8除以5，最接近且大于等于的整數是2，第一組占用了兩條記錄，這時候還剩余6條記錄分4組；依次類推，6除以4，最接近且大于等于的整數是2，第二組還是2條記錄；此時剩余4條記錄要分3組，4除以3，最接近且大于等于的整數還是2；再往下，剩余兩條分兩組，這時候就剛好均分了。

需要明確的是，NTILE也可用來分頁，但因分組邏輯的原因，效果就不如ROW_NUMBER理想了。

通過上述分析，我們對排名函數就有了充分的認識，您大可根據需要使用。希望對您有所幫助！

分享到：

標簽：SQL