SQLServer 2005 新增了排名函數 , 一共四個 , 其中 RANK() DENSE_RANK() NTILE() 這三個函數是制作帶有排名列報表的利器 ,ROW_NUMBER() 函數在分頁存儲過程中被廣泛使用

排名函數有一個明顯的共性 , 就是與 over() 子句配合使用

與排名函數一起使用的 over() 子句有兩個參數 partition 分組方式 order by 排序方式

RANK() 和 DENSE_RANK() 是一對兄弟 , 唯一的不同是

當使用 RANK() 進行排名時 由于 order by 參數 的值可能存在相等的情況 , 比如有兩個第一 , 那么下一個會被排名在第三 , 即使用 RANK() 排名時 , 得到數值并不是連續的 (1 、 1 、 3), 而是用 DENSE_RANK() 時 , 得到的永遠是連續的 (1 、 1 、 2).

語法：

RANK ( ) OVER ( [ < partition_by_clause > ] < order_by_clause > )

DENSE_RANK ( ) OVER ( [ < partition_by_clause > ] < order_by_clause > )

示例：

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 i . LocationID , RANK

GO

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 i . LocationID , DENSE_RANK ;

GO

結果：

NTILE() 我的理解是將結果集按片分割， SQLServer 幫助里寫的是存儲桶。

語法：

NTILE (integer_expression) OVER ( [ <partition_by_clause> ] < order_by_clause > )

與其他排名函數不同 NTILE() 多了一個參數 integer_expression, 這個參數是一個正整數 , 數據類型支持 bigint. integer_expression 參數決定了結果集最終分成幾片 , 或者是分布到幾個桶中 .

例如 integer_expression 參數為 5, 如果結果集的行數為 50, 正好能夠整除 , 那么這 50 行數據會被分成 5 片 , 每片 10 行數據 ; 如果不能夠整除 , 結果集是 53 行數據 , 那么第一、二、三片 , 每片 11 行數據 , 其余 10 行數據 .

示例 :

USE AdventureWorks ;

GO

SELECT c . FirstName , c . LastName , NTILE ( 5 ) OVER ( ORDER BY SalesYTD DESC ) AS 'Quartile' , 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 ;

GO

結果 :

ROW_NUMBER() 函數用來生成數據行在結果集中的序號

語法 :

ROW_NUMBER ( ) OVER ( [ <partition_by_clause> ] <order_by_clause> )

示例 :

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

-- 以下示例將返回行號為 50 到 60 （含）的行，并以 OrderDate 排序。

-- 這在分頁時非常實用

-- 關于 WITH 語法應查閱 CTE 相關內容

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 ;

結果 :

SQLServer 2005 實現排序報表的利器 排序函數RANK()、DENSE_RANK()、NTILE()和ROW_NUMBER()