详解SQL中的排名问题

数据库 SQL Server
今天给大家介绍一下SQL Server排名中经常用到的ROW_NUMBER(),RANK(),DENSE_RANK(),NTILE()这四个好兄弟。

[[392081]]

我们先创建一个测试数据表Scores

 

  1. WITH t AS 
  2. (SELECT 1 StuID,70 Score 
  3. UNION ALL 
  4. SELECT 2,85 
  5. UNION ALL 
  6. SELECT 3,85 
  7. UNION ALL 
  8. SELECT 4,80 
  9. UNION ALL 
  10. SELECT 5,74 
  11. SELECT * INTO Scores FROM t; 
  12. SELECT * FROM Scores 

 

结果如下:

 

1、ROW_NUMBER()

定义:ROW_NUMBER()函数作用就是将SELECT查询到的数据进行排序,每一条数据加一个序号,他不能用做于学生成绩的排名,一般多用于分页查询,比如查询前10个 查询10-100个学生。

1.1 对学生成绩排序

示例

 

  1. SELECT 
  2. ROW_NUMBER() OVER (ORDER BY SCORE DESCAS [RANK],* 
  3. FROM Scores; 

 

(提示:可以左右滑动代码)

结果如下:

 

这里RANK就是每个学生的排名后的次序, 根据Score进行DESC倒序

1.2 获取第2名的成绩信息

 

  1. SELECT * FROM ( 
  2. SELECT ROW_NUMBER() OVER (ORDER BY SCORE DESCAS [RANK],* 
  3. FROM Scores 
  4. ) t WHERE t.RANK=2; 

 

结果:

 

这里用到的思想就是 分页查询的思想 在原sql外再套一层SELECT

WHERE t.RANK>=1 AND t.RANK<=3 是不是就是获取前三名学生的成绩信息了。

2、RANK()

定义:RANK()函数,顾名思义排名函数,可以对某一个字段进行排名,这里和ROW_NUMBER()有什么不一样呢?ROW_NUMBER()是排序,当存在相同成绩的学生时,ROW_NUMBER()会依次进行排序,他们序号不相同,而Rank()则不一样。如果出现相同的,他们的排名是一样的。下面看例子:

示例

 

  1. SELECT ROW_NUMBER() OVER (ORDER BY SCORE DESCAS [RANK],* 
  2. FROM Scores; 
  3.  
  4. SELECT RANK() OVER (ORDER BY SCORE DESCAS [RANK],* 
  5. FROM Scores; 

 

结果:

 

上面是ROW_NUMBER()函数的结果,下面是RANK()函数的结果。

当出现两个学生成绩相同是里面出现变化。RANK()是1-1-3-4-5,而ROW_NUMBER()则还是1-2-3-4-5,这就是RANK()和ROW_NUMBER()的区别了

3、DENSE_RANK()

定义:DENSE_RANK()函数也是排名函数,和RANK()功能相似,也是对字段进行排名,那它和RANK()到底有什么不同那?特别是对于有成绩相同的情况,DENSE_RANK()排名是连续的,RANK()是跳跃的排名,一般情况下用的排名函数就是RANK() 我们看例子:

示例

 

  1. SELECT 
  2. RANK() OVER (ORDER BY SCORE DESCAS [RANK],* 
  3. FROM Scores; 
  4.  
  5. SELECT 
  6. DENSE_RANK() OVER (ORDER BY SCORE DESCAS [RANK],* 
  7. FROM Scores; 

 

结果:

 

上面是RANK()的结果,下面是DENSE_RANK()的结果

4、NTILE()

定义:NTILE()函数是将有序分区中的行分发到指定数目的组中,各个组有编号,编号从1开始,就像我们说的'分区'一样 ,分为几个区,一个区会有多少个。

 

  1. SELECT NTILE(1) OVER (ORDER BY SCORE DESCAS [RANK],* FROM Scores; 
  2.  
  3. SELECT NTILE(2) OVER (ORDER BY SCORE DESCAS [RANK],* FROM Scores; 
  4.  
  5. SELECT NTILE(3) OVER (ORDER BY SCORE DESCAS [RANK],* FROM Scores; 

 

结果:

 

就是将查询出来的记录根据NTILE函数里的参数进行平分分区。

这几兄弟就介绍完了,有空再给大家介绍分组排名的问题。

责任编辑:华轩 来源: SQL数据库开发
相关推荐

2021-11-09 06:55:03

SQLServer排序

2021-04-25 09:42:40

SQL递归SQL Server

2023-06-05 08:36:04

SQL函数RANK()

2023-10-26 08:38:43

SQL排名平分分区

2021-11-03 16:00:40

SQL流量序号

2021-04-06 11:50:30

SQL数据统计

2009-04-02 09:46:19

排名函数排序SQL 2005

2010-05-12 11:14:25

MySQL SQL优化

2010-08-05 09:07:24

2021-02-03 09:21:59

SQL机器学习ML

2010-09-06 14:58:42

SQL函数

2010-09-09 11:12:49

SQL函数DATALENGTH

2010-09-07 11:53:00

SQL语句

2010-09-06 17:46:48

SQL函数

2010-09-08 17:15:45

SQL循环结构

2010-09-08 14:03:41

SQL函数CONVERT

2021-11-19 08:12:45

SQL重叠数据

2013-12-09 15:21:28

ASOApp Store

2010-10-14 09:32:52

SQL Server

2017-03-01 14:16:20

点赞
收藏

51CTO技术栈公众号