SQL2005学习总结：排名函数

SQL Server 2005学习总结:排名函数

https://www.360docs.net/doc/8617751775.html, 2008年08月30日社区交流

Transact-SQL提供了4个排名函数: RANK(),DENSE_RANK(),ROW_NUMBER(),NTILE()

下面是对这4个函数的解释：

RANK()

返回结果集的分区内每行的排名。行的排名是相关行之前的排名数加一。

如果两个或多个行与一个排名关联，则每个关联行将得到相同的排名。

例如，如果两位销售员具有相同的SalesYTD值，则他们将并列第一。由于已有两行排名在前，所以具有下一个最大SalesYTD的销售人员将排名第三。

因此，RANK 函数并不总返回连续整数。

DENSE_RANK()

返回结果集分区中行的排名，在排名中没有任何间断。行的排名等于所讨论行之前的所有排名数加一。

如果有两个或多个行受同一个分区中排名的约束，则每个约束行将接收相同的排名。

例如，如果两位顶尖销售员具有相同的SalesYTD 值，则他们将并列第一。接下来SalesYTD 最高的销售人员排名第二。该排名等于该行之前的所有行数加一。

因此，DENSE_RANK 函数返回的数字没有间断，并且始终具有连续的排名。

ROW_NUMBER()

回结果集分区内行的序列号，每个分区的第一行从1 开始。

ORDER BY 子句可确定在特定分区中为行分配唯一ROW_NUMBER 的顺序。

NTILE()

将有序分区中的行分发到指定数目的组中。各个组有编号，编号从一开始。对于每一个行，NTILE 将返回此行所属的组的编号。

如果分区的行数不能被integer_expression 整除，则将导致一个成员有两种大小不同的组。按照OVER 子句指定的顺序，较大的组排在较小的组前面。

例如，如果总行数是53，组数是5，则前三个组每组包含11 行，其余两个组每组包含10 行。

另一方面，如果总行数可被组数整除，则行数将在组之间平均分布。

例如，如果总行数为50，有五个组，则每组将包含10 行。

--演示例题，建一个table

--插入数据

--查询出各类排名

--结果

--ROW_NUMBER()是按qty由小到大逐一排名，不并列，排名连续--RANK()是按qty由小到大逐一排名，并列，排名不连续

--DENSE_RANK()是按qty由小到大逐一排名，并列，排名连续

--NTILE()是按qty由小到大分成3组逐一排名，并列，排名连续

SQL 2005实现排名非常方便，但是用SQL 2000实现排名就比较麻烦，下面是SQL 2000的实现代码:

--RANK在sql 2000中的实现

--ROW_NUMBER在sql 2000中的实现

--利用临时表和IDENTITY(函数)

排名函数是与窗口函数OVER()配合一起使用的。

如果借助OVER子句的参数PARTITION BY,就可以将结果集分为多个分区。排名函数将在每个分区内进行排名.

--例题

--说明：

--a列是在全部记录上进行的排名

--b列是把orderid中的记录分成了10,21,22,30,40,80这6个区，再在每个区上进行的排名。

SELECT班级名称,姓名,考生成绩,ROW_NUMBER()OVER(PARTITION BY班级名称ORDER BY 考生成绩desc)AS成绩排序号自然连号,RANK()OVER(PARTITION BY班级名称ORDER BY 考生成绩desc)AS成绩排序号不连号, DENSE_RANK()OVER(PARTITION BY班级名称ORDER BY考生成绩)AS成绩排序号重复连号FROM student ORDER BY班级名称,考生成绩desc;

我们看到排名函数可以很简便的得到各种类型的排名

以下是我对4个排名函数的类比表格：

排名连续性排名并列性

RANK() 不一定连续有并列

DENSE_RANK() 连续有并列

ROW_NUMBER() 连续无并列

NTILE() 连续有并列

SQL Server 2008 联机丛书（2008 年8 月）

排名函数(Transact-SQL)

排名函数为分区中的每一行返回一个排名值。根据所用函数的不同，某些行可能与其他行接收到相同的值。排名函数具有不确定性。

Transact-SQL 提供下列排名函数：

示例

以下示例显示了用在同一查询中的四个排名函数。有关每个函数的具体示例，请参阅每个排名函数。

复制代码

USE AdventureWorks;

GO

SELECT c.FirstName, https://www.360docs.net/doc/8617751775.html,stName

,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.PostalCode) 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;

下面是结果集：

请参阅

参考

MS SQL Server：排名函数详解

上一篇/ 下一篇2008-08-02 22:43:38 / 个人分类：MS SQL Server

查看( 52 ) / 评论( 2 )

排名函数详解

SQL Server 2005 能够在T-SQL 代码中对数据行进行排名。排名函数（ranking function）能对每一个数据行进行排名，从而提供一种以升序来组织输出的方法。可以给每一行一个唯一的序号，或者给每一组相似的行相同的序号。

排名函数有四种类型：

? ROW_NUMBER ：为查询的结果行提供连续的整数值。

RANK? ：为行的集合提供升序的、非唯一的排名序号，对于具有相同值的行，给予相同的序号。由于行的序号有相同的值，因此，要跳过一些序号。

? DENSE_RANK ：与RANK类似，不过，无论有多少航具有相同的序号，DENSE_RANK 放回的每一行的序号将比前一个序号增加1.

? NTILE ：把从查询中获取的行放置到具有相同的（或尽可能相同的）行数的、特定序号的组中，NTILE 返回行所属的组的序号。

排名函数语法如下：

() OVER ([PARTITION BY ])

ORDER BY < order_by_list >

按照选项到来的顺序给定这些选项，可以看到能够怎样在SELECT 语句中放置选项，例如：? function_name ：可以是ROW_NUMBER / RANK / DENSE_RANK / NTILE 之一。

OVER? ：定义排名应该如何对数据排序或划分。

PARTITION BY ：定义列将使用什么数据作为划分的基线。?

? ORDER BY ：定义数据排序的详情。

以下示例显示了用在同一查询中的四个排名函数。有关每个函数的具体示例，请参阅每个排名函数。

USE AdventureWorks;

GO

SELECT c.FirstName, https://www.360docs.net/doc/8617751775.html,stName

,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;

下面进行详细的说明。

1. ROW_NUMBER

说明：返回结果集分区内行的序列号，每个分区的第一行从 1 开始。

语法：

ROW_NUMBER ( ) OVER ( [ ] )

例1：以下示例将根据年初至今的销售额，返回AdventureWorks 中销售人员的ROW_NUMBER。

USE AdventureWorks;

GO

SELECT c.FirstName, https://www.360docs.net/doc/8617751775.html,stName

,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 将对ROW_NUMBER 进行排序。如果将ORDER BY 子句添加到以非'Row Number' the 的某列（或多列）作为排序依据的SELECT 语句，则结果集将以外部的ORDER BY 为依据进行排序。

例2：以下示例将返回行号为50 到60 的行（包含这两行），并按OrderDate 进行排序。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 参数。

USE AdventureWorks;

GO

SELECT c.FirstName, https://www.360docs.net/doc/8617751775.html,stName

,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

说明：返回结果集的分区内每行的排名。行的排名是相关行之前的排名数加一。

示例：以下示例按照数量对清单中的产品进行了排名。行集按LocationID 分区，按Quantity 排序。注意，OVER 子句中的ORDER BY 对RANK 进行排序，SELECT 语句的ORDER BY 对结果集进行排序。

USE AdventureWorks;

GO

SELECT i.ProductID, https://www.360docs.net/doc/8617751775.html,, 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 https://www.360docs.net/doc/8617751775.html,;

GO

3. DENSE_RANK

4. NTILE

说明：将有序分区中的行分发到指定数目的组中。各个组有编号，编号从一开始。对于每一个行，NTILE 将返回此行所属的组的编号。

参数：

integer_expression ：

一个正整数常量表达式，用于指定每个分区必须被划分成的组数。integer_expression 的类型可以为int 或bigint。

注意：

integer_expression 只能引用PARTITION BY 子句中的列。integer_expression 不能引用在当前FROM 子句中列出的列。

备注：

如果分区的行数不能被integer_expression 整除，则将导致一个成员有两种大小不同的组。按照OVER 子句指定的顺序，较大的组排在较小的组前面。例如，如果总行数是53，组数是5，则前三个组每组包含11 行，其余两个组每组包含10 行。另一方面，如果总行数可被组数整除，则行数将在组之间平均分布。例如，如果总行数为50，有五个组，则每组将包含10 行。

例1：将行分为组

以下示例将行分成四组。由于总行数不能被组数整除，因此第一个组将包含四行，其余每组包含三行。

USE AdventureWorks;

GO

SELECT c.FirstName, https://www.360docs.net/doc/8617751775.html,stName

,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 划分结果集

以下示例将PARTITION BY 参数添加到示例A 中的代码。首先按PostalCode 将行分区，然后在每个PostalCode 内将行分成四个组。注意，OVER 子句中的ORDER BY 对NTILE 进行排序，SELECT 语句的ORDER BY 对结果集进行排序。

USE AdventureWorks;

GO

SELECT c.FirstName, https://www.360docs.net/doc/8617751775.html,stName

,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 编辑]

Sql Server2005对t-sql的增强之排名函数

https://www.360docs.net/doc/8617751775.html, 2008-04-28 11:47 赵玉开博客园我要评论(0)

Sql Server2005中新增加了4个排名函数：ROW_NUMBER, RANK, DENSE_RANK, NTILE；大家一定已经对ROW_NUMBER非常熟悉了，所以我从最后一个NTILE开始分析。

我们给NTITL传的参数是3，即表示每一区将分成三组，然后OVER中表达式指定要根据categoryId来分割分组，并要按照categoryId排序。上面的表达式执行结果如下：

版权声明：原创作品，如需转载，请与作者联系。否则将追究法律责任。

一、简介

在2005年11月份，微软发行了三种新产品系列：Visual Studio 2005，SQL Ser ver 2005和.NET框架2.0(它包括https://www.360docs.net/doc/8617751775.html, 2.0)。SQL Server 2005是微软自从其上一个主要发行版本SQL Server 2000以来最新版本的数据库平台。在过去五年的发展中，SQL Server中加入了大量的新特征，所有这些新内容都被总结到微软网站的一篇文章《W hat's New in SQL Server 2005?》中。使用SQL Server 2005作为后端数据库构建基于web应用程序的开发者很可能会对这些新特征抱有浓厚的兴趣，这些新特征包括新的T-SQL改进，更好的Visual Studio集成，与CLR/.NET框架的集成，以及SQL Server 2005 Management Studio应用程序（它是SQL Server 2000的企业管理器的一个更为"平滑"的版本）。

与以前的SQL Server 2000相比，2005中的T-SQL改进使得编写某些类型的查询极为容易。在SQL Server 2005中，T-SQL语法更为精练、可读和易于理解。

在本文中，我们将专门探讨SQL Server 2005的评价函数，它们大大简化了对查询结果进行评价的过程。

二、数据模型和评价结果基础

在我们分析如何使用普通查询模式之前，让我们首先创建一个能够运行这些查询的数据模型。在本文中，我使用SQL Server 2005 Express版本来实现我的演示，并且包括了一个数据库和一个https://www.360docs.net/doc/8617751775.html, 2.0网站（请参考本文相应的完整源码。就象Visual Studi o一样，SQL Server发行中也一同加杂了其它一些不同的版本。其中，Express版本是一个针对业余爱好者、学生等群体的免费版本。如果你下载和安装Visual Web Developer （Visual Studio针对web开发者的Express版本），那么你可以选择一同安装SQL Ser ver 2005 Express版本）。

对于本文中的示例，我们将使用一个含有产品、销售人员（雇员）、顾客和订单信息的数据库。我们使用五个表来建模：Customers，Employees，Products，Orders和Ord erItems。其中，Customers，Employees和Products表分别包含每一个顾客，雇员和产品信息的行记录数据。每当一个顾客进行购买活动，一条新记录被添加到Orders表中，其中的信息指示该顾客实现了购买、该雇员进行的这一销售活动及订单的日期。其中，Ord erItems映射订单中的每一件产品，产品的数量和价格总值(假定较大的购买量可以打折)。下图展示了这些表（及字段）以及它们之间的关系。

如图所展示的，这个OrderItems在Orders和Products表之间建立一个对多对的连接。

当构建报告或分析数据时，用户或管理员经常希望看到以某种方式对数据的评价信息。例如，你的老板可能想要一个报告来显示卖路最好的前十项，或在第三个季度销售部中实现最大收入的前三名销售人员。更复杂的情况可能是仅返回第3到第5个评价排名的销售人员。在SQL Server 2000中，返回最高排名项的查询可以通过使用TOP或ROWCOUN T关键字来实现。为了检索一个特定评价子集，你需要使用一种"派生表"（或者是一种基于视图的手段）。

SQL Server 2005中引入了四个新的评价函数：ROW_NUMBER，RANK，DENSE _RANK和NTILE。尽管这些与SQL Server 2000所提供的函数相比是一个明显的进步，但是这些函数的使用仍然存在一些限制（要求使用派生表或视图来实现功能更为强大的应用程序）。下面让我们分析一下每一个函数。

三、使用ROW_NUMBER函数计算行数

这个ROW_NUMBER函数把一个序数值赋给每一个返回的记录，该序数值依赖于一个特定的与这个函数一起使用的ORDER BY语句。函数ROW_NUMBER的语法是：ROW_ NUMBER() OVER([partition] ORDER BY子句)。例如，下列查询将返回从最贵的到最便宜的产品，对每一种产品按价格进行评价：

这个语句的执行结果如下表所示：

默认情况下，这个ROW_NUMBER函数把一个增量值（逐次加1）赋给结果集中的每一个记录。借助于可选的partition参数，无论何时分区（partitioning）列值发生变化，你都可以让ROW_NUMBER函数重新计算行数。为了说明这个问题，我使用如下查询语法创建了一个视图vwTotalAmountBilledPerOrder，它将返回每一个OrderID和该订购的总订单数：

这条语句将返回OrderItems表中每一个唯一的订单，还有相应于该订单的Amount Billed值的和。借助于这个视图，我们可以使用ROW_NUMBER方法来按最大花钱数来评价这些订单，如下所示：

这个语句将返回如下表所示的结果：

注意，某些顾客多次出现在这个列表中（如Bob，Tito和Bruce）。也许有时，我们不是想观看以销售量排序的所有订单，而更想看到每一个顾客的最高订单量。为此，我们可以通过使用ROW_NUMBER函数中的PARTITION BY子句达到这一目的，如下所示：

这个语句将返回如下表所示的结果：

注意，尽管这些结果非常不错；但是，你却不能在WHERE语句中使用ROW_NUMB ER()函数(或任何其它的评价函数)。也就是说，你可能想要说，"把按价格评价第5到第8名的产品列出"。为此，你需要使用一个派生的表或视图。例如，你可以把上面的查询放到一个视图vwPriceRankedProducts中，然后使用如下查询返回第5到第8个排名的产品：

SELECT ProductID，Name，Price，PriceRankFROM vwPriceRankedProductsWH ERE PriceRank BETWEEN 5 AND 8

四、使用RANK和DENSE_RANK处理同级问题

基于可选的partition子句和要求的order by子句，ROW_NUMBER函数默认地递增（加1）每一个返回结果的值。然而，有时你可能想以不同方式处理相同级别，而不是把相同的值赋给相同的级别。例如，前面显示的总订单列表中，Tito在2005年12月22日实现的订单数与Bruce在2006年1月5日实现的订单数相同；然而，ROW_NUMBER 函数却把这两行评价为#4和#5，而不是都评价为#4。

RANK和DENSE_RANK函数都能够使用相同的评价计数级。例如，使用下列查询：

这个语句将返回如下表所示的结果：