注册 登录  
 加关注
查看详情
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

午夜阳光

 
 
 

日志

 
 

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

2011-08-25 16:00:33|  分类: Sql Server |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
 SQL server 2005新增的几个函数,分别是row_number( )、rank( )、,DENSE_RANK( )、ntile( )下面以实例分别简单讲解。
1.row_number( )
         先来点数据,先建个表
SET NOCOUNT ON
CREATE TABLE Person(
FirstName VARCHAR(10),
Age INT,
Gender CHAR(1))
INSERT INTO Person VALUES ('Ted',23,'M')
INSERT INTO Person VALUES ('John',40,'M')
INSERT INTO Person VALUES ('George',6,'M')
INSERT INTO Person VALUES ('Mary',11,'F')
INSERT INTO Person VALUES ('Sam',17,'M')
INSERT INTO Person VALUES ('Doris',6,'F')
INSERT INTO Person VALUES ('Frank',38,'M')
INSERT INTO Person VALUES ('Larry',5,'M')
INSERT INTO Person VALUES ('Sue',29,'F')
INSERT INTO Person VALUES ('Sherry',11,'F')
INSERT INTO Person VALUES ('Marty',23,'F')
直接用例子说明问题:
SELECT ROW_NUMBER() OVER (ORDER BY Age) AS [Row Number by Age],
FirstName,
Age
FROM Person
出现的数据如下
Row Number by Age                FirstName            Age
--------------------------                 ----------            --------
1                                                Larry                   5
2                                                Doris                   6
3                                                George               6
4                                                Mary                   11
5                                                Sherry                 11
6                                                Sam                    17
7                                                Ted                     23
8                                                Marty                   23
9                                                Sue                     29
10                                              Frank                  38
11                                              John                    40
可以观察到,是根据年龄升序排列了,并且row_number()是给出了序列号了,这个序列号被重命名为Row Number by Age,
与sql server2000对比:
如果在sql server2000中实现相对麻烦一些,我们可以利用IDENTITY()函数实现,但IDENTITY()函数只能用在sql server2000临时表中,因此需要将数据检索到临时表里。
select identity(int,1,1) as [Row Number by Age],FirstName,Age into #A from Person order by Age
select * from #A
drop table #a
如果不想按年龄排序,可以这样写
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS [Row Number by Record Set], FirstName, Age FROM Person
另外一个例子
SELECT ROW_NUMBER() OVER (PARTITION BY Gender ORDER BY Age) AS [Partition by Gender], FirstName, Age, Gender FROM Person

这里是按性别划分区间了,同一性别再按年龄来排序,输出结果如下
Partition by Gender        FirstName        Age               Gender -------------------- ---------- ----------- ------ 1                          Doris            6                 F 2                          Mary             11                F 3                          Sherry           11                F 4                          Sue              29                F 1                          Larry            5                 M 2                          George           6                 M 3                          Sam              17                M 4                          Ted              23                M 5                          Marty            23                M 6                          Frank            38                M 7                          John             40                M
注意,姓名M开始,序号又从1,2,3开始了
 
2.RANK( )函数
        先看例子
SELECT RANK() OVER (ORDER BY Age) AS [Rank by Age], FirstName, Age FROM Person
输出如下:
Rank by Age                FirstName        Age -------------------- ---------- ----------- 1                          Larry            5 2                          Doris            6 2                          George           6 4                          Mary             11 4                          Sherry           11 6                          Sam              17 7                          Ted              23 7                          Marty            23 9                          Sue              29 10                         Frank            38 11                         John             40
看到了么,同年岭的话,将有相同的顺序,顺序成1,2,2,4了。
与sql server2000对比:
出现了RANK()函数实在是方便,在sql server2000里实现排序并列的问题麻烦很多。
select [Rank by Age]=isnull((select count(*) from person where Age>A.Age),0)+1,FirstName,Age from Person A order by [Rank by Age]
 
SELECT RANK() OVER(PARTITION BY Gender ORDER BY Age) AS [Partition by Gender],
FirstName, Age, Gender FROM Person输出为
Partition by Gender        FirstName        Age               Gender -------------------- ---------- ----------- ------ 1                          Doris            6                 F 2                          Mary             11                F 2                          Sherry           11                F 4                          Sue              29                F 1                          Larry            5                 M 2                          George           6                 M 3                          Sam              17                M 4                          Ted              23                M 4                          Marty            23                M 6                          Frank            38                M 7                          John             40                M

可以看到,按性别分组了,每个性别分组里,继续是用了rank( )函数
3.DENSE_RANK( )函数
         SELECT DENSE_RANK() OVER (ORDER BY Age) AS [Dense Rank by Age],
         FirstName,
         Age
         FROM Person
输出结果为:
Dense Rank by Age          FirstName        Age
-------------------- ---------- -----------
1                          Larry            5
2                          Doris            6
2                          George           6
3                          Mary             11
3                          Sherry           11
4                          Sam              17
5                          Ted              23
5                          Marty            23
6                          Sue              29
7                          Frank            38
8                          John             40
看到了么,和rank函数区别是,顺序始终是连续的,Doris 和George同年,都是排第2位,但之后的mary不象rank函数那样排第4,而是排第3位了
4.ntile( )函数
SELECT FirstName,
Age,
NTILE(3) OVER (ORDER BY Age) AS [Age Groups]
FROM Person

输出结果:
FirstName        Age               Age Groups
---------- ----------- --------------------
Larry                5                  1
Doris                6                  1
George            6                  1
Mary                11                1
Sherry             11                 2
Sam                17                 2
Ted                 23                 2
Marty              23                 2
Sue                29                 3
Frank             38                 3
John               40                 3
这个函数按照ntile(n)中的N,把记录强制分成多少段,11条记录现在分成3段了,lary到mary是第1段,sherry到maty是第2段,sue到john是第3段了。

  评论这张
 
阅读(191)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018