SQLSERVER 2005的ROW_NUMBER、RANK、DENSE_RANK的用法
更新時(shí)間：2010年07月20日 01:58:36 作者：
SQL Server 2005 引入幾個(gè)新的排序(排名)函數(shù),如ROW_NUMBER、RANK、DENSE_RANK等。這些新函數(shù)使您可以有效地分析數(shù)據(jù)以及向查詢(xún)的結(jié)果行提供排序值。
ROW_NUMBER()
說(shuō)明：返回結(jié)果集分區(qū)內(nèi)行的序列號(hào)，每個(gè)分區(qū)的第一行從 1 開(kāi)始。
語(yǔ)法：ROW_NUMBER () OVER ( [ <partition_by_clause> ] <order_by_clause> ) 。
備注：ORDER BY 子句可確定在特定分區(qū)中為行分配唯一 ROW_NUMBER 的順序。
參數(shù)：<partition_by_clause> ：將 FROM 子句生成的結(jié)果集劃入應(yīng)用了 ROW_NUMBER 函數(shù)的分區(qū)。
<order_by_clause>：確定將 ROW_NUMBER 值分配給分區(qū)中的行的順序。
返回類(lèi)型：bigint 。
示例：
/*以下示例將根據(jù)年初至今的銷(xiāo)售額，返回 AdventureWorks 中銷(xiāo)售人員的 ROW_NUMBER。*/
USE AdventureWorks
GO
SELECT c.FirstName, c.LastName, ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number', s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s JOIN Person.Contact c on s.SalesPersonID = c.ContactID
JOIN Person.Address a ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0
/*
FirstName LastName Row Number SalesYTD PostalCode
--------- ---------- ---------- ------------ ----------------------------
Shelley Dyck 1 5200475.2313 98027
Gail Erickson 2 5015682.3752 98055
Maciej Dusza 3 4557045.0459 98027
Linda Ecoffey 4 3857163.6332 98027
Mark Erickson 5 3827950.238 98055
Terry Eminhizer 6 3587378.4257 98055
Michael Emanuel 7 3189356.2465 98055
Jauna Elson 8 3018725.4858 98055
Carol Elliott 9 2811012.7151 98027
Janeth Esteves 10 2241204.0424 98055
Martha Espinoza 11 1931620.1835 98055
Carla Eldridge 12 1764938.9859 98027
Twanna Evans 13 1758385.926 98055
(13 行受影響)
*/
/*以下示例將返回行號(hào)為 50 到 60（含）的行，并以 OrderDate 排序。*/
USE AdventureWorks;
GO
WITH OrderedOrders AS
(SELECT SalesOrderID, OrderDate,
ROW_NUMBER() OVER (order by OrderDate)as RowNumber
FROM Sales.SalesOrderHeader )
SELECT *
FROM OrderedOrders
WHERE RowNumber between 50 and 60;
/*
SalesOrderID OrderDate RowNumber
------------ ----------------------- --------------------
43708 2001-07-03 00:00:00.000 50
43709 2001-07-03 00:00:00.000 51
43710 2001-07-03 00:00:00.000 52
43711 2001-07-04 00:00:00.000 53
43712 2001-07-04 00:00:00.000 54
43713 2001-07-05 00:00:00.000 55
43714 2001-07-05 00:00:00.000 56
43715 2001-07-05 00:00:00.000 57
43716 2001-07-05 00:00:00.000 58
43717 2001-07-05 00:00:00.000 59
43718 2001-07-06 00:00:00.000 60
(11 行受影響)
*/
--------------------------------------------------------------
RANK()
說(shuō)明：返回結(jié)果集的分區(qū)內(nèi)每行的排名。行的排名是相關(guān)行之前的排名數(shù)加一。
語(yǔ)法：RANK () OVER ( [ < partition_by_clause > ] < order_by_clause > )
備注：如果兩個(gè)或多個(gè)行與一個(gè)排名關(guān)聯(lián)，則每個(gè)關(guān)聯(lián)行將得到相同的排名。
例如，如果兩位頂尖銷(xiāo)售員具有同樣的 SalesYTD 值，他們將并列第一。
由于已有兩行排名在前，所以具有下一個(gè)最大 SalesYTD 的銷(xiāo)售人員將排名第三。
因此，RANK 函數(shù)并不總返回連續(xù)整數(shù)。
用于整個(gè)查詢(xún)的排序順序決定了行在結(jié)果集中的顯示順序。這也隱含了行在每個(gè)分區(qū)中的排名。
參數(shù)：< partition_by_clause > ：將 FROM 子句生成的結(jié)果集劃分為要應(yīng)用 RANK 函數(shù)的分區(qū)。
< order_by_clause >：確定將 RANK 值應(yīng)用于分區(qū)中的行時(shí)所基于的順序。
返回類(lèi)型：bigint
示例：
/*以下示例按照數(shù)量對(duì)清單中的產(chǎn)品進(jìn)行了排名。行集按 LocationID 分區(qū)，按 Quantity 排序。
USE AdventureWorks;
GO
SELECT i.ProductID, p.Name, i.LocationID, i.Quantity, RANK() OVER (PARTITION BY i.LocationID order by i.Quantity) as RANK
FROM Production.ProductInventory i JOIN Production.Product p
ON i.ProductID = p.ProductID
ORDER BY p.Name
GO
/*
ProductID Name LocationID Quantity RANK
----------- -------------------------------------------------- ---------- -------- --------------------
1 Adjustable Race 6 324 71
1 Adjustable Race 1 408 78
1 Adjustable Race 50 353 117
2 Bearing Ball 6 318 67
2 Bearing Ball 1 427 85
2 Bearing Ball 50 364 122
3 BB Ball Bearing 50 324 106
3 BB Ball Bearing 1 585 110
3 BB Ball Bearing 6 443 115
4 Headset Ball Bearings 1 512 99
4 Headset Ball Bearings 6 422 108
4 Headset Ball Bearings 50 388 140
316 Blade 10 388 33
......
(1069 行受影響)
*/
SQL code
說(shuō)明：返回結(jié)果集分區(qū)內(nèi)行的序列號(hào)，每個(gè)分區(qū)的第一行從 1 開(kāi)始。
語(yǔ)法：ROW_NUMBER () OVER ( [ <partition_by_clause> ] <order_by_clause> ) 。
備注：ORDER BY 子句可確定在特定分區(qū)中為行分配唯一 ROW_NUMBER 的順序。
參數(shù)：<partition_by_clause> ：將 FROM 子句生成的結(jié)果集劃入應(yīng)用了 ROW_NUMBER 函數(shù)的分區(qū)。
<order_by_clause>：確定將 ROW_NUMBER 值分配給分區(qū)中的行的順序。
返回類(lèi)型：bigint 。
示例：
/*以下示例將根據(jù)年初至今的銷(xiāo)售額，返回 AdventureWorks 中銷(xiāo)售人員的 ROW_NUMBER。*/
USE AdventureWorks
GO
SELECT c.FirstName, c.LastName, ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number', s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s JOIN Person.Contact c on s.SalesPersonID = c.ContactID
JOIN Person.Address a ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0
/*
FirstName LastName Row Number SalesYTD PostalCode
--------- ---------- ---------- ------------ ----------------------------
Shelley Dyck 1 5200475.2313 98027
Gail Erickson 2 5015682.3752 98055
Maciej Dusza 3 4557045.0459 98027
Linda Ecoffey 4 3857163.6332 98027
Mark Erickson 5 3827950.238 98055
Terry Eminhizer 6 3587378.4257 98055
Michael Emanuel 7 3189356.2465 98055
Jauna Elson 8 3018725.4858 98055
Carol Elliott 9 2811012.7151 98027
Janeth Esteves 10 2241204.0424 98055
Martha Espinoza 11 1931620.1835 98055
Carla Eldridge 12 1764938.9859 98027
Twanna Evans 13 1758385.926 98055
(13 行受影響)
*/
/*以下示例將返回行號(hào)為 50 到 60（含）的行，并以 OrderDate 排序。*/
USE AdventureWorks;
GO
WITH OrderedOrders AS
(SELECT SalesOrderID, OrderDate,
ROW_NUMBER() OVER (order by OrderDate)as RowNumber
FROM Sales.SalesOrderHeader )
SELECT *
FROM OrderedOrders
WHERE RowNumber between 50 and 60;
/*
SalesOrderID OrderDate RowNumber
------------ ----------------------- --------------------
43708 2001-07-03 00:00:00.000 50
43709 2001-07-03 00:00:00.000 51
43710 2001-07-03 00:00:00.000 52
43711 2001-07-04 00:00:00.000 53
43712 2001-07-04 00:00:00.000 54
43713 2001-07-05 00:00:00.000 55
43714 2001-07-05 00:00:00.000 56
43715 2001-07-05 00:00:00.000 57
43716 2001-07-05 00:00:00.000 58
43717 2001-07-05 00:00:00.000 59
43718 2001-07-06 00:00:00.000 60
(11 行受影響)
*/
--------------------------------------------------------------
RANK()
說(shuō)明：返回結(jié)果集的分區(qū)內(nèi)每行的排名。行的排名是相關(guān)行之前的排名數(shù)加一。
語(yǔ)法：RANK () OVER ( [ < partition_by_clause > ] < order_by_clause > )
備注：如果兩個(gè)或多個(gè)行與一個(gè)排名關(guān)聯(lián)，則每個(gè)關(guān)聯(lián)行將得到相同的排名。
例如，如果兩位頂尖銷(xiāo)售員具有同樣的 SalesYTD 值，他們將并列第一。
由于已有兩行排名在前，所以具有下一個(gè)最大 SalesYTD 的銷(xiāo)售人員將排名第三。
因此，RANK 函數(shù)并不總返回連續(xù)整數(shù)。
用于整個(gè)查詢(xún)的排序順序決定了行在結(jié)果集中的顯示順序。這也隱含了行在每個(gè)分區(qū)中的排名。
參數(shù)：< partition_by_clause > ：將 FROM 子句生成的結(jié)果集劃分為要應(yīng)用 RANK 函數(shù)的分區(qū)。
< order_by_clause >：確定將 RANK 值應(yīng)用于分區(qū)中的行時(shí)所基于的順序。
返回類(lèi)型：bigint
示例：
/*以下示例按照數(shù)量對(duì)清單中的產(chǎn)品進(jìn)行了排名。行集按 LocationID 分區(qū)，按 Quantity 排序。
USE AdventureWorks;
GO
SELECT i.ProductID, p.Name, i.LocationID, i.Quantity, RANK() OVER (PARTITION BY i.LocationID order by i.Quantity) as RANK
FROM Production.ProductInventory i JOIN Production.Product p
ON i.ProductID = p.ProductID
ORDER BY p.Name
GO
/*
ProductID Name LocationID Quantity RANK
----------- -------------------------------------------------- ---------- -------- --------------------
1 Adjustable Race 6 324 71
1 Adjustable Race 1 408 78
1 Adjustable Race 50 353 117
2 Bearing Ball 6 318 67
2 Bearing Ball 1 427 85
2 Bearing Ball 50 364 122
3 BB Ball Bearing 50 324 106
3 BB Ball Bearing 1 585 110
3 BB Ball Bearing 6 443 115
4 Headset Ball Bearings 1 512 99
4 Headset Ball Bearings 6 422 108
4 Headset Ball Bearings 50 388 140
316 Blade 10 388 33
......
(1069 行受影響)
*/
--接上.
-------------------------------------------------------------------------------------
DENSE_RANK()
說(shuō)明：返回結(jié)果集分區(qū)中行的排名，在排名中沒(méi)有任何間斷。行的排名等于所討論行之前的所有排名數(shù)加一。
語(yǔ)法：DENSE_RANK () OVER ( [ < partition_by_clause > ] < order_by_clause > )
備注：如果有兩個(gè)或多個(gè)行受同一個(gè)分區(qū)中排名的約束，則每個(gè)約束行將接收相同的排名。
例如，如果兩位頂尖銷(xiāo)售員具有相同的 SalesYTD 值，則他們將并列第一。
接下來(lái) SalesYTD 最高的銷(xiāo)售人員排名第二。該排名等于該行之前的所有行數(shù)加一。
因此，DENSE_RANK 函數(shù)返回的數(shù)字沒(méi)有間斷，并且始終具有連續(xù)的排名。
整個(gè)查詢(xún)所用的排序順序確定了各行在結(jié)果中的顯示順序。這說(shuō)明排名第一的行可以不是分區(qū)中的第一行。
參數(shù)：< partition_by_clause > ：將 FROM 子句所生成的結(jié)果集劃分為數(shù)個(gè)將應(yīng)用 DENSE_RANK 函數(shù)的分區(qū)。
< order_by_clause >：確定將 DENSE_RANK 值應(yīng)用于分區(qū)中各行的順序。
返回類(lèi)型：bigint
示例：
/*以下示例返回各位置上產(chǎn)品數(shù)量的 DENSE_RANK。 */
USE AdventureWorks;
GO
SELECT i.ProductID, p.Name, i.LocationID, i.Quantity, DENSE_RANK() OVER (PARTITION BY i.LocationID order by i.Quantity) as DENSE_RANK
FROM Production.ProductInventory i JOIN Production.Product p ON i.ProductID = p.ProductID
ORDER BY Name;
GO
/*
ProductID Name LocationID Quantity DENSE_RANK
----------- -------------------------------------------------- ---------- -------- --------------------
1 Adjustable Race 1 408 57
1 Adjustable Race 6 324 52
1 Adjustable Race 50 353 82
879 All-Purpose Bike Stand 7 144 34
712 AWC Logo Cap 7 288 38
3 BB Ball Bearing 50 324 74
3 BB Ball Bearing 6 443 81
3 BB Ball Bearing 1 585 82
*/
-------------------------------------------------------------------------------------------------------
將上面三個(gè)函數(shù)放在一起計(jì)算，更能明顯看出各個(gè)函數(shù)的功能。
CREATE TABLE rankorder(orderid INT,qty INT)
INSERT rankorder VALUES(30001,10)
INSERT rankorder VALUES(10001,10)
INSERT rankorder VALUES(10006,10)
INSERT rankorder VALUES(40005,10)
INSERT rankorder VALUES(30003,15)
INSERT rankorder VALUES(30004,20)
INSERT rankorder VALUES(20002,20)
INSERT rankorder VALUES(20001,20)
INSERT rankorder VALUES(10005,30)
INSERT rankorder VALUES(30007,30)
INSERT rankorder VALUES(40001,40)
INSERT rankorder VALUES(30007,30)
GO
--對(duì)一個(gè)列qty進(jìn)行的排序
SELECT orderid,qty,
ROW_NUMBER() OVER(ORDER BY qty) AS rownumber,
RANK() OVER(ORDER BY qty) AS rank,
DENSE_RANK() OVER(ORDER BY qty) AS denserank
FROM rankorder
ORDER BY qty
/*
orderid qty rownumber rank denserank
----------- ----------- -------------------- -------------------- --------------------
30001 10 1 1 1
10001 10 2 1 1
10006 10 3 1 1
40005 10 4 1 1
30003 15 5 5 2
30004 20 6 6 3
20002 20 7 6 3
20001 20 8 6 3
10005 30 9 9 4
30007 30 10 9 4
30007 30 11 9 4
40001 40 12 12 5
(12 行受影響)
*/
--對(duì)兩個(gè)列qty,orderid進(jìn)行的排序
SELECT orderid,qty,
ROW_NUMBER() OVER(ORDER BY qty,orderid) AS rownumber,
RANK() OVER(ORDER BY qty,orderid) AS rank,
DENSE_RANK() OVER(ORDER BY qty,orderid) AS denserank
FROM rankorder
ORDER BY qty,orderid
drop table rankorder
/*
orderid qty rownumber rank denserank
----------- ----------- -------------------- -------------------- --------------------
10001 10 1 1 1
10006 10 2 2 2
30001 10 3 3 3
40005 10 4 4 4
30003 15 5 5 5
20001 20 6 6 6
20002 20 7 7 7
30004 20 8 8 8
10005 30 9 9 9
30007 30 10 10 10
30007 30 11 10 10
40001 40 12 12 11
(12 行受影響)
*/
您可能感興趣的文章:
相關(guān)文章
SqlServer 2005/2008數(shù)據(jù)庫(kù)被標(biāo)記為“可疑”的解決辦法
當(dāng)數(shù)據(jù)庫(kù)發(fā)生這種操作故障時(shí)，可以按如下操作步驟可解決此方法，打開(kāi)數(shù)據(jù)庫(kù)里的Sql 查詢(xún)編輯器窗口，運(yùn)行以下的命令。2010-05-05
SQL 2005 ERROR:3145 解決辦法(備份集中的數(shù)據(jù)庫(kù)備份與現(xiàn)有的數(shù)據(jù)庫(kù)不同)
在圖形界面中，選擇備份文件，設(shè)置覆蓋原有數(shù)據(jù)庫(kù)，指定現(xiàn)數(shù)據(jù)庫(kù)文件所在的位置，選擇錯(cuò)誤回滾，點(diǎn)擊確定2013-01-01
SQL Server 數(shù)據(jù)庫(kù)清除日志的方法
SQLSERVER的數(shù)據(jù)庫(kù)日志占用很大的空間，下面提供三種方法用于清除無(wú)用的數(shù)據(jù)庫(kù)日志文件2012-07-07
簡(jiǎn)述SQL Server 2005數(shù)據(jù)庫(kù)鏡像相關(guān)知識(shí)
本文我們主要介紹了SQL Server 2005數(shù)據(jù)庫(kù)鏡像的相關(guān)知識(shí)，包括數(shù)據(jù)庫(kù)鏡像的優(yōu)點(diǎn)、數(shù)據(jù)庫(kù)鏡像的工作方式等,需要的朋友可以參考下2015-08-08
玩轉(zhuǎn)-SQL2005數(shù)據(jù)庫(kù)行列轉(zhuǎn)換
雖然開(kāi)發(fā)過(guò)程中沒(méi)用過(guò)行列轉(zhuǎn)換，但是聽(tīng)說(shuō)面試時(shí)常常會(huì)遇到這個(gè)問(wèn)題，以前在網(wǎng)上也看到過(guò)大神的例子，今天自己仔細(xì)的玩了下，希望和大家分享一下了2013-11-11
一臺(tái)服務(wù)器A配置SQL2005&SP3，含多個(gè)用戶(hù)數(shù)據(jù)庫(kù)，需要遷移到新的服務(wù)器B使用SQL2008R2&SP2,下面是遷移步驟，大家參考使用吧2014-01-01
SQLSERVER 2005中使用sql語(yǔ)句對(duì)xml文件和其數(shù)據(jù)的進(jìn)行操作(很全面)
由于數(shù)據(jù)庫(kù)對(duì)xml數(shù)據(jù)直接處理有很多優(yōu)勢(shì)，05也對(duì)這方面加強(qiáng)了功能。下面是一些實(shí)例代碼，大家可以參考下。2010-06-06
各位拿站的朋友。是不是感覺(jué)到現(xiàn)在mssql2000一部分都升級(jí)到mssql2005了，目前網(wǎng)絡(luò)上大多數(shù)log備份和差異備份WebShell都針對(duì)sql2000的數(shù)據(jù)庫(kù)而開(kāi)發(fā)的。那么碰到2005的數(shù)據(jù)庫(kù)怎么辦呢。不用急，請(qǐng)按照以下方式繼續(xù)可以拿到webshell。2008-06-06
SQL2005 高效分頁(yè)sql語(yǔ)句，需要的朋友可以參考下。2010-05-05
SQL2005CLR函數(shù)擴(kuò)展 - 關(guān)于山寨索引
對(duì)于文件索引lucene才是權(quán)威,這里只是自己實(shí)現(xiàn)了一個(gè)可以實(shí)現(xiàn)簡(jiǎn)單文件索引的半成品.所謂文件索引就是把sql字符串按字節(jié)分詞保存到磁盤(pán)文件目錄結(jié)構(gòu)中用來(lái)快速定位2013-06-06