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

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

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

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

當(dāng)使用 RANK() 進(jìn)行排名時 由于 order by 參數(shù) 的值可能存在相等的情況 , 比如有兩個第一 , 那么下一個會被排名在第三 , 即使用 RANK() 排名時 , 得到數(shù)值并不是連續(xù)的 (1 、 1 、 3), 而是用 DENSE_RANK() 時 , 得到的永遠(yuǎn)是連續(xù)的 (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

結(jié)果：

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

語法：

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

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

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

示例 :

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

結(jié)果 :

ROW_NUMBER() 函數(shù)用來生成數(shù)據(jù)行在結(jié)果集中的序號

語法 :

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 排序。

-- 這在分頁時非常實用

-- 關(guān)于 WITH 語法應(yīng)查閱 CTE 相關(guān)內(nèi)容

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 ;

結(jié)果 :

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