sql排名函数--四个
1 row_number
2 rank
3 dense_rank
4 ntile
例子如下：
select * into #MyTablefrom(select ‘语文‘ as 课程,70 as 成绩 union all select ‘数学‘ ,80 union all select ‘数学‘ ,80 union all select ‘英语‘ ,90 union all select ‘英语‘ ,100 union all select ‘英语‘ ,70) as tb
课程 成绩---- -----------语文 70数学 80数学 80英语 90英语 100英语 70
select 成绩,row_number()over( order by 成绩 desc) as row_number,rank() over( order by 成绩 desc) as rank,dense_rank()over( order by 成绩 desc) as dense_rank,ntile(4) over( order by 成绩 desc) as ntilefrom #MyTable
结果如下
成绩 row_number rank dense_rank ntile----------- -------------------- -------------------- -------------------- --------------------100 1 1 1 190 2 2 2 180 3 3 3 280 4 3 3 270 5 5 4 370 6 5 4 4
分析：
1 row_number 不论函数的值是什么都一直排序。
2 rank 对于相同的值会并列排名，然后跳过须后继续排名。
3 dense_rank 对于相同的值会并列排名，然后会按序号继续排名，不会跳过序号排名。
4 ntile 将数据分成若干块，主要用于分析 ？
当然还可以在 row_number()over()中加入partition by 在小组中分别排名：
select 课程,成绩,row_number()over( partition by 课程 order by 成绩 desc) as row_number,rank() over( partition by 课程 order by 成绩 desc) as rank,dense_rank()over( partition by 课程 order by 成绩 desc) as dense_rank,ntile(4) over( partition by 课程 order by 成绩 desc) as ntilefrom #MyTable
课程 成绩 row_number rank dense_rank ntile---- ----------- -------------------- -------------------- -------------------- --------------------数学 80 1 1 1 1数学 80 2 1 1 2英语 100 1 1 1 1英语 90 2 2 2 2英语 70 3 3 3 3语文 70 1 1 1 1
sql排名函数--四个
标签：排序 sql int 跳过 tab 数学 union 英语 strong
小编还为您整理了以下内容，可能对您也有帮助：
rank相反的函数
排名函数是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函数有一个参数，用来指定桶数。
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 行受影响)
*/
select drankId=dense_rank() over(partition by 部门 order by 薪资 desc),* from Test
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(3) over(order by 薪资 desc),* from Test
1工程部立白24000.00
1策划部李波12000.00
1工程部雕牌10000.00
2市场部张三6000.00
2市场部李四6000.00
2市场部王五5000.00
3工程部赵柳3400.00
3策划部海丝2000.00
登录后复制

SQL中几个常用的排序函数
排序函数是做什么的？
排序函数的作用是基于一个结果集返回一个排序值。排序值就是一个数字，这个数字是典型的以1开始且自增长为1的行值。由ranking函数决定排序值可以使唯一的对于当前结果集，或者某些行数据有相同的排序值。在接下来我将研究不同的排序函数以及如何使用这些函数。
使用RANK函数的例子 RANK函数每个分区的排序都是从1开始。“partition”是一组有相同指定分区列值的数据行的集合。如果一个分区中有相同排序列的值（这个列指定在ORDER BY后面），然后相同排序列值的行将会分配给相同的排序值。有点绕口，为了更好的理解，如何使用，让我们看下下面的语法:
RANK()OVER([PARTITIONBY<partition_column>]ORDERBY<order_by_column>)这里有几个参数:
<partition_column>: 指定一个或者多个列名作为分区数据
<order by column>: 确定一个或者多个列然后用来对每个分区的输出数据进行排序
注意:PARTITION BY子句是一个可选项。如是不使用，数据将按照一个分区对所有数据进行排序。如果指定了PARTITION BY子句，则每个分区的数据集都各自进行从1开始的排序。
现在对RANK函数的语法和如何工作有了一定的理解，下面运行一对该函数的例子。需要说明一下我的例子的运行环境都是AdventureWorks2012数据库，可以从网络上下载这里给出一个下载地址http://msftdbprodsamples.codeplex.com/releases/view/93587。
下面是第一个使用RANK函数的例子:
1234567USE AdventureWorks2012;GOSELECTPostalCode, StateProvinceID,RANK() OVER(ORDERBYPostalCodeASC)ASRankingValueFROMPerson.AddressWHEREStateProvinceIDIN(23,46);
Code1: 只有RANK函数不分区
运行代码后，结果集如下：
PostalCodeStateProvinceIDRankingValue
--------------------------------------------------
03064461
03064461
03106463
03276464
03865465
83301236
83402237
83501238
83702239
838642310如上所示，按照RANK函数使结果集按照列RankingValue进行了排序。在例子中排序是基于列PostalCode。每一个唯一的PostalCode得到一个不同的排序值。这里PostalCode为03054 有两行数据，它们的排序值都是1,因为有两个1，所以排序2就被跳过。其余的排序继续往下依次进行。
由于RANK函数的分区子句没有使用，那么整个结果集被当做一个单一的分区。如果我打算按照的StateProvinceID进行分区，然后进行排序我可以做按照如下的例子来执行：
12345678USE AdventureWorks2012;GOSELECTPostalCode, StateProvinceID,RANK() OVER(PARTITIONBYStateProvinceIDORDERBYPostalCodeASC)ASRankingValueFROMPerson.AddressWHEREStateProvinceIDIN(23,46);
Code 2: 使用分区子句
运行代码后的结果集：
PostalCodeStateProvinceIDRankingValue
--------------------------------------------------
83301231
83402232
83501233
83702234
83864235
03064461
03064461
03106463
03276464
03865465
在输出结果中分为了两个分区，一个分区是StateProvinceID是23的，而另一个是包含StateProvinceID值为46的、注意每个分区都是从1开始进行排序的。
使用DENSE_RANK函数
当运行RANK函数时，由于有一个相同的PostalCode，输出结果会跳过一个排序值2，通过使用DENSE_RANK函数我能生成一个不省略改相同排序值的一个排序。该函数语法如下:
DENSE_RANK()OVER([PARTIIONBY<partition_column>]ORDERBY<order_by_column>)
语法中唯一的不同就是函数名称的改变。让我们运行下面的代码来研究下函数：
12345678USE AdventureWorks2012;GOSELECTPostalCode, StateProvinceID,DENSE_RANK() OVER(PARTITIONBYStateProvinceIDORDERBYPostalCodeASC)ASRankingValueFROMPerson.AddressWHEREStateProvinceIDIN(23,46);
Code3: 使用 DENSE_RANK
结果集如下：
PostalCodeStateProvinceIDRankingValue
--------------------------------------------------
83301231
83402232
83501233
83702234
83864235
03064461
03064461
03106462
03276463
03865464
根据结果集，可以看到PostalCode03064 有相同的排序值，但是下一个PostalCode的排序值为2而不是3了。与RANK函数的不同就是当有重复排序值时它能保证了排序序列中没有省略排序。
使用NTILE 函数该函数将数据集合划分为不同的组。得到组的数量是根据指定的一个整数来确定的。下面就是NTILE 函数的语法:
NTILE(integer_expression)OVER([PARTIIONBY<partition_column>]ORDERBY<order_by_column>)Where:
<integer_expression>: 确定创建不同组的数量
<partition_column>:确定一个或者多个列用来进行分区数据
<order by column>: 确定一个或者多个列然后用来对每个分区的输出数据进行排序
为了更好地理解，让我们回顾几个不同的例子。运行下面代码：
1234567USE AdventureWorks2012;GOSELECTPostalCode, StateProvinceID,NTILE(2) OVER(ORDERBYPostalCodeASC)ASNTileValueFROMPerson.AddressWHEREStateProvinceIDIN(23,46);
Code4: 使用NTILE 函数查询
运行结果如下：
PostalCodeStateProvinceIDNTileValue
--------------------------------------------------
03064461
03064461
03106461
03276461
03865461
83301232
83402232
83501232
83702232
83864232
通过观察结果集，能很容易发现有两个不同的NTileValue的列值，1和2。两个不同的NTileValue值被创建是因为这里我查询语句中指定了“NTILE(2)” 。这个括号内的值就是整数表达式，作用就是指定创建的组的数量。当看到结果集中有10行数据，前五行NTileValue为1,后五行为2。不出所料整个结果集被平均分成了两组。
如果不能被平均分配到不同个组的时候，比如参数导致有不能被整除的时候。当发生这种情况是那么将不能被整除的行按序放到每一个组内，知道所有的剩余行都被分配完毕。如下所示：
12345678USE AdventureWorks2012;GODECLARE@Integer_Expressionint= 4;SELECTPostalCode, StateProvinceID,NTILE(@Integer_Expression) OVER(ORDERBYPostalCodeASC)ASNTileValueFROMPerson.AddressWHEREStateProvinceIDIN(46,23);
Code 5: NTile 查询不能平均分配结果集
运行代码如下：
PostalCodeStateProvinceIDNTileValue
--------------------------------------------------
03064461
03064461
03106461
03276462
03865462
83301232
83402233
83501233
83702234
83864234
这里直奔主题，10个结果行，参数为4需要分成4组，那么10除以4 余数为2。这意味着前两组会多一行比后两组。如上所示，在这个输出结果中1和2组都有3行，然后NTileValue为3和4的组只有两行。
跟RANK函数一样，我们也能使用partition 分区子句来创建分区下的NTILE 函数。当引入PARTITION BY 子句时，每个分区内部都从1开始进行NTILE排序。下面展示一下运行代码：
123456789USE AdventureWorks2012;GODECLARE@Integer_Expressionint= 3;SELECTPostalCode, StateProvinceID,NTILE(@Integer_Expression) OVER(PARTITIONBYStateProvinceIDORDERBYPostalCodeASC)ASNTileValueFROMPerson.AddressWHEREStateProvinceIDIN(46,23);
Code 6: 使用分区子句后，使用NTile 查询不平均分组
运行代码如下：
PostalCodeStateProvinceIDNTileValue
--------------------------------------------------
83301231
83402231
83501232
83702232
83864233
03064461
03064461
03106462
03276462
03865463
通过结果集可以看到加入分区子句后对NTILE函数的影响。如果观察输出的NTileValue列值，可以发现排序从StateProvinceID 为46开始重新从1开始。这就是加入“PARTITION BY StateProvinceID”子句的作用，先分区在分组排序。
使用 ROW_NUMBER 函数 当打算为输出的行生成一个行号时，行号顺序地自增长，步长为1.为了完成目标我们需要使用ROW_NUMBER 函数。
下面是使用ROW_NUMBER 的例子:
ROW_NUMBER()OVER([PARTIIONBY<partition_expressions>]ORDERBY<order_by_column>)
代码如下：
1234567USE AdventureWorks2012;GOSELECTPostalCode, StateProvinceID,ROW_NUMBER() OVER(ORDERBYPostalCodeASC)ASRowNumberFROMPerson.AddressWHEREStateProvinceIDIN(46,23);
Code 7: 使用ROW_NUMBER 函数
运行结果如下：
PostalCodeStateProvinceIDRowNumber
--------------------------------------------------
03064461
03064462
03106463
03276464
03865465
83301236
83402237
83501238
83702239
838642310
如果想对输出的PostalCode进行排序，但是你打算先按照StateProvinceID进行分组，再排序。为了实现上述要求，我加入PARTITION BY子句，代码如下：
12345678USE AdventureWorks2012;GOSELECTPostalCode, StateProvinceID,ROW_NUMBER() OVER(PARTITIONBYStateProvinceIDORDERBYPostalCodeASC)ASRowNumberFROMPerson.AddressWHEREStateProvinceIDIN(46,23);
Code 8: 使用PARTITION BY 子句和ROW_NUMBER 函数查询
运行结果如下：
PostalCodeStateProvinceIDRowNumber
--------------------------------------------------
83301231
83402232
83501233
83702234
83864235
03064461
03064462
03106463
03276464正如你看到的结果，通过添加分区子句，行数列RowNumber每个不同的StateProvinceID值都会从1重新开始排序。
SQL中几个常用的排序函数
标签：sql
SQL中的窗口函数总结
over() 窗口函数
语法结构：
partition by : 作用与group by 类似，实现分组功能
常与over一起使用的分析函数：
聚合函数：
avg()，sum()，max()，mean()
排名函数：
row_number() 产生不会重复的自增编号 （1，2，3，4，5，6.......)
rank()按照值排序的时候产生一个自增编号，值相等时会重复,有空位(eg:1,2,2,4,5,6,6,8...)
dense_rank()按照值排序的时候产生一个自增编号，值相等时会重复，无空位(1,2,2,2,3...)
其他函数：
lag()，lead()，ntile()
实例：
1.对所有员工的当前(to_date='9999-01-01')薪水按照salary进行按照1-N的排名，相同salary并列且按照emp_no升序排列
SQL中的窗口函数总结
over() 窗口函数
语法结构：
partition by : 作用与group by 类似，实现分组功能
常与over一起使用的分析函数：
聚合函数：
avg()，sum()，max()，mean()
排名函数：
row_number() 产生不会重复的自增编号 （1，2，3，4，5，6.......)
rank()按照值排序的时候产生一个自增编号，值相等时会重复,有空位(eg:1,2,2,4,5,6,6,8...)
dense_rank()按照值排序的时候产生一个自增编号，值相等时会重复，无空位(1,2,2,2,3...)
其他函数：
lag()，lead()，ntile()
实例：
1.对所有员工的当前(to_date='9999-01-01')薪水按照salary进行按照1-N的排名，相同salary并列且按照emp_no升序排列
如何让SQL server中的一列自动排名,然后把名次插进原表中。
第一步：
函数语法介绍：
=rank（number，ref,[order]）注逗号分隔。
number：用来确定排序的本体；例 =rank（A1，ref，[order]）
ref：为排序的范围；例 =rank（A1，A1：A100，[order]）
[order]:为排序的规则，“0”降序；“1”升序；
例=rank（A1，A1：A100,0）
第二步：
用光标选中单元格;
输入rank函数；
使用函数时有格式 就是先输入等号例 =rank（A2，A2：A10，0）
这里输入=rank(B2,B$2:B$51,0);
Enter
就可以得到排序了追问你这是Excel的吧？
如何让SQL server中的一列自动排名,然后把名次插进原表中。
第一步：
函数语法介绍：
=rank（number，ref,[order]）注逗号分隔。
number：用来确定排序的本体；例 =rank（A1，ref，[order]）
ref：为排序的范围；例 =rank（A1，A1：A100，[order]）
[order]:为排序的规则，“0”降序；“1”升序；
例=rank（A1，A1：A100,0）
第二步：
用光标选中单元格;
输入rank函数；
使用函数时有格式 就是先输入等号例 =rank（A2，A2：A10，0）
这里输入=rank(B2,B$2:B$51,0);
Enter
就可以得到排序了追问你这是Excel的吧？
使用rank函数对成绩进行排名
使用rank函数对成绩进行排名的方法如下：
电脑：华硕天选2
系统：Windows11
1、选中单元格B2，在开始界面中点击Σ旁的倒三角图标，点击其他函数。
2、然后在插入函数界面中输入排名，点击转到，然后选择函数rank点击确定。
3、在函数参数界面的number中输入A2，在Ref中输入A1：A10，然后点击确定就可以了。
rank函数
rank是Tsql函数，rank函数返回结果集分区内指定字段的值的排名，指定字段的值的排名是相关行之前的排名加一。partition by clause将from子句生成的结果，集划分为应用到rank函数的分区。在实际应用中，我们往往需要求某一列的数值的排名情况，例如我们求A1到A5单元格内的数据的各自排名情况，可以使用单元格引用的方法来排名。
函数名后面的参数中number为需要求排名的那个数值或者单元格名称（单元格内必须为数字），ref为排名的参照数值区域，order的为0和1，默认不用输入，得到的就是从大到小的排名，若是想求倒数第几，order的值请使用1。
以上内容参考：百度百科—rank
SQL函数:查询薪金处于第四位的雇员的姓名、部门名称、工作和薪金
用集合查询可以实现
第四名就是在前四名而不在前三名
select TOP 4 *
FROM EMP,DEPT
where 两个表连接
order by SAL desc
EXCEPT
select TOP 3 *
FROM EMP,DEPT
where 两个表连接
order by SAL desc