排名函數(shù) 詳解
SQL Server 2005 能夠在 T-SQL 代碼中對(duì)數(shù)據(jù)行進(jìn)行排名,。排名函數(shù)（ranking function）能對(duì)每一個(gè)數(shù)據(jù)行進(jìn)行排名,，從而提供一種以升序來組織輸出的方法,。可以給每一行一個(gè)唯一的序號(hào),，或者給每一組相似的行相同的序號(hào),。

排名函數(shù)有四種類型：
        ROW_NUMBER ：為查詢的結(jié)果行提供連續(xù)的整數(shù)值。
        RANK ：為行的集合提供升序的,、非唯一的排名序號(hào),，對(duì)于具有相同值的行，給予相同的序號(hào),。由于行的序號(hào)有相同的值,，因此，要跳過一些序號(hào),。
        DENSE_RANK ：與RANK類似,，不過,，無論有多少航具有相同的序號(hào)，DENSE_RANK放回的每一行的序號(hào)將比前一個(gè)序號(hào)增加1.
        NTILE ：把從查詢中獲取的行放置到具有相同的（或盡可能相同的）行數(shù)的,、特定序號(hào)的組中,，NTILE 返回行所屬的組的序號(hào)。

排名函數(shù)語法如下：
<function_name>() OVER ([PARTITION BY <partition_by_list>])
ORDER BY < order_by_list >

按照選項(xiàng)到來的順序給定這些選項(xiàng),，可以看到能夠怎樣在 SELECT 語句中放置選項(xiàng),，例如：
        function_name ：可以是 ROW_NUMBER / RANK / DENSE_RANK / NTILE 之一。
        OVER ：定義排名應(yīng)該如何對(duì)數(shù)據(jù)排序或劃分,。
        PARTITION BY ：定義列將使用什么數(shù)據(jù)作為劃分的基線,。
        ORDER BY ：定義數(shù)據(jù)排序的詳情。

以下示例顯示了用在同一查詢中的四個(gè)排名函數(shù),。有關(guān)每個(gè)函數(shù)的具體示例,，請(qǐng)參閱每個(gè)排名函數(shù)。
USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
    ,ROW_NUMBER() OVER (ORDER BY a.PostalCode) AS 'Row Number'
    ,RANK() OVER (ORDER BY a.PostalCode) AS 'Rank'
    ,DENSE_RANK() OVER (ORDER BY a.PostalCode) AS 'Dense Rank'
    ,NTILE(4) OVER (ORDER BY a.PosthalCode) AS 'Quartile'
    ,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s
    INNER JOIN Person.Contact c
        ON s.SalesPersonID = c.ContactID
    INNER JOIN Person.Address a
        ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL
    AND SalesYTD <> 0;

下面進(jìn)行詳細(xì)的說明,。

1. ROW_NUMBER
說明：返回結(jié)果集分區(qū)內(nèi)行的序列號(hào),，每個(gè)分區(qū)的第一行從 1 開始。
語法：
ROW_NUMBER ( ) OVER ( [ <partition_by_clause> ] <order_by_clause> )


例1：以下示例將根據(jù)年初至今的銷售額,，返回 AdventureWorks 中銷售人員的 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
    INNER JOIN Person.Contact c
        ON s.SalesPersonID = c.ContactID
    INNER JOIN Person.Address a
        ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL
    AND SalesYTD <> 0;

注意：
OVER 子句中的 ORDER BY 將對(duì) ROW_NUMBER 進(jìn)行排序。如果將 ORDER BY 子句添加到以非 'Row Number' the 的某列（或多列）作為排序依據(jù)的 SELECT 語句,，則結(jié)果集將以外部的 ORDER BY 為依據(jù)進(jìn)行排序,。

例2：以下示例將返回行號(hào)為 50 到 60 的行（包含這兩行），并按 OrderDate 進(jìn)行排序,。
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;
GO

例3：以下示例說明了如何使用 PARTITION BY 參數(shù),。
USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
    ,ROW_NUMBER() OVER
    (PARTITION BY PostalCode ORDER BY SalesYTD DESC) AS 'Row Number'
    ,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s
    INNER JOIN Person.Contact c
        ON s.SalesPersonID = c.ContactID
    INNER JOIN Person.Address a
        ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL
    AND SalesYTD <> 0;
GO



2. RANK
說明：返回結(jié)果集的分區(qū)內(nèi)每行的排名。行的排名是相關(guān)行之前的排名數(shù)加一,。

示例：以下示例按照數(shù)量對(duì)清單中的產(chǎn)品進(jìn)行了排名,。行集按 LocationID 分區(qū)，按 Quantity 排序,。注意,，OVER 子句中的 ORDER BY 對(duì) RANK 進(jìn)行排序，SELECT 語句的 ORDER BY 對(duì)結(jié)果集進(jìn)行排序,。
USE AdventureWorks;
GO
SELECT i.ProductID, p.Name, i.LocationID, i.Quantity
    ,RANK() OVER
    (PARTITION BY i.LocationID ORDER BY i.Quantity DESC) AS 'RANK'
FROM Production.ProductInventory i
    INNER JOIN Production.Product p
        ON i.ProductID = p.ProductID
ORDER BY p.Name;
GO



3. DENSE_RANK




4. NTILE
說明：將有序分區(qū)中的行分發(fā)到指定數(shù)目的組中,。各個(gè)組有編號(hào)，編號(hào)從一開始,。對(duì)于每一個(gè)行,，NTILE 將返回此行所屬的組的編號(hào)。

參數(shù)：
integer_expression ：
一個(gè)正整數(shù)常量表達(dá)式,，用于指定每個(gè)分區(qū)必須被劃分成的組數(shù),。integer_expression 的類型可以為 int 或 bigint,。

注意：
integer_expression 只能引用 PARTITION BY 子句中的列。integer_expression 不能引用在當(dāng)前 FROM 子句中列出的列,。

備注：
如果分區(qū)的行數(shù)不能被 integer_expression 整除,，則將導(dǎo)致一個(gè)成員有兩種大小不同的組。按照 OVER 子句指定的順序,，較大的組排在較小的組前面,。例如，如果總行數(shù)是 53,，組數(shù)是 5,，則前三個(gè)組每組包含 11 行，其余兩個(gè)組每組包含 10 行,。另一方面,，如果總行數(shù)可被組數(shù)整除，則行數(shù)將在組之間平均分布,。例如,，如果總行數(shù)為 50，有五個(gè)組,，則每組將包含 10 行,。

例1：將行分為組
以下示例將行分成四組。由于總行數(shù)不能被組數(shù)整除,，因此第一個(gè)組將包含四行,，其余每組包含三行。
USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
    ,NTILE(4) OVER(ORDER BY SalesYTD DESC) AS 'Quartile'
    ,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s
    INNER JOIN Person.Contact c
        ON s.SalesPersonID = c.ContactID
    INNER JOIN Person.Address a
        ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL
    AND SalesYTD <> 0;
GO

例2：使用 PARTITION BY 劃分結(jié)果集
以下示例將 PARTITION BY 參數(shù)添加到示例 A 中的代碼,。首先按 PostalCode 將行分區(qū),，然后在每個(gè) PostalCode 內(nèi)將行分成四個(gè)組。注意,，OVER 子句中的 ORDER BY 對(duì) NTILE 進(jìn)行排序,，SELECT 語句的 ORDER BY 對(duì)結(jié)果集進(jìn)行排序。
USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
    ,NTILE(4) OVER(PARTITION BY PostalCode ORDER BY SalesYTD DESC) AS 'Quartile'
    ,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s
    INNER JOIN Person.Contact c
        ON s.SalesPersonID = c.ContactID
    INNER JOIN Person.Address a
        ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL
    AND SalesYTD <> 0
ORDER BY LastName;
GO

[本帖最后由 zhangzongjun 于 2008-8-2 22:41 編輯]