sql2005排名函数
ROW_NUMBER
返回结果集分区内行的序列号，每个分区的第一行从 1 开始。
语法
[code]
ROW_NUMBER ( ) OVER ( [ <partition_by_clause> ] <order_by_clause> )
[/code]
ORDER BY 子句可确定在特定分区中为行分配唯一 ROW_NUMBER 的顺序。
<partition_by_clause>
将 FROM 子句生成的结果集划入应用了 ROW_NUMBER 函数的分区。
<order_by_clause>
确定将 ROW_NUMBER 值分配给分区中的行的顺序。
以下示例将根据年初至今的销售额，返回 AdventureWorks 中销售人员的 ROW_NUMBER。
[code]
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
[/code]
以下示例将返回行号为 50 到 60（含）的行，并以 OrderDate 排序。
[code]
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;
[/code]
RANK ( ) OVER ( [ < partition_by_clause > ] < order_by_clause > )
返回结果集的分区内每行的排名。行的排名是相关行之前的排名数加一。
如果两个或多个行与一个排名关联，则每个关联行将得到相同的排名。例如，如果两位顶尖销售员具有同样的 SalesYTD 值，他们将并列第一。由于已有两行排名在前，所以具有下一个最大 SalesYTD 的销售人员将排名第三。因此，RANK 函数并不总返回连续整数。
用于整个查询的排序顺序决定了行在结果集中的显示顺序。这也隐含了行在每个分区中的排名。
以下示例按照数量对清单中的产品进行了排名。行集按 LocationID 分区，按 Quantity 排序。
[code]
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
[/code]
DENSE_RANK ( ) OVER ( [ < partition_by_clause > ] < order_by_clause > )
返回结果集分区中行的排名，在排名中没有任何间断。行的排名等于所讨论行之前的所有排名数加一。
如果有两个或多个行受同一个分区中排名的约束，则每个约束行将接收相同的排名。例如，如果两位顶尖销售员具有相同的 SalesYTD 值，则他们将并列第一。接下来 SalesYTD 最高的销售人员排名第二。该排名等于该行之前的所有行数加一。因此，DENSE_RANK 函数返回的数字没有间断，并且始终具有连续的排名。
整个查询所用的排序顺序确定了各行在结果中的显示顺序。这说明排名第一的行可以不是分区中的第一行。
以下示例返回各位置上产品数量的 DENSE_RANK。
[code]
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
[/code]
NTILE (integer_expression) OVER ( [ <partition_by_clause> ] < order_by_clause > )
如果分区的行数不能被 expression 整除，则将导致一个成员有两种大小不同的组。按照 OVER 子句指定的顺序，较大的组排在较小的组前面。例如，如果总行数是 53，存储桶数是 5，则前三个存储桶每个包含 11 行，其余两个存储桶每个包含 10 行。另一方面，如果总行数可被存储桶数整除，则行数将在存储桶之间平均分布。例如，如果总行数为 50，有五个存储桶，则每个存储桶将包含 10 行。
参数
integer_expression
一个正正整数常量，用于指定每个分区必须被划分成的存储桶的数量。integer_expression 的类型可以为 bigint。
<partition_by_clause>
将 FROM 子句生成的结果集划分成 RANK 函数适用的分区。
< order_by_clause >
确定 NTILE 值分配到分区中各行的顺序
以下示例将在四个存储桶中分布行。由于总行数不能被存储桶数整除，因此第一个存储桶将包含四行，其余的存储桶每个包含三行。
[code]
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 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
[/code]
