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

最新推荐文章于 2021-04-14 16:16:46 发布
最新推荐文章于 2021-04-14 16:16:46 发布
阅读量2.6k 收藏 1
点赞数
分类专栏： SQL Server 文章标签： sql insert sql server table object 文档
排名函数是SQL Server2005新加的功能。在SQL Server2005中有如下四个排名函数：

　　1.row_number

　　2.rank

　　3.dense_rank

　　4.ntile　　

一、row_number

　　row_number函数的用途是非常广泛，这个函数的功能是为查询出来的每一行记录生成一个序号。

       其中row_number列是由row_number函数生成的序号列。在使用row_number函数是要使用over子句选择对某一列进行排序，然后才能生成序号。

二、rank

rank函数生成的序号有可能不连续

三、dense_rank

dense_rank函数的功能与rank函数类似，只是在生成序号时是连续的，而rank函数生成的序号有可能不连续。

四、ntile　　

ntile函数可以对序号进行分组处理。这就相当于将查询出来的记录集放到指定长度的数组中，每一个数组元素存放一定数量的记录。ntile函数为每条记录生成的序号就是这条记录所有的数组元素的索引（从1开始）。也可以将每一个分配记录的数组元素称为“桶”。ntile函数有一个参数，用来指定桶数。


if OBJECT_ID('Test') IS NOT NULL DROP TABLE Test
go
create table Test
(
    部门 char(6),
    姓名 varchar(6),
    薪资 money
)
go
insert into Test values('市场部','张三',6000)
insert into Test values('市场部','李四',6000)
insert into Test values('市场部','王五',5000)
insert into Test values('工程部','赵柳',3400)
insert into Test values('工程部','立白',24000)
insert into Test values('工程部','雕牌',10000)
insert into Test values('策划部','海丝',2000)
insert into Test values('策划部','李波',12000)
go

--题目：找出各部门薪资最高的人

--1.常规做法：相关子查询实现

select 部门,姓名,薪资 from Test a
where 薪资=(select max(薪资) from test b where a.部门=b.部门)
/***************

=========查询结果===========

部门     姓名     薪资
------ ------ ---------------------
策划部    李波     12000.00
工程部    赵柳     24000.00
市场部    张三     6000.00

(3 行受影响)
****************/

--以上结果无法满足薪资相同的情况

------------------------------------------------------------------------

--2.常规做法：找出每个部门的最大薪资，然后子查询匹配

select test.部门,test.姓名,test.薪资 from Test,
    (select 部门,max(薪资)薪资 from test group by 部门)t 
where Test.部门=t.部门 and test.薪资=t.薪资

/*********

=========查询结果===========

部门     姓名     薪资
------ ------ ----------------
市场部    张三     6000.00
市场部    李四     6000.00
工程部    立白     24000.00
策划部    李波     12000.00

(4 行受影响)
*/
--以上结果正确
---------------------------------------------------------------------------------------------

/*
*以下的分组函数，相对Group by与 compute更好用，用得也比较多，大家可以根据实际情况，自由选择。
*下面我只是做简单介绍，有兴趣的可以看看帮助文档，或者上网查询一下。
*/
--row_number()
/*
*row_number()中的partition by就是按那个字段进行分组,并对分组后的数据进行编号
*如果没有当前字段，那么就是按排序从1开始编号。
*/

--实验
select rowid=row_number() over(order by 薪资 desc),* from Test
select rowid=row_number() over(partition by 部门 order by 薪资 desc),* from Test

/* 以上两条语句查询结果

rowid                部门     姓名     薪资
-------------------- ------ ------ ---------------------
1                    工程部    立白     24000.00
2                    策划部    李波     12000.00
3                    工程部    雕牌     10000.00
4                    市场部    张三     6000.00
5                    市场部    李四     6000.00
6                    市场部    王五     5000.00
7                    工程部    赵柳     3400.00
8                    策划部    海丝     2000.00

(8 行受影响)

rowid                部门     姓名     薪资
-------------------- ------ ------ ---------------------
1                    策划部    李波     12000.00
2                    策划部    海丝     2000.00
1                    工程部    立白     24000.00
2                    工程部    雕牌     10000.00
3                    工程部    赵柳     3400.00
1                    市场部    张三     6000.00
2                    市场部    李四     6000.00
3                    市场部    王五     5000.00

(8 行受影响)


*/

--通过以上结果我们可以通过rowid来查询，但同样无法解决薪资相同的情况

;with t as(
select rowid=row_number() over(partition by 部门 order by 薪资 desc),* from Test)
select * from t where t.rowid=1

/*结果如下：
rowid                部门     姓名     薪资
-------------------- ------ ------ ---------------------
1                    策划部    李波     12000.00
1                    工程部    立白     24000.00
1                    市场部    张三     6000.00

(3 行受影响)

*/
--以上结果也未解决同部门同薪资的情况
---------------------------------------------------------------------------
--rank()用法与row_unmber()相同

--注意下面两个查询的区别
select rankId=rank()over(order by 薪资 desc),* from Test
select rankId=rank()over(order by 部门 desc),* from Test

/* 重点看第2个结果
rankId               部门     姓名     薪资
-------------------- ------ ------ ---------------------
1                    工程部    立白     24000.00
2                    策划部    李波     12000.00
3                    工程部    雕牌     10000.00
4                    市场部    张三     6000.00
4                    市场部    李四     6000.00
6                    市场部    王五     5000.00
7                    工程部    赵柳     3400.00
8                    策划部    海丝     2000.00

(8 行受影响)

rankId               部门     姓名     薪资
-------------------- ------ ------ ---------------------
1                    市场部    张三     6000.00
1                    市场部    李四     6000.00
1                    市场部    王五     5000.00
4                    工程部    赵柳     3400.00
4                    工程部    立白     24000.00
4                    工程部    雕牌     10000.00
7                    策划部    海丝     2000.00
7                    策划部    李波     12000.00

(8 行受影响)

*/
--rank() 即是不连续编号的分组函数
select rankId=rank() over(partition by 部门 order by 薪资 desc),* from Test

/* 查询结果
rankId               部门     姓名     薪资
-------------------- ------ ------ ---------------------
1                    策划部    李波     12000.00
2                    策划部    海丝     2000.00
1                    工程部    立白     24000.00
2                    工程部    雕牌     10000.00
3                    工程部    赵柳     3400.00
1                    市场部    张三     6000.00
1                    市场部    李四     6000.00
3                    市场部    王五     5000.00

(8 行受影响)


--此处需要注意的是，如果同部门同薪资，他们的编号相同，
--比如这里两个薪资为6000的行他们的编号都是“1”，而5000的这一行是“3”而不是“2”。
*/

--通过以上结果很明确的可以知道我们的结果就出来了(能够完美解决相同问题)

;with t as(
    select rankId=rank() over(partition by 部门 order by 薪资 desc),* from Test)
select * from t where t.rankid=1

/* 查询结果
rankId               部门     姓名     薪资
-------------------- ------ ------ ---------------------
1                    策划部    李波     12000.00
1                    工程部    立白     24000.00
1                    市场部    张三     6000.00
1                    市场部    李四     6000.00

(4 行受影响)
*/

--dense_rank()与rank()相反,即他的编号是连续的，可以自己对比一下他们的区别
select drankId=dense_rank()over(order by 薪资),* from Test
select drankId=dense_rank()over(order by 部门),* from Test

/*
drankId              部门     姓名     薪资
-------------------- ------ ------ ---------------------
1                    策划部    海丝     2000.00
2                    工程部    赵柳     3400.00
3                    市场部    王五     5000.00
4                    市场部    张三     6000.00
4                    市场部    李四     6000.00
5                    工程部    雕牌     10000.00
6                    策划部    李波     12000.00
7                    工程部    立白     24000.00

(8 行受影响)

drankId              部门     姓名     薪资
-------------------- ------ ------ ---------------------
1                    策划部    海丝     2000.00
1                    策划部    李波     12000.00
2                    工程部    赵柳     3400.00
2                    工程部    立白     24000.00
2                    工程部    雕牌     10000.00
3                    市场部    张三     6000.00
3                    市场部    李四     6000.00
3                    市场部    王五     5000.00

(8 行受影响)

*/
 

;with t as(
    select drankId=dense_rank() over(partition by 部门 order by 薪资 desc),* from Test)
select * from t where drankid=1

/*
drankId              部门     姓名     薪资
-------------------- ------ ------ ---------------------
1                    策划部    李波     12000.00
1                    工程部    立白     24000.00
1                    市场部    张三     6000.00
1                    市场部    李四     6000.00

(4 行受影响)

*/

--顺便介绍一下ntile() 分组函数，平均分配
--ntile()
select tileid=ntile(6) over(order by 部门),* from Test



转自：

http://topic.csdn.net/u/20120731/10/32331a56-b060-4b01-9fe4-8f20f6e3cd18.html?seed=1803686804&r=79277841#r_79277841

http://www.cnblogs.com/xhyang110/archive/2009/10/27/1590448.html


七色鸟
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
计算机一级如何用rank函数降序,rank函数怎么用降序及排名（图文rank函数操作流程）...
weixin_31235395的博客
07-19 9865
打乱的序号？使用排序功能进行排名说到排名，常规的操作方法，是使用排序功能。比如，按出货量排序，点击出货量下的任意一个单元格，再使用升序或降序进行排列。但这样会打乱仓库号的顺序。使用函数完成排名RANK.EQ函数的第一个参数使用RANK.EQ函数可以解决这个问题。RANK.EQ有三个参数 RANK.EQ(number,ref,[order])。其中，number是指要对哪个数值进行排名计算。在这个例...
SQL2005 四个排名函数(row_number、rank、dense_rank和ntile)的比较
09-11
总结来说，SQL Server 2005中的四个排名函数各有特点： - `row_number`：为每一行生成一个唯一的序号，不受相同值的影响。 - `rank`：在考虑相同值的情况下分配序号，出现相同值时跳过序号。 - `dense_rank`：同样...
实例讲解sql server排名函数DENSE_RANK的用法
09-10
主要以一个实例讲解了sql server排名函数DENSE_RANK的用法，感兴趣的小伙伴们可以参考一下
SQL Server2005中四个排名函数
sjrhero
03-22 95
排名函数是SQL Server2005新加的功能。在SQL Server2005中有如下四个排名函数： 1. row_number 2. rank 3. dense_rank 4. ntile    下面分别介绍一下这四个排名函数的功能及用法。在介绍之前假设有一个t_table表，表结构与表中的数据如图1所示：                          图...
Hive窗口函数
过河卒子Rover的博客
02-08 3万+
Hive窗口函数
sql四大排名函数之ROW_NUMBER、RANK、DENSE_RANK、NTILE使用介绍
09-08
本文主要讲解SQL中的四大排名函数：ROW_NUMBER、RANK、DENSE_RANK以及NTILE，它们在数据分析和报表生成中起到关键作用。 1. ROW_NUMBER() ROW_NUMBER()函数用于为查询结果集中的每一行分配一个唯一的数字序列。这个...
ROW_NUMBER、RANK、DENSE_RANK 和 NTILE
03-12
MS SQL 2005 中的排名函数包括 ROW_NUMBER、RANK、DENSE_RANK 和 NTILE，这四个函数可以有效地分析数据并提供排序值。下面将详细介绍每个函数的用法和示例。 一、ROW_NUMBER 函数 ROW_NUMBER 函数返回结果集分区...
SQL, PL/SQL 之NUMBER数据类型
zunguitiancheng的专栏
11-13 1556
http://blog.csdn.net/leshami/article/details/8153826      NUMBER数据类型在Oracle中使用的较为广泛，可以存储零值，正负数，以及定长数，对于这个数据类型有个几个概念要搞清，否则容易搞混，下面给出具体描述。   1、可表示范围及存储空间     从1.0 x 10-130 到 1.0 x 10126(不包括)，如果表达式或
Hive分析函数之ntile、排名函数学习
BabyFish13
11-16 5694
1、Ntile使用 可以看成是：它把有序的数据集合平均分配到指定的数量（num）个桶中, 将桶号分配给每一行。如果不能平均分配，则优先分配较小编号的桶，并且各个桶中能放的行数最多相差1。 语法是：ntile (num)  over ([partition_clause]  order_by_clause)  as your_bucket_num 然后可以根据桶号，选取前或后 n分之几的数据。
SQL server数据库之排名函数
飘零落叶
08-31 6813
在sql server中，我们常用的排名函数有三种，分别是 row_number、rank、dense_rank在这三种排名函数中，都要与over子句连用，而下面我们就来说一下它们的用法和差异。 1.row_number row_number函数是我平时运用最多的排名函数，这个函数的功能是为查询出来的每一行记录生成一个序号(序号连续的排名)。row_number函数的用法如下 SELECT...
mysql 实现 oracle 中 rorw_number() over () 函数
xiaomifeng287的专栏
01-13 979
drop table if exists heyf_t10; create table heyf_t10 (empid int ,deptid int ,salary decimal(10,2) ); insert into heyf_t10 values (1,10,5500.00), (2,10,4500.00), (3,20,1900.00), (4,20,4800.
【转载】R语言dplyr包学习笔记（吐血整理宇宙无敌详细版）
王斯的博客
11-03 5272
出处：AI入门学习 dplyr包介绍 dplyr包主要用于数据清洗和整理，主要功能有：行选择、列选择、统计汇总、窗口函数、数据框交集等是非常高效、友好的数据处理包，学清楚了，基本上数据能随意玩弄，对的，随意玩弄，简直大大提高数据处理及分析效率。我以为，该包是数据分析必学包之一。学习过程需要大量试验，领悟其中设计的精妙之处。欢迎交流 #包安装与加载 install.packages("dplyr") library(dplyr) #调用mtcars数据&数据集介绍 data(mtcars)
sql如何设置自动编号列的SEED
知止不殆
12-12 2238
2、保持表的内容，并重置自动编号列的SEED DBCC CHECKIDENT ('table_name', RESEED, new_reseed_value) 如dbcc checkident ("bc_pos",reseed,1)即可，但如果表内有数据，则重设的值如果小于最大值可能会有问题，这时可以用 dbcc checkident("bc_pos",reseed)即可自动重设值。
R语言dplyr包：高效数据处理函数（filter、group_by、mutate、summarise）
热门推荐
关注我，你就是我的电子朋友咯！
12-13 7万+
R语言dplyr包的数据整理、分析函数用法文章连载NO.01 在日常数据处理过程中难免会遇到些难处理的，选取更适合的函数分割、筛选、合并等实在是大快人心！ 利用dplyr包中的函数更高效的数据清洗、数据分析，及为后续数据建模创造环境；本篇涉及到的函数为filter、filter_all()、filter_if()、filter_at()、mutate、group_by、select、summa...
SQL server查询变动积分和当前积分
weixin_43942765的博客
04-14 230
先看一个查询 select Id,AddTime,Points from IntellManuSchool_UserPoints where UserId=4 order by AddTime desc 现在需要添加一列为当前积分 实现Sql语句 select AddTime,Points,(select sum(Points) from IntellManuSchool_UserPoints as a where a.AddTime<=IntellManuSchool_UserPoints.Ad
SQL随机分组与随机平均分组----ntile(组数) over(partition by 字段1 order by 字段2)
肥货
12-30 1万+
今天发现一个函数，ntile 直接看代码吧 select t.classno ,t.sno,t.score ntile(5) over(partition by classno order by t.sno) as ntile from t_score t order by ntile 意思：根据 classno 分组后再进行每个相同classno的集合尽量地平均分5组。 例如：一
Oracle分析函数ntile
关注系统性能调优
11-19 2281
有这么一个需求，将课程的成绩分成四个等级，为学生打A、B、C、D的绩效。 drop table course purge; create table course (   id number,   grade number ); insert into course values(1,50); insert into course values(2,55); insert int
DENSE_RANK NTILE RANK ROW_NUMBER 请分别给出这些分析函数在sql中的语法，以及具体的实例
最新发布
06-01
好的，下面是这些分析函数在SQL中的语法和实例： 1. DENSE_RANK函数语法：DENSE_RANK() OVER (ORDER BY column_name) DENSE_RANK函数可以用来计算某一列的密集排名，即相等值的数据行将得到相同的排名，但是排名之间没有间隔。 示例： 假设我们有一个名为“customers”的表，其中包含客户名称和订单总数。我们可以使用DENSE_RANK函数来按订单总数对客户进行排名，如下所示： SELECT customer_name, order_total, DENSE_RANK() OVER (ORDER BY order_total DESC) as dense_rank FROM customers; 结果将返回客户名称、订单总数和密集排名，按订单总数降序排序。 2. NTILE函数语法：NTILE(n) OVER (ORDER BY column_name) NTILE函数可以将一列数据分成n个等份，并返回每个数据所在的组号。 示例： 假设我们有一个名为“students”的表，其中包含学生姓名和成绩。我们可以使用NTILE函数将学生按成绩分成3组，并返回每个学生所在的组号，如下所示： SELECT student_name, grade, NTILE(3) OVER (ORDER BY grade DESC) as ntile FROM students; 结果将返回学生姓名、成绩和组号，按成绩降序排序并分成3组。 3. RANK函数语法：RANK() OVER (ORDER BY column_name) RANK函数可以用来计算某一列的排名，相等值的数据行将得到相同的排名，并且排名之间会有间隔。 示例： 假设我们有一个名为“employees”的表，其中包含员工姓名和销售额。我们可以使用RANK函数来按销售额对员工进行排名，如下所示： SELECT employee_name, sales, RANK() OVER (ORDER BY sales DESC) as rank FROM employees; 结果将返回员工姓名、销售额和排名，按销售额降序排序。 4. ROW_NUMBER函数语法：ROW_NUMBER() OVER (ORDER BY column_name) ROW_NUMBER函数可以用来给每一行数据分配一个唯一的数字标识，不考虑数据是否相等。 示例： 假设我们有一个名为“orders”的表，其中包含订单号、客户名称和订单总数。我们可以使用ROW_NUMBER函数来对订单进行编号，并按订单总数降序排序，如下所示： SELECT order_number, customer_name, order_total, ROW_NUMBER() OVER (ORDER BY order_total DESC) as row_number FROM orders; 结果将返回订单号、客户名称、订单总数和行号，按订单总数降序排序并为每个订单分配一个唯一的行号。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值