Sql Server2005 Transact-SQL 新兵器学习总结之-排名函数

系列文章导航

Sql Server2005 Transact-SQL 新兵器学习总结之-总结

免费美女视频聊天,多人视频会议功能加强版本(Fms3和Flex开发(附源码))

 

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

create table rankorder(

orderid int,

qty int

)

go

--插入数据

insert rankorder values(30,10)

insert rankorder values(10,10)

insert rankorder values(80,10)

insert rankorder values(40,10)

insert rankorder values(30,15)

insert rankorder values(30,20)

insert rankorder values(22,20)

insert rankorder values(21,20)

insert rankorder values(10,30)

insert rankorder values(30,30)

insert rankorder values(40,40)

go

--查询出各类排名

SELECT orderid,qty,

  ROW_NUMBER() OVER(ORDER BY qty) AS rownumber,

  RANK()       OVER(ORDER BY qty) AS [rank],

  DENSE_RANK() OVER(ORDER BY qty) AS denserank ,

  NTILE(3) OVER(ORDER BY qty) AS [NTILE]

FROM rankorder

ORDER BY qty

--结果

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

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

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

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

orderid    qty    rownumber    rank    denserank    NTILE

30        10    1            1        1            1

10        10    2            1        1            1

80        10    3            1        1            1

40        10    4            1        1            1

30        15    5            5        2            2

30        20    6            6        3            2

22        20    7            6        3            2

21        20    8            6        3            2

10        30    9            9        4            3

30        30    10            9        4            3

40        40    11            11        5            3

复制代码

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

复制代码

--RANK在sql 2000中的实现

select orderid,qty,

    (select count(1)+1 from rankorder where qty<r.qty) as [rank]

from rankorder r

ORDER BY qty

go

 

--ROW_NUMBER在sql 2000中的实现

--利用临时表和IDENTITY（函数）

select identity(int,1,1) as [ROW_NUMBER],orderid,qty

into #tem             

from rankorder

select orderid,qty,[ROW_NUMBER]

from #tem

drop table #tem

go

--DENSE_RANK在sql 2000中的实现

select identity(int,1,1) as ids,  qty

into #t

from rankorder

group by qty

order by qty

select r.orderid,r.qty,t.ids as [DENSE_RANK]

from rankorder r join #t  t

on r.qty=t.qty

drop table #t

go

复制代码

复制代码

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

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

--例题

SELECT orderid,qty,

DENSE_RANK() OVER(ORDER BY qty) AS a ,

DENSE_RANK() OVER(PARTITION BY orderid ORDER BY qty) AS b     

FROM rankorder

ORDER BY qty

--说明：

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

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

orderid    qty    a    b

10        10    1    1

30        10    1    1

40        10    1    1

80        10    1    1

30        15    2    2

30        20    3    3

21        20    3    1

22        20    3    1

10        30    4    2

30        30    4    4

40        40    5    2

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

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

复制代码

                               排名连续性   排名并列性

RANK()                 不一定连续    有并列

DENSE_RANK()    连续               有并列

ROW_NUMBER()   连续             无并列 

NTILE()                  连续               有并列

 

 

收藏与分享

收藏到QQ书签 添加到百度搜藏 添加到百度搜藏 添加到雅虎收藏 分享到饭否 收藏到就喜欢网络收藏夹 

RSS订阅我 什么是RSS?

feedsky    http://wap.feedsky.com/aierongrss    E-mail 

订阅到雅蛙        使用RSS邮天下订阅    订阅到有道阅读 

订阅到抓虾    鲜果阅读器订阅图标    Add to Google 

訂閱 Bloglines    哪吒提醒    Subscribe in NewsGator Online

东莞.net俱乐部

东莞.net俱乐部 欢迎您的加入

我的系列文章

A.Sql Server2005 Transact-SQL 新兵器学习 

B.MCAD学习 

C.代码阅读总结 

D.ASP.NET状态管理 

E.DB(数据库) 

F.WAP 

G.WinForm 

H.Flex

我的好文推荐

FlexAir开源版-全球免费多人视频聊天室,免费网络远程多人视频会议系统((Flex,Fms3联合开发))<视频聊天,会议开发实例8> 

Sql Server2005 Transact-SQL 新兵器学习总结之-总结 

MS SQL数据库备份和恢复存储过程(加强版本) 

sql server中分布式查询随笔(链接服务器(sp_addlinkedserver)和远程登录映射(sp_addlinkedsrvlogin)使用小总结) 

ASP.NET2.0国际化/本地化应用程序的实现总结(多语言,多文化页面的实现) 

WAP开发资料站(最新更新) 

自定义格式字符串随笔 (IFormattable,IFormatProvider,ICustomFormatter三接口的实现) 

Mcad学习笔记之异步编程(AsyncCallback 委托,IAsyncResult接口,BeginInvoke方法,EndInvoke方法的使用小总结) 

Mcad学习笔记之通过反射调用類的方法,屬性,字段,索引器(2種方法) 

Mcad学习笔记之序列化(2进制和Soap序列 化) 

Mcad学习笔记之委托再理解(delegate的构造器,BeginInvoke,EndInvoke,Invoke4个方法的探讨) 

ASP.NET状态管理之一（概括篇） 

Flex,Fms学习笔记

本文转自aierong博客园博客，原文链接http://www.cnblogs.com/aierong/archive/2008/08/18/1269407.html如需转载请自行联系原作者