|
|
51CTO旗下网站
|
|
移动端

ORDER BY排序太简单?那是因为你还没用过这四大排序函数!

今天就给大家介绍四个你不怎么常用排序函数,他们就是SQL Server排序中经常用到的ROW_NUMBER(),RANK(),DENSE_RANK(),NTILE()这四个好兄弟。

作者:佚名来源:数据库开发|2019-12-26 14:50

 

我们在写SQL代码时,只要有排序,首先想到的肯定是ORDER BY,以至于好多小伙伴觉得排序多简单啊。

今天就给大家介绍四个你不怎么常用排序函数,他们就是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.  
  12. SELECT * INTO Scores FROM t;  
  13. SELECT * FROM Scores 

结果如下:

1、ROW_NUMBER()

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

1.1 对学生成绩排序

示例

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

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

结果如下:

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

1.2 获取第2名的成绩信息 

  1. SELECT * FROM (  
  2. SELECT ROW_NUMBER() OVER (ORDER BY SCORE DESC) AS [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 DESC) AS [RANK],*  
  2. FROM Scores;  
  3. SELECT RANK() OVER (ORDER BY SCORE DESC) AS [RANK],*  
  4. 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 DESC) AS [RANK],*  
  3. FROM Scores;  
  4. SELECT  
  5. DENSE_RANK() OVER (ORDER BY SCORE DESC) AS [RANK],*  
  6. FROM Scores; 

结果:

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

4、NTILE()

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

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

结果:

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

【编辑推荐】

  1. 详解Oracle数据库LRU算法:LRU链、脏块与脏LRU链
  2. 除了会排序,你对ORDER BY的用法可能一无所知!
  3. 一文搞懂各种数据库SQL执行计划:MySQL、Oracle等
  4. 一文总结MySQL数据库事件--定时任务实现方式
  5. MySQL数据库小项目日志表定期删除实现方案
【责任编辑:庞桂玉 TEL:(010)68476606】

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

订阅专栏+更多

云架构师修炼手册

云架构师修炼手册

云架构师的必备技能
共3章 | Allen在路上

9人订阅学习

Devops之监控神器Prometheus

Devops之监控神器Prometheus

监控主流
共22章 | 小罗ge11

109人订阅学习

手把手玩转Elasticsearch

手把手玩转Elasticsearch

Chandler_珏瑜
共20章 | Chandler_珏瑜

79人订阅学习

读 书 +更多

解密SEO:搜索引擎优化与网站成功战略

本书是作者根据十年网站营销经验,特别是搜索引擎优化(SEO)实现营销方面的技术和实践总结。本书帮助读者建立搜索营销的概念,分析搜索营...

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊

51CTO服务号

51CTO官微