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()
