聊聊 SQL 中的排名问题

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

[[434024]]

本文转载自微信公众号「SQL数据库开发」,作者丶平凡世界。转载本文请联系SQL数据库开发公众号。

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

我们先创建一个测试数据表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数据库开发
相关推荐

2023-06-05 08:36:04

SQL函数RANK()

2023-10-26 08:38:43

SQL排名平分分区

2021-04-09 10:37:40

SQL Server数据库排名

2021-08-31 07:54:24

SQLDblink查询

2023-08-29 09:46:12

SQLCTE递归

2023-07-28 09:54:14

SQL数据Excel

2022-08-03 08:11:58

数据测试同类型

2021-09-03 06:46:34

SQL分组集功能

2021-08-16 08:12:04

SQLMerge用法

2022-10-17 08:22:28

2023-05-29 09:07:10

SQLpageSize主键

2021-02-03 15:12:08

java内存溢出

2010-09-06 17:46:48

SQL函数

2021-04-25 09:42:40

SQL递归SQL Server

2022-07-27 08:31:28

SQL开发控制

2021-11-19 08:12:45

SQL重叠数据

2023-11-09 11:56:28

MySQL死锁

2023-02-01 13:22:00

数据库表连接SQL

2009-05-26 10:21:07

2024-02-21 08:19:54

点赞
收藏

51CTO技术栈公众号