|
|
|
|
公众号矩阵

聊聊 SQL 中的排名问题

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

作者: 丶平凡世界 来源:SQL数据库开发|2021-11-09 06:55

本文转载自微信公众号「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函数里的参数进行平分分区。

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

【编辑推荐】

  1. 鸿蒙官方战略合作共建——HarmonyOS技术社区
  2. k8s包管理工具Helm-新版
  3. Windows 11 新工具,轻轻松松帮你安装任何安卓 APK 应用
  4. 值得了解的高效K8S命令行管理工具
  5. Vue 发布新版脚手架工具,300 行代码轻盈新生!
  6. Python 中很好用的命令行参数解析工具
【责任编辑:武晓燕 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

订阅专栏+更多

带你轻松入门 RabbitMQ

带你轻松入门 RabbitMQ

轻松入门RabbitMQ
共4章 | loong576

53人订阅学习

数据湖与数据仓库的分析实践攻略

数据湖与数据仓库的分析实践攻略

助力现代化数据管理:数据湖与数据仓库的分析实践攻略
共3章 | 创世达人

14人订阅学习

云原生架构实践

云原生架构实践

新技术引领移动互联网进入急速赛道
共3章 | KaliArch

42人订阅学习

视频课程+更多

HTML5CSS3JAVASCRIPT三合一教程实战

HTML5CSS3JAVASCRIPT三合一教程实战

讲师:张晨光28623人学习过

数据结构与算法分析

数据结构与算法分析

讲师:sunnyDLL8544人学习过

Java代码审计系列课程

Java代码审计系列课程

讲师:Hack_Man2063人学习过

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊

51CTO服务号

51CTO官微