SQLServer 2005 实现排序报表的利器 排序函数RANK()、DENSE_RANK()、NTILE()和ROW_NUMBER()

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;

结果: