SQL2005四个排名函数（row_number、rank、dense_rank和ntile）的比较

最新推荐文章于 2024-09-14 15:35:55 发布
最新推荐文章于 2024-09-14 15:35:55 发布
阅读量1.4k 收藏 1
点赞数
分类专栏： SQLServer SQL应用 文章标签： sql sql server 服务器 数据库 permissions collation
本文详细介绍了SQL Server 2005 中新增的排名函数，包括 row_number()、rank()、dense_rank() 和 ntile() 的使用方法与特性。同时，文章还探讨了与安全相关的函数以及系统函数与系统变量的作用。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

排名函数是SQL Server2005新加的功能。在SQL Server2005中有如下四个排名函数：

row_number()

rank()

dense_rank()

ntile()


使用格式：X() over(order by field)


ROW_NUMBER()函数

ROW_NUMBER()函数根据作为参数传递给这个函数的ORDER BY子句的值，返回一个不断递增的整数值。如果ROW_NUMBER的ORDER BY的值和结果集中的顺序相匹配，返回值将是递增的，以升序排列。如果ROW_NUMBER的ORDER BY子句的值和结果集中的顺序不同，这些值将不会按顺序列出，但它们表示ROW_NUMBER函数的ORDER BY子句的顺序。如下面的例子和结果所示：

SELECT ProductCategoryID 

    ,Name

    ,ROW_NUMBER() OVER (ORDER BY Name) AS RowNum

FROM Production.ProductCategory

ORDER BY Name

 由于ROW_NUMBER()调用中的ORDERBY子句和查询结果的顺序匹配，所以对这些结果按顺序列出，如下图所示：

clip_image028

不过，在函数调用中使用另一个ORDER BY子句时，这些值就是无序的了。

SELECT ProductCategoryID 

    ,Name

    ,ROW_NUMBER() OVER (ORDER BY Name) AS RowNum

FROM Production.ProductCategory

ORDER BY ProductCategoryID

这是了解如何使用ORDER BY子句对结果进行排序的有效方法。如下图所示：

clip_image030

RANK()与DENSE_RANK()函数

这两个函数与ROW_NUMBER()函数类似，因为它们都返回一个基于ORDER BY子句的值。不过这些值不一定永远是唯一的。排列值对于所提供的ORDER BY子句中的重复结果而言也是重复的，而且唯一性是仅仅基于ORDER BY列表中的唯一值的。这些函数用不同的方法来处理重复的值。RANK()函数保留列表中行的位置序号，对于每个重复的值，该函数会跳过下面与其相邻的值，于是就可以将下一个不重复的值保留在正确的位置上。

 其行为类似于短跑比赛中的并列成绩。例如刘翔与Dayron Robles（古巴）在110栏的比赛中都跑出了12’92的成绩，那他们就是并列第一，而其后的一名选手将会获得第三名的成绩。

SELECT ProductID 

    ,Name

    ,ListPrice

    ,RANK() OVER (ORDER BY ListPrice DESC) AS [Rank]

FROM Production.Product

ORDER BY [Rank]

注意在下图的结果列表中，重复的价格值所对应的结果是相同的，而每个连接之后的值都被跳过了。比如，产品"Road-150 Red, 52"和"Road-150 Red, 56"都排在第1，而接下来的行"Mountain-100 Silver,38"就排在第6了。

clip_image032

DENSE_RANK()函数的工作方式与RANK()函数相同，不过它不会跳过每个连接后的值，这样就不会有值被跳过了，但是在连接处排列序号位置将会丢失。

SELECT ProductID 

    ,Name

    ,ListPrice

    ,DENSE_RANK() OVER (ORDER BY ListPrice DESC) AS [Rank]

FROM Production.Product

ORDER BY [Rank]

下图的结果重复了排列值，但是不会跳过列中的任何数字。

clip_image034

NTILE(n)函数

这个函数也用于对结果进行排列，并返回一个整型的排列值，但是它不会对结果以唯一的排列顺序进行枚举，而是将结果切分为有限数量的排列组。比如，一个表有10 000行，使用1000为参数值调用NTILE()函数，即NTILE(1000)，并将结果分成以10为单位的1000个组，每个组赋予相同的排列值。和本节讨论的其他排列函数一样，NTILE()函数也支持OVER(ORDER BY…)语法。下面的例子根据产品价格，按照从高到低的顺序把Product表分为50组产品：

SELECT ProductID 

    ,Name

    ,ListPrice

    ,NTILE(50) OVER (ORDER BY ListPrice DESC) AS GroupedProducts

FROM Production.Product

ORDER BY GroupedProducts

结果为：

clip_image036

安全函数

与安全相关的函数返回SQL Server用户的角色成员和权限信息。这类函数也包括一组管理事件与跟踪的函数。下表显示了这些函数：

函    数

说    明

fn_trace_geteventinfo()

为指定的跟踪ID返回一个填充事件信息的表类型值

fn_trace_getfilterinfo()

为指定的跟踪ID返回一个填充与过滤器有关的信息的表类型值

fn_trace_getinfo()

为指定的跟踪ID返回一个填充跟踪信息的表类型值

fn_trace_getable()

为指定的跟踪ID返回一个填充文件信息的表类型值

HAS_DBACCESS()

返回一个表明当前用户是否有访问指定数据库权限的标志

IS_MEMBER()

返回一个表明当前用户是Windows组用户还是SQL Server用户的标志

IS_SRVROLEMEMBER()

返回一个表明当前用户是否是数据库服务器角色成员的标志

SUSER_SID()

返回指定用户的登录名的安全ID，或者(如果参数被忽略)返回当前用户的安全ID。返回指定用户的用户ID，或者(如果参数被忽略的话)返回当前用户的用户ID

SUSER_SNAME()

返回指定安全ID的登录名。如果不提供任何安全ID，则返回当前用户的登录名

USER_ID()

返回指定用户名的用户ID，或者(如果参数被忽略的话)返回当前用户的用户ID

USER_NAME()

返回指定用户ID的用户名

系统函数与系统变量

本节讨论具有多种用途的工具函数，包括值比较、值类型测试等功能。这个类别的函数也包罗了其他函数：

函    数

说    明

APP_NAME()

返回与当前连接相关联的应用程序的名字

COALESCE()

从以逗号分隔的表达式列表中返回第一个非空值

COLLATIONPROPERTY()

返回一个特定字符集排序规则的特定属性的值。这些属性包括CodePage、LCID、ComparisonStyle

CURRENT_TIMESTAMP()

返回当前日期与时间。和GETDATE()函数是同义的。这个函数的存在只是为了与ANSI-SQL兼容

C1UJRRENT_USER()

返回当前用户的名字。与USER_NAME()函数相同

DATALENGTH()

返回存储或处理一个值所需的字节数。对于ANSI字符串类型，这个函数返回的值与LEN()函数相同，但对于其他数据类型而言就可能不一定相同了

fn_helpcollations()

返回一个填充有由当前SQLSewer版本支持的字符集排序规则的表类型值

fn_servershareddrives()

返回一个填充有服务器共享的驱动列表的表类型值

fn_virtualfilestats()

返回一个填充有包括日志文件在内数据库文件的I/O状态的表类型值

FORMATMESSAGE()

从sysmessages表中为指定的信息代码和以逗号分隔的参数列表返回错误信息

GETANSINULL()

根据ANSLNULL_DFLT_ON与ANSLNULL_DFLT_OFF数据库设置返回数据库的可空性设置

HOST_ID()

返回当前会话的工作站ID

HOST_NAME()

返回当前会话的工作站名

IDENT_CURRENT()

返回最后一个为指定的表生成的标识(ID)值。与会话、范围无关

IDENT_INCR()

返回最后一次创建的标识(ID)列中定义的增量值

IDENT_SEED()

返回最后一次创建的标识(ID)列中定义的种子值

IDENTITY()

用在SELECT…INTO语句中，在一个列中插入自动生成的标识值

ISDATE()

返回一个表明指定的值是否可被转换为日期值的标志

ISNULL()

判断指定的值是否是空值，然后返回一个事先提供的替代值

ISNUMERIC()

返回一个表明指定的值是否可被转换为数字值的标志

NEWID()

返回一个新生成的UniqueIdentifier类型的值。这是一个128位的整型、全球唯一的值，通常以字母或数字十六进制来表示(如89DE6247·C2E242DB-8CE8·A787E505D7EA)。这个类型经常被用作复制的和半连接系统中的主键.

NULLIF()

两个特定的参数的值如果是相同的，则返回NULL

PARSENAME()

返回一个具有4部分对象名的特定部分

PERMISSIONS()

返回一个整型值，该值是一个表示当前用户在指定的数据库对象上权限或者权限组合的位映像

ROWCOUNT_BIG()

与@@RowCount变量一样，这个函数返回被最后一条语句修改或返回的行数量。返回值类型是bigint

SCOPE_IDENTITY()

与@@IDENTIY变量一样，这个函数返回限制在当前会话与范围内的最后一次生成的标识值

SERVERPROPERTY()

返回一个表示服务器属性状态的标记。属性包括Collation、Edition、EngineEdition、InstanceName、IsClustered、IsFullTextInstalled、IsIntegrated- SecurityOnly、IsSingleUser、IsSyncWithBackup、LicenseTYpe、MachineName、NumLicenses、ProcessID、ProductLevel、ProductVersion、ServerName

SESSION_USER

返回当前用户名。调用本函数不需要括号

SESSIONPROPERTY()

返回表示一个会话属性状态的标记。属性包括:ANSL_NULLS，ANSI_PADDING，ANSL_WARNINGS，ARITHABORT，CONCAT_NULL_ YIELDS_NULL，NUMERIC_ROUNDABORT，QUOTED_IDENTIFIER

STATS_DATE()

返回指定的索引统计信息最后一次被更新的时间

SYSTEM_USER

返回当前用户名。调用本函数不需要括号

USER_NAME()

为一个指定的用户ID返回用户名。如果没有提供ID号则返回当前的数据库用户

COALESCE()函数

COALESCE()函数是非常有用的，它返回其参数中第一个非空表达式。它能够节省颇多IF或者CASE分支逻辑。以下例子用产品数据填充一个表，每个产品最多有3种价格:

CREATE TABLE #ProductPrices (ProductName varchar(25), SuperSalePriceMoney NULL, SalePrice Money NULL, ListPrice Money NULL) 

GO 

INSERT INTO #ProductPrices VALUES('Standard Widget', NULL, NULL,15.95) 

INSERT INTO #ProductPrices VALUES('Economy Widget', NULL, 9.95, 12.95) 

INSERT INTO #ProductPrices VALUES('Deluxe Widget', 19.95, 20.95,22.95) 

INSERT INTO #ProductPrices VALUES('Super Deluxe Widget', 29.45, 32.45,38.95) 

INSERT INTO #ProductPrices VALUES('Executive Widget', NULL, 45.95,54.95) 

GO

 所有的产品都有定价，有些有销售价，有些还有促销价。一项产品的当前价格是所有己有价格的最低价，或者在读取每个价格列时以列出顺序读到的第一个非空值:

SELECT ProductName, COALESCE(SuperSalePrice, SalePrice, ListPrice) ASCurrentPrice

FROM #ProductPrices

 这个方法比使用多行分支与判断逻辑要简洁得多，而结果也是同样简单，如下图所示：

clip_image038

DATALENGTH()函数

DATALENGTH()函数返回一个用于对值进行管理的字节数，这有助于揭示不同数据类型间的一些有趣差别。当把varchar类型传递给DATALENGTH()和LEN()函数时，它们将返回相同的值：

DECLARE @Value varchar(20) 

SET @Value = 'abc'

SELECT DATALENGTH(@Value) 

SELECT LEN(@Value)

 这些语句的返回值都为3。因为varchar类型使用了3个单字节字符来存储三个字符的值。然而，如果使用nVarchar类型来管理相同长度的值，就要占用多一倍的字节:

DECLARE @Value nvarchar(20) 

SET @Value = 'abc'

SELECT DATALENGTH(@Value) 

SELECT LEN(@Value)

DATALENGTH()函数返回值为6，因为每个使用Unicode字符集的字符都要占用2个字节。LEN()函数返回值为3，因为这个函数返回字符数，不是字节数。以下是一个有趣的测试：要存储一个值为2的整型变量，要占用多少个字节？而如果要存储一个值为20亿的整型变量，又将占用多少个字节呢？试一下：

DECLARE @Value1 int, @Value2 int

SET @Value1 = 2 

SET @Value2 = 2000000000 

SELECT DATALENGTH(@Value1) 

SELECT LEN(@Value1) 

SELECT DATALENGTH(@Value2) 

SELECT LEN(@Value2)

 在这两种情况下，DATALENGTH()函数都返回4。因为int类型不论值是多少，总是使用4个字节。LEN()函数本质上将整型值当成已转换成字符型的数据来处理，所以，在这个例子中，它分别返回1和10，即值的位数。

在下表中的全局系统变量都将返回int类型的值。这些变量可用于存储过程和其他实现定制业务逻辑的编程对象。

变    量

说    明

@@ERROR

当前会话最后一次发生的错误代码

@@IDENTITY

当前会话最后一次生成的标识值

@@ROWCOUNT

当前会话中最后一次返回结果集的执行操作所返回的行数

@@TRANCOUNT

当前会话中活动的事务数。这是在执行相关的COMMIT TRANSACTION或者ABORT TRANSACTION语句之前嵌套的多个BEGIN TRANSACTION语句的结果

系统统计变量

下表描述了用于确定数据库系统使用信息与环境信息的管理工具：

变    量

说    明

@@CONNECTIONS

返回打开连接的次数

@@CPU_BUSY

从上次启动服务器开始，SQL Server一共工作的毫秒数

@@IDLE

从上次启动服务器开始，SQL Server一共空闲的毫秒数

@@IO_BUSY

从上次启动服务器开始，SQL Server一共处理I/0的毫秒数

@@PACK_RECEIVED

从上次启动服务器开始，SQL Server一共收到的网络数据包数

@@PACK_SENT

从上次启动服务器开始，SQL Server一共发送的网络数据包数

@@PACKET_ERRORS

从上次启动服务器开始，SQL Server一共收到的网络数据包错误数

@@TIMETICKS

每个时钟滴答有多少毫秒

@@TOTAL_ERRORS

从上次启动服务器开始，SQL Server一共收到的磁盘I/O错误数

@@TOTAL_READ

从上次启动服务器开始，SQL Server一共进行的物理磁盘读取次数

@@TOTAL_WRITE

从上次启动服务器开始，SQL Server一共进行的物理磁盘写入次数


实例说明四个排名函数--【叶子】
沧海一叶点滴心得
04-26 2157
-- =============================================-- Title：     实例说明四个排名函数的区别-- Author:    maco_wang(叶子)-- Create date: 2011-04-26-- Description:   三国版-- =============================================/*RANK() 返回结果集的分区内每行的排名。行的排名是相关行之前的排名数加一。如果两个或多个行与一个排名关联，则每
SQL Server2005杂谈（3）：四个排名函数（row_number、rank、dense_rank和ntile）的比较
一个被知识诅咒的人
02-05 1192
Normal07.8 磅02falsefalsefalseMicrosoftInternetExplorer4<!--/* Font Definitions */@font-face{font-family:宋体;panose-1:2 1 6 0 3 1 1 1 1 1;}@font-face{font-f
SQL的四大排名函数
qq_33420835的博客
09-16 678
row_number() 根据列为结果集中的行返回有序数字 select employeeID,row_number()over (order by salary asc) as Rank  from employee 查询结果会有一个Rank列，内容是从上到下1，2，3，4..... rank() 根据指定条件在结果集中返回每一行的排名 ...
四大排名函数
stone_tomcate的博客
03-09 417
一、ROW_NUMBER() Row_number() 在排名是序号 连续 不重复，即使遇到表中的两个一样的数值亦是如此 select *,row_number() OVER(order by number ) as row_num from num 数据如下： 结果如图： 注意：在使用row_number() 实现分页时需要特别注意一点，over子句中的order by 要与SQL排序记录中的order by保持一致，否则得到的序号可能不是连续的 select *,row_number()..
排名函数
潇潇雨未歇
10-13 292
 drop table t; create table t (stuid  varchar(10),                 classname varchar(50),                 score int);               insert into t  values ('A0001','cn',60); insert into t  values ('...
SQL2005 四个排名函数(row_number、rank、dense_rank和ntile)的比较
09-11
总结来说，SQL Server 2005中的四个排名函数各有特点： - `row_number`：为每一行生成一个唯一的序号，不受相同值的影响。 - `rank`：在考虑相同值的情况下分配序号，出现相同值时跳过序号。 - `dense_rank`：同样...
sql四大排名函数之ROW_NUMBER、RANK、DENSE_RANK、NTILE使用介绍
09-08
本文主要讲解SQL中的四大排名函数：ROW_NUMBER、RANK、DENSE_RANK以及NTILE，它们在数据分析和报表生成中起到关键作用。 1. ROW_NUMBER() ROW_NUMBER()函数用于为查询结果集中的每一行分配一个唯一的数字序列。这个...
SQL2005排名函数详解：ROW_NUMBER、RANK、DENSE_RANK、NTILE
"这篇文章主要介绍了在MS SQL 2005中使用的四个排序函数：ROW_NUMBER、RANK、DENSE_RANK 和 NTILE，这些函数在数据分析和查询结果排序中非常有用。通过一个具体的实例，文章解释了每个函数的用法和区别。" 在SQL ...
函数：排名函数
hyde100的专栏
01-05 3548
  ㈠排名函数⒈RANK ( ) OVER ( [ partition_by_clause > ] order_by_clause > )功能：返回结果集分区内中每行的排名(不连续)。行的排名是相关行之前的排名数加一。partition_by_cluause：分区字段。order_by_cluause：排序字段。 示例：declare @a table( 
SQL Server 2005 提供了4个行号排序函数
09-08
SQL Server 2005 提供了4个行号排序函数，合理的运用他们将可以非常方面的实现分页。
EXCEL 排名(RANK,COUNTIFS)
清山博客
07-24 7600
Criteria_range1：区域1 选择 招聘岗位这一列（选中C2，Ctrl+Shift+方向下，再按F4）。Criteria_range2：区域2 选择 笔试成绩这一列（选中D2，Ctrl+Shift+方向下，再按F4）。与第2中组内排序基本一致，只是在COUNTIFS函数中，多写入一组区域和条件即可。"确定"即可计算出本行排名，双击单元格右下角，其他列重复此操作即可。Ref 选择这一整列（Ctrl+Shift+向下箭头、再按F4）。需求描述：如有下面表格，需要按笔试成绩整体排名。
Excel表格中排名函数
weixin_51707948的博客
12-31 4303
rank函数 要求将上述表格的身高进行排名 注意：B2单元格为168数值的单元格，后面B2：B6记得要加绝对引用 设置好公式后的排名
排序函数
多一些不为什么的坚持
01-27 3109
sort函数有sort(),stable_sort()和partial_sort()。sort()函数是对给定区间的元素进行排序，但是会改变值相同的元素的相对位置。stable_sort()是对给定区间的元素进行稳定排序，如果两个元素相等，那么排序完成后两个元素的相对位置保持不变，partial_sort()是对给定区间的元素进行部分排序。默认的顺序是由小到大进行排序。 三种函数的用法如下 v为容...
【SQL】排名函数ROW_NUMBER、RANK、DENSE_RANK和NTILE讲解
Asher117的博客
06-06 1417
SQL中四大排名为：ROW_NUMBER、RANK、DENSE_RANK和NTILE。 其中用法为： SELECT ROW_NUMBER()|RANK()|DENSE_RANK()|NTILE(n) OVER( ORDER BY 列名 [DESC] ) AS NewColumn，* FROM TABLE ...... 注意： 1.你可能会注意到NTILE函数中带有参数n，这个函数用来对...
excel排名_WPS-Excel表格实用技巧——排位排名函数(RANK函数）的使用
weixin_39950010的博客
11-10 5210
平时使用excel表格处理数据时经常会用到排序功能，一般使用“升序”和“降序”工具按钮就可以完成简单的排序任务，但是当我们遇到相同数值在总体中的排名时，简单的“升序”和“降序”按钮会使相同数值出现重叠，那该怎么办呢？这就需要使用到RANK函数。RANK函数是排名函数，用于求某个数值在某一组数值中低排名。rank函数的语法格式=rank(number,ref,[order])。number表示参与排...
Excel根据分数排名次，RANK函数来帮忙
最新发布
效率办公指南
09-14 2万+
SORT(A2:A10, 1, -1) 这里，A2:A100 是你想要排序的数据范围，1 表示按照第一列（即分数列）进行排序，-1 表示降序排序（如果使用 1 则表示升序）。今天，我们将介绍几种在Excel中根据分数进行排名的方法，帮助你快速、准确地完成这项任务。如果你想要对整个数据集进行排序并排名，可以先使用SORT函数对数据进行排序，然后使用RANK函数进行排名。与RANK函数类似，RANK.EQ函数也可以用来排名，但它会为相同的分数分配相同的排名。同样，将公式向下拖动，以应用到整列。
MySQL四大排名函数
sky2019116的博客
02-02 2112
本文解释MySQL中四大排名函数的具体功能，并做了的使用示例。具体效果会根据实际数据和排序条件不同而有所差异。
窗口函数之排名函数与分析函数
铭霏的记事本
11-11 8625
窗口函数中的排名函数与分析函数实在是太好用了，尤其是row_number和lead 全局表如下： 排名函数       row_number           ROW_NUMBER() over (partition by name order by testid)       (partition by 是可选的)  其他排名函数相同  按照order排序
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值