SQL Server 2005中的ROW_NUMBER和RANK

时间:2021-12-22 10:06:12
下面介绍两个SQL Server 2005 中的新东东:
ROW_NUMBER
返回结果集分区内行的序列号,每个分区的第一行从 1 开始。

语法:
ROW_NUMBER ( )     OVER ( [ <partition_by_clause> ] <order_by_clause> )

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

参数:
<partition_by_clause>
将 FROM 子句生成的结果集划入应用了 ROW_NUMBER 函数的分区。
<order_by_clause>
确定将 ROW_NUMBER 值分配给分区中的行的顺序。

返回类型:
bigint

示例:
以下示例将根据年初至今的销售额,返回 AdventureWorks 中销售人员的 ROW_NUMBER
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


以下示例将返回行号为 5060(含)的行,并以 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 ;

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

语法:
RANK ( )    OVER ( [ < partition_by_clause > ] < order_by_clause > )

备注:
如果两个或多个行与一个排名关联,则每个关联行将得到相同的排名。例如,如果两位顶尖销售员具有同样的 SalesYTD 值,他们将并列第一。由于已有两行排名在前,所以具有下一个最大 SalesYTD 的销售人员将排名第三。因此,RANK 函数并不总返回连续整数。

用于整个查询的排序顺序决定了行在结果集中的显示顺序。这也隐含了行在每个分区中的排名。

参数:
< partition_by_clause >
将 FROM 子句生成的结果集划分为要应用 RANK 函数的分区。
< order_by_clause >
确定将 RANK 值应用于分区中的行时所基于的顺序。

返回类型:
bigint

示例:
以下示例按照数量对清单中的产品进行了排名。行集按 LocationID 分区,按 Quantity 排序。

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

下面是和朋友讨论的一个小例子,不知道对不对,大家指教啊:
一个记录集{1,1,1,4,4,4,7,7,7,7,7,7,7,7,7}
当用 rank between 1 and 10 过滤 {1,1,1,4,4,4,7,7,7,7,7,7,7,7,7}时,就会得到超过十条的记录
用rownumber between 1 and 10得到的是{1,1,1,4,4,4,7,7,7,7}
也就是说:
用rank就是同样的记录看作是进行一个排名,得到的有可能大于10条
例如:比赛中第一名1个,第二名1个,第三名3个,下一个人就不是第四名而是第六名
 

人员

名次

A

1

B

2

C

3

D

3

E

3

F

6

G

7

H

8

I

9

J

10


如果用rank就是从这个记录集中选出结果,例如前4名我们得到的是如下结果:
 

人员

名次

A

1

B

2

C

3

D

3

E

3

F

6


而rownumber不管重复仅得到4条而已,结果如下:
 

人员

名次

A

1

B

2

C

3

D

3