SQL2005实现排序报表的利器排序函数RANK()、DENSE_RANK()、NTILE()和ROW

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;

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

USE AdventureWorks;

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;

结果：

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;

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;

结果:

ROW_NUMBER()函数用来生成数据行在结果集中的序号

语法:

ROW_NUMBER ( ) OVER ( [ <partition_by_clause> ] <order_by_clause> )

示例:

USE AdventureWorks

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;

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;

结果:

(责任编辑：admin)

SQL在存储过程中使用递归	SQL 2005如何批量修改表和存储过程的架构
SQL Server事务日志意外增大的处理方法	SQL Server的数据类型介绍
SQL Server复制功能巧妙选择避开Bug	简单三步让你轻松堵死SQL注入漏洞(图)
VC在SQL Server7中动态增加/删除用户	如何远程连接局域网内的sql server
有效地使用SQL事件探查器的提示和技巧	Sybase SQL Server数据库系统的一些概念区分
SQL Server 2005-实作CLR存储过程	监测你的SQL SERVER--让瓶颈暴露
解决SQL Server里sp_helptext输出格式错行问题	SQL Server连接中三个最常见错误原因分析
连接池与SQLServer2000 Analysis Services	VS和SQL Server对存储过程进行版本排序
SQL：如何重新存储备份到不同的服务器？	将整型数字转换为大写汉字

ASP.NET学习网|ASP.NET教程|ASP.NET MVC|ASP.NET 源码|VS2010教程|ASP.NET视频教程

热门标签

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

Tags：排序函数

ASP.NET学习网|ASP.NET教程|ASP.NET MVC|ASP.NET 源码|VS2010教程|ASP.NET视频教程

热门标签

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

Tags：排序函数

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