`
chenhongquan
  • 浏览: 42718 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

SQL server 2005新增的几个函数 转

阅读更多

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 Ageselect * from #Adrop 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段


本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/htl258/archive/2009/03/20/4006717.aspx

分享到:
评论

相关推荐

    Microsoft SQL Server 2005技术内幕: T-SQ程序设计.pdf

    该书解释并比较了SQL Server 2000和SQL Server 2005在数据库开发相关问题上的解决方案,深入讨论了SQL Server 2005中新增的T-SQL编程特性,包含了大量的代码示例、表示例和逻辑难题以帮助数据库开发人员和管理员理解...

    SQL2005工作经验

    case在where条件中的应用; SQL注入;... SQL 2005新增的几个函数之学习; 多数据库查询; 利用NEWID函数来取随机记录; 利用ROW_NUMBER方法分页; 在数据库中处理字符串数组; ......

    SqlServer2012中LEAD函数简单分析

    SQL SERVER 2012 T-SQL新增几个聚合函数: FIRST_VALUE LAST_VALUE LEAD LAG,今天我们首先来简单分析下LEAD,希望对大家有所帮助,能够尽快熟悉这个聚合函数

    SqlServer2012中First_Value函数简单分析

    SQL SERVER 2012 T-SQL新增几个聚合函数: FIRST_VALUE LAST_VALUE LEAD LAG,今天我们首先来简单分析下FIRST_VALUE,希望对大家有所帮助,能够尽快熟悉这个聚合函数

    经典SQL语句大全

    在SQLServer中分组时:不能以text,ntext,image类型的字段作为分组依据 在selecte统计函数中的字段,不能和普通的字段放在一起; 13、对数据库进行操作: 分离数据库: sp_detach_db; 附加数据库:sp_attach_db 后...

    PHP连接MSSQL2005的扩展插件.

    SQL Server Driver for PHP :http://www.microsoft.com/downloads/details.aspx?familyid=CCDF728B-1EA0-48A8-A84A-5052214CAAD9&displaylang=en , 这是一个自解压的EXE文件,解压缩后你会得到这么几个文件: ...

    sql经典语句一部分

    在SQLServer中分组时:不能以text,ntext,image类型的字段作为分组依据 在selecte统计函数中的字段,不能和普通的字段放在一起; 13、对数据库进行操作: 分离数据库: sp_detach_db; 附加数据库:sp_attach_db 后...

    数据库操作语句大全(sql)

    在SQLServer中分组时:不能以text,ntext,image类型的字段作为分组依据 在selecte统计函数中的字段,不能和普通的字段放在一起; 13、对数据库进行操作: 分离数据库: sp_detach_db; 附加数据库:sp_attach_db ...

    数据库导入导出工具DataPie3.0

    重构整个数据访问层,增强了扩展性(只需要实现接口IDBUtility中的几个方法,即可轻松扩展到其它数据库)。 整合调整了一些类库,修正了模板导出时,格式与数据库中字段顺序不一致的问题。 增加了分多个EXCEL导出表...

    DataPie数据库导入导出工具 v3.4源码2012810

    重构整个数据访问层,增强了扩展性(只需要实现接口IDBUtility中的几个方法,即可轻松扩展到其它数据库)。 整合调整了一些类库,修正了模板导出时,格式与数据库中字段顺序不一致的问题。 增加了分多个EXCEL导出表...

    asp.net知识库

    ASP.NET 2.0基于SQLSERVER 2005的aspnetdb.mdf部署 ASP.NET 2.0 Security FAQs Asp.net 2.0功能体验,细节之Web控件(一) 隐藏控件 Asp.net 2.0功能体验,总体设计思想 Asp.net 2.0 WebPart使用经验点滴 革新:.NET...

    数据库开发框架(PDF版)

    则也显示红色.*NEW:上面说的功能,把它写在了一个函数中,SetGridTitleColor,而且只在Dataset的AfterOpen中执行一次,这样程式的性能应该会有提高.2005-10-16 jacky*BUG:主窗口的状态栏上显示文字的格子不够宽,加宽一些...

    ASP设计在线考试系统OnlineTest

    该在线考试系统主要采用ASP、HTML、Access数据库、VBScript、SQL等技术和工具〖本文来自论文路上,Www.Lw63.Com,专业毕业设计〗整体设计遵循软件工程的方法,经过需求分析、总体设计、文档和代码的编制、模块测试和...

    asp在线考试系统(asp+access实现)

    该在线考试系统主要采用ASP、HTML、Access数据库、VBScript、SQL等技术和工具〖本文来自论文路上,Www.Lw63.Com,专业毕业设计〗整体设计遵循软件工程的方法,经过需求分析、总体设计、文档和代码的编制、模块测试和...

    C#编程经验技巧宝典

    73 <br>0106 如何获得一个字符串中数字的长度 74 <br>0107 如何获得字符串中数字或字母的长度 74 <br>0108 如何获得字符串中某个数字的位置 75 <br>0109 获得字符串中汉字的个数 76 <br>0110...

    Microsoft Visual C# 2008从入门到精通

    书中沿袭深受读者欢迎的Step by Step风格,通过丰富的练习引导读者逐步构建Windows应用程序,访问sql server数据库,开发ASP.NET Web应用程序,创建并使用Web服务等。 《Visual C# 2008从入门到精通》以最新的.NET ...

    03开源NewSql数据库TiDB-Deep Dive into TiDB

    在这一版本中,SQL 执行引擎引入新的内部数据表示方式 --- `Chunk`,一个结构中保存一批数据而不仅是一行数据,同一列的数据在内存中连续存放,使得内存使用更紧凑,这样带来了几点好处:1. 显著减小了内存消耗; 2....

Global site tag (gtag.js) - Google Analytics