SQL2005中row_number()、rank()、dense_rank()、ntile()函数的用法

<span style="font-size:18px;">create table gg(sname varchar(10),sort varchar(10),num int)
go
insert into gg
select '白芍','根茎类',55
union all
select '法半夏','根茎类',78
union all
select '柴胡','根茎类',60
union all
select '川芎','根茎类',99
union all
select '天香炉','草类',68
union all
select '灯心草','草类',55
union all
select '龙葵','草类',60
union all
select '石见穿','草类',60
union all
select '猪笼草','草类',70
union all
select '益母草','草类',86
union all
select '扁豆','果实类',86
union all
select '草果','果实类',70
union all
select '金樱子','果实类',55
union all
select '女贞子','果实类',94
union all
select '胖大海','果实类',66
union all
select '桑葚','果实类',78
select sname,sort,num,
row_number() over(order by num) as rownum,
rank() over(order by num) as ranknum,
dense_rank() over(order by num) as dersenum,
ntile(3) over(order by num) as ntilenum
from gg
--结果
--ROW_NUMBER()是按num由小到大逐一排名，不并列，排名连续
--RANK()是按num由小到大逐一排名，并列，排名不连续
--DENSE_RANK()是按num由小到大逐一排名，并列，排名连续
--NTILE()是按num由小到大分成组逐一排名，并列，排名连续
sname sort num rownum ranknum dersenum ntilenum
-------- --------- --------- ------------- ------------- --------------- ---------------
白芍 根茎类 55 1 1 1 1
灯心草 草类 55 2 1 1 1
金樱子 果实类 55 3 1 1 1
龙葵 草类 60 4 4 2 1
石见穿 草类 60 5 4 2 1
柴胡 根茎类 60 6 4 2 1
胖大海 果实类 66 7 7 3 2
天香炉 草类 68 8 8 4 2
草果 果实类 70 9 9 5 2
猪笼草 草类 70 10 9 5 2
法半夏 根茎类 78 11 11 6 2
桑葚 果实类 78 12 11 6 3
益母草 草类 86 13 13 7 3
扁豆 果实类 86 14 13 7 3
女贞子 果实类 94 15 15 8 3
川芎 根茎类 99 16 16 9 3
(16 行受影响)
select sname,sort,num,
row_number() over(partition by sort order by num) as rownum,
rank() over(partition by sort order by num) as ranknum,
dense_rank() over(partition by sort order by num) as dersenum,
ntile(3) over(partition by sort order by num) as ntilenum
from gg
--结果
此时加了partition by sort，就以类别来分类了，ntile(3)意思就是强制分为三组。
sname sort num rownum ranknum dersenum ntilenum
-------- ---------- --------- ------------- --------------- ---------------- -----------
灯心草 草类 55 1 1 1 1
龙葵 草类 60 2 2 2 1
石见穿 草类 60 3 2 2 2
天香炉 草类 68 4 4 3 2
猪笼草 草类 70 5 5 4 3
益母草 草类 86 6 6 5 3
白芍 根茎类 55 1 1 1 1
柴胡 根茎类 60 2 2 2 1
法半夏 根茎类 78 3 3 3 2
川芎 根茎类 99 4 4 4 3
金樱子 果实类 55 1 1 1 1
胖大海 果实类 66 2 2 2 1
草果 果实类 70 3 3 3 2
桑葚 果实类 78 4 4 4 2
扁豆 果实类 86 5 5 5 3
女贞子 果实类 94 6 6 6 3
(16 行受影响)
下面分别用SQL 2000实现，相对比2005要麻烦的多了。
--ROW_NUMBER在sql 2000中的实现
--利用临时表和IDENTITY（函数）
select sname,num,identity(int,1,1) as rownumber
into #tem 
from gg
order by num
select sname,num,rownumber
from #tem
drop table #tem
go
--RANK在sql 2000中的实现
select sname,num,
(select count(1)+1 from gg where num<g.num) as ranknum
from gg g
order by num
go
--DENSE_RANK在sql 2000中的实现
select num,identity(int,1,1) as densenum 
into #t
from gg
group by num
order by num
select r.sname,r.num,t.densenum
from gg r join #t t
on r.num=t.num
order by num
drop table #t
go</span>

下面介绍两个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

以下示例将返回行号为  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 ;

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