SQL中ROW_NUMBER()/RANK() /DENSE_RANK() OVER函数的基本用法

最新推荐文章于 2024-03-29 10:23:20 发布
最新推荐文章于 2024-03-29 10:23:20 发布
阅读量8k 收藏 17
点赞数
分类专栏： 数据库 文章标签： sql ROW_NUMBER RANK DENSE_RANK
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/biaobiao1217/article/details/41077417
版权

一、ROW_NUMBER()的用法

 

语法：ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN)

row_number()从1开始，为每一条分组记录返回一个数字，这里的ROW_NUMBER() OVER (ORDER BY colum DESC) 是先把colum列降序，再为降序以后的每条colum记录返回一个序号。
示例： 

Row_Num    colum

     1              2200

     2              2150

     3             1780

     4             1125

Row_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组，在分组内部根据 COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的，没有重复值)

实例1：

初始化数据

create table employer (employerid int ,deptid int ,salary decimal(8,1))

insert into employer values(1,1,15000.0)

insert into employer values(2,1,10000.0)

insert into employer values(3,2,19000.0)

insert into employer values(4,2,21000.0)

insert into employer values(5,3,14500.0)

insert into employer values(6,3,10000.0)

insert into employer values(7,3,44500.0)

insert into employer values(8,4,22500.0)

insert into employer values(9,4,35500.0)

insert into employer values(10,4,35500.0)

insert into employer values(11,4,36000.0)

insert into employer values(12,4,36000.0)

 

数据显示为

employerid       deptid      salary
----------- ----------- ---------------------------------------
1                         1          15000.0

2                         1          10000.0

3                         2          19000.0

4                         2          21000.0

5                         3          14500.0

6                         3          10000.0

7                         3          44500.0

8                         4          22500.0

9                         4          35500.0

10                       4          35500.0

11                       4          36000.0

12                       4          36000.0

需求：根据部门分组，显示每个部门的工资等级

预期结果：

employerid       deptid      salary              Leve 
----------- ----------- ---------------------------------------
1                         1          15000.0               1

2                         1          10000.0               2

4                         2          21000.0               1 

3                         2          19000.0               2

7                         3          44500.0               1

5                         3          14500.0               2

6                         3          10000.0               3

11                       4          36000.0               1

12                       4          36000.0               2

9                         4          35500.0               3

10                       4          35500.0               4

8                         4          22500.0               5

SQL脚本：

SELECT *, ROW_NUMBER() OVER (PARTITION BY deptid ORDER BY salary desc) Leve FROM employeer

 

实例2：

初始化数据

create table tb_EmployerSign (SignId int ,EmployerId int ,SignDate datetime)-- 创建员工签到表

insert into tb_EmployerSign values(1,1,'2014-09-15 18:21:38.130' )

insert into tb_EmployerSign values(2,2,'2014-09-16 18:21:38.130' )

insert into tb_EmployerSign values(3,3,'2014-09-14 18:21:38.130' )

insert into tb_EmployerSign values(4,4,'2014-09-16 18:21:38.130' )

insert into tb_EmployerSign values(5,1,'2014-09-17 18:21:38.130' )

insert into tb_EmployerSign values(6,2,'2014-09-17 19:21:38.130' )

insert into tb_EmployerSign values(7,3,'2014-09-19 18:21:38.130' )

insert into tb_EmployerSign values(8,4,'2014-09-20 18:21:38.130' )

数据显示为

SignId       EmployerId              SignDate
----------- ----------- -------------------------------------------
1                      1            2014-09-15 18:21:38.130

2                      2            2014-09-16 18:21:38.130

3                      3            2014-09-14 18:21:38.130

4                      4            2014-09-16 18:21:38.130

5                      1            2014-09-17 18:21:38.130

6                      2            2014-09-17 19:21:38.130

7                      3            2014-09-19 18:21:38.130

8                      4            2014-09-20 18:21:38.130

需求：查询三天内没有签到的员工最后一次签到的信息

假如今天是2014-09-21 则预期结果：

    SignId             EmployerId             SignDate                        OutDateNumb
-------------------------------------------------------------------------------------------------------
       5                           1          2014-09-17 18:21:38.130                   4

       6                           2          2014-09-17 19:21:38.130                   4

SQL脚本：

select SignId,EmployerId,SignDate,datediff(dd,SignDate,getdate()) as OutDateNumb 

from (select *,ROW_NUMBER() over(PARTITION by EmployerId order by signId DESC) numb from EmployerSign) tb 

where tb.numb=1 and datediff(dd,SignDate,getdate())>3<span style="font-size:14px;"><strong>
</strong></span>


 二、RANK()的用法

 

语法：RANK() OVER (PARTITION BY COL1 ORDER BY COL2)

RANK()的用法和ROW_NUMBER()类似，只不过RANK()是跳跃排序，有两个第三名时接下来就是第五名（同样是在各个分组内）．

例如执行如下SQL语句之后实例1中的数据显示结果如下：

SELECT *, RANK() OVER (PARTITION BY deptid ORDER BY salary desc) Leve FROM employer


结果：

employerid       deptid      salary              Leve 
----------- ----------- ---------------------------------------
1                         1          15000.0               1

2                         1          10000.0               2

4                         2          21000.0               1

3                         2          19000.0               2

7                         3          44500.0               1

5                         3          14500.0               2

6                         3          10000.0               3

11                       4          36000.0               1

12                       4          36000.0               1

9                         4          35500.0               3

10                       4          35500.0               3

8                         4          22500.0               5

 

三、DENSE_RANK()的用法

 

语法：DENSE_RANK() OVER(PARTITION BY COL1 ORDER BY COL2)

DENSE_RANK()的用法和ROW_NUMBER()类似，只不过DENSE_RANK()是连续排序，有两个第二名时仍然跟着第三名(同样在各个分组内)。

例如执行如下SQL语句后实例1中的数据显示如下：

SELECT *, DENSE_RANK() OVER (PARTITION BY deptid ORDER BY salary desc) Leve FROM employee

结果：

employerid       deptid      salary              Leve 
----------- ----------- ---------------------------------------
1                         1          15000.0               1

2                         1          10000.0               2

4                         2          21000.0               1

3                         2          19000.0               2

7                         3          44500.0               1

5                         3          14500.0               2

6                         3          10000.0               3

11                       4          36000.0               1

12                       4          36000.0               1

9                         4          35500.0               2

10                       4          35500.0               2

8                         4          22500.0               3

 

 

freeson_csdn
关注
  • 0
    点赞
  • 踩
  • 17
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
rank() over(partition)的使用
weixin_34194379的博客
02-04 1255
     有的时候会遇到这样的问题，我们需要查询一张表，而且要按照业务排序，比如我需要如下的结果：     地区   日期    费用  产品编号   用户编号      290 201202 258 1              s1     290 201202 200 1              s5     290 201202 100 1              s100    ...
rank over partition by
weixin_30469895的博客
07-17 883
高级函数，分组排序 over: 在什么条件之上。 partition by e.deptno: 按部门编号划分（分区）。 order by e.sal desc: 按工资从高到低排序（使用rank()/dense_rank() 时，必须要带order by，否则非法） rank()/dense_rank(): 分级 遇到有相同数值“字段2”时，有下面三种处理方式 1、row_numbe...
SQLSERVER 2005的ROW_NUMBER、RANK、DENSE_RANK的用法
12-15
ROW_NUMBER()说明：返回结果集分区内行的序列号，每个分区的第一行从 1 开始。语法：ROW_NUMBER () OVER ( [ <partition> ] <order> ) 。备注：ORDER BY 子句可确定在特定分区中为行分配唯一 ROW_NUMBER 的顺序。参数：<partition> ：将 FROM 子句生成的结果集划入应用了 ROW_NUMBER 函数的分区。       <order>：确定将 ROW_NUMBER 值分配给分区中的行的顺序。返回类型：bigint 。示例：/*以下示例将
mysql-自动排序函数(dense_rank() over、rank() over、row_number())
最新发布
weixin_41367158的博客
03-29 218
需要对score进行1-N排名，需要用到rank() ovre(业务逻辑)函数,但是又因为相同分数的学生，这样在相同排名的下一位学生只需要在之前相同排名的基础上+1即可，故要用到dense_rank() over(业务逻辑)函数。rank() over()与dense_rank() over()的作用基本相同，都是对查出指定条件后的进行排名，条件相同排名相同，排名间断不连续，区别在于dense_rank() over 排名是密集连续的。在排名相同的分数后，排名数应该是下一个连续的整数。
[Mysql] RANK()函数 | ROW_NUMBER()函数 | DENSE_RANK()函数
Hudas的博客
05-05 4215
本文主要讲解Mysql有关排序类的窗口函数(RANK()函数、 ROW_NUMBER()函数、DENSE_RANK()函数)
【小5聊】sql server 分页和分组-row_number()和over()、rank()和over()的小区别
小5聊的博客
12-02 2225
在使用sql server数据库时，分页方式之一会常用到row_number结合over进行重新排序分页 如果需要根据班级分组，分别显示对应班级的排名，rank就是很好的函数
SQL中常用的窗口函数（排序函数）-row_number/rank/dense_rank/ntile
momomuabc的博客
11-17 1万+
SQL Server中的排序函数有四个：row_number()，rank()，dense_rank()及ntile()函数；
sql row_number(),rank(),row_number()的区别
热门推荐
猪猪侠的博客
09-06 2万+
第一个,row_numer(),这个排序函数的特点是相同数据,先查出的排名在前,没有重复值。像我们这里呢sal相同,先查出来的数据的rank排名优先。如下图： 第二个，rank()函数，是跳跃排序,相同数据(这里为sal列相同)排名相同,比如并列第1,则两行数据(这里为rank列)都标为1,下一位将是第3名.中间的2被直接跳过了。排名存在重复值。 第三个，dense_rank(),这个是连续排...
Sql 四大排名函数（ROW_NUMBER、RANK、DENSE_RANK、NTILE）简介
weixin_34168700的博客
01-09 1227
　　排名函数是Sql Server2005新增的功能，下面简单介绍一下他们各自的用法和区别。我们新建一张Order表并添加一些初始数据方便我们查看效果。 CREATE TABLE [dbo].[Order]( [ID] [int] IDENTITY(1,1) NOT NULL, [UserId] [int] NOT NULL, [TotalPrice]...
(16条消息) sql 四大排名函数---（ROW
jiaoqi6132的博客
07-13 1205
1.ROW_NUMBER() 定义：ROW_NUMBER()函数作用就是将select查询到的数据进行排序，每一条数据加一个序号，他不能用做于学生成绩的排名，一般多用于分页查询， 比如查询前10个 查询10-100个学生。 实例： 1.1对学生成绩排序 这里number就是每个学生的序号 根据studentScore(分数)进行desc倒序 1.2获取第二个同学的成绩信息 这里用到的思想就是 分页查询的思想 在原sql外再套一层select where t.number>=1 ...
sql: rank/row_number/count/sum/avg/max/min over的用法
SUMMERENT的博客
03-14 2533
1.1 根据score 升序排序，ranks 为排序后的序号，从1开始，依次递增，如果有两个重复的score则序号会一样（下图中的score = 80，则ranks = 4），因为两个为4的序号，所以接着跳到序号6。3.1 根据score 升序排序，ranks 为排序后的序号，从1开始，依次递增，如果有两个重复的score则序号会一样（下图中的score = 80，则ranks = 4），接着序号为5。根据class_name分区，然后根据score排序，求每个班级学生成绩score最低分。
sql四大排名函数之ROW_NUMBER、RANK、DENSE_RANK、NTILE使用介绍
09-08
主要介绍了sql四大排名函数之ROW_NUMBER、RANK、DENSE_RANK、NTILE使用，文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面随着小编来一起学习学习吧
Oracle开发之分析函数(Rank, Dense_rank, row_number)
09-10
本文主要是对Oracle分析函数Rank, Dense_rank, row_number的使用法，通过这些函数，我们可以对数据进行排序和分组，需要的朋友可以参考下。
SQL中的排序函数【row_number() 、rank() 、dense_rank() 】
qq_39236157的博客
04-20 1万+
一、SQL中有三种排序函数 1、row_number() over(order by 列名) 2、rank() over(order by 列名) 3、dense_rank() over(order by 列名) row_number():不考虑数据的重复性，按照顺序一次打上标号 如：1 2 3 4 rank():是跳跃排序 如：1 2 2 4,会跳过3 dense_rank():是连续排序，考虑数据的重复性 如：1 2 2 3 序号连续 二、示例 首先展示班级表 示例1：row_number():对班级
sql 四大排名函数---（ROW_NUMBER、RANK、DENSE_RANK、NTILE）简介
茹茹的博客
05-28 1080
1.ROW_NUMBER() 定义：ROW_NUMBER()函数作用就是将select查询到的数据进行排序，每一条数据加一个序号，他不能用做于学生成绩的排名，一般多用于分页查询， 比如查询前10个 查询10-100个学生。 实例： 1.1对学生成绩排序 这里number就是每个学生的序号 根据studentScore(分数)进行desc倒序 1.2获取第二个同学的成绩信息 这里用到的思想就是 分页查询的思想 在原sql外再套一层select where t.number>=1 and
SQL开窗函数row_number()、rank()、DENSE_RANK()区别和联系
feiyuxc的博客
09-08 1856
SQL hive mysql row_number() rank() dense_rank() 开窗函数
SQL排列函数（row_number/rank)
kilolo_w的博客
12-13 554
4种排序函数 示例用法 NTILE详细说明 高级用法 partition by http://blog.csdn.net/zhouwubin123/article/details/7067105?locationNum=5&fps=1
SQL排序函数基础详解 row_number()/rank()/dense_rank() over(partition by)
Ego_Bai的博客
08-10 8209
为了方便学习和测试，所有的例子都是在Oracle自带用户Scott下建立的。 我用了mysql的环境,借助以下语句进行建表和插入数据,以备后续练习使用 create table EMP --创建表EMP ( empno int(4) not null, ename VARCHAR(10), job VARCHAR(9), mgr ...
Hive sql rank dense_rank row_number
06-07
在 Hive SQL 中，可以使用窗口函数（Window Function）来实现类似于 rank、dense_rank 和 row_number 这样的排名函数。 1. RANK RANK 函数用于计算排名，返回每个分组中按照指定的列排序后的排名。如果有多条记录...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值