mysql8.0窗口函数:rank,dense_rank,row_number 使用上的区别

简介:

本节主要介绍排序类 rank,dense_rank,row_number使用上的区别

*RANK:跳跃排序
DENSE_RANK:连续排序
row_number:没有重复值的排序[记录相等也是不重复的]可以进行分页使用。
*
案例:

root@my3308.sock-8.0.11>[test]>select * from window_test;
+------+--------+---------+-------+
| id   | name   | subject | score |
+------+--------+---------+-------+
|    1 | 小黄   | 数学    |   100 |
|    2 | 小黄   | 语文    |    90 |
|    3 | 小黄   | 英语    |    80 |
|    4 | 小黄   | 物理    |   100 |
|    5 | 小黄   | 化学    |    99 |
|    6 | 小红   | 数学    |    90 |
|    7 | 小红   | 语文    |   100 |
|    8 | 小红   | 英语    |    80 |
|    9 | 小红   | 物理    |    90 |
|   10 | 小红   | 化学    |    70 |
|   11 | 小绿   | 数学    |    90 |
|   12 | 小绿   | 语文    |    92 |
|   13 | 小绿   | 英语    |    93 |
|   14 | 小绿   | 物理    |    94 |
|   15 | 小绿   | 化学    |    95 |
|   16 | 小青   | 数学    |    30 |
|   17 | 小青   | 语文    |   100 |
|   17 | 小青   | 英语    |    70 |
|   17 | 小青   | 物理    |    88 |
|   17 | 小青   | 化学    |    96 |
+------+--------+---------+-------+
20 rows in set (0.00 sec)

rank :跳跃式的排序:

image

dense_rank:连续排序

image

row_number:没有重复值的排序[记录相等也是不重复的]可以进行分页使用

root@my3308.sock-8.0.11>[test]>select *,row_number()  over(partition by subject order by score desc) as 'rank' from window_test;
+------+--------+---------+-------+------+
| id   | name   | subject | score | rank |
+------+--------+---------+-------+------+
|    5 | 小黄   | 化学    |    99 |    1 |
|   17 | 小青   | 化学    |    96 |    2 |
|   15 | 小绿   | 化学    |    95 |    3 |
|   10 | 小红   | 化学    |    70 |    4 |
|    1 | 小黄   | 数学    |   100 |    1 |
|    6 | 小红   | 数学    |    90 |    2 |
|   11 | 小绿   | 数学    |    90 |    3 |
|   16 | 小青   | 数学    |    30 |    4 |
|    4 | 小黄   | 物理    |   100 |    1 |
|   14 | 小绿   | 物理    |    94 |    2 |
|    9 | 小红   | 物理    |    90 |    3 |
|   17 | 小青   | 物理    |    88 |    4 |
|   13 | 小绿   | 英语    |    93 |    1 |
|    3 | 小黄   | 英语    |    80 |    2 |
|    8 | 小红   | 英语    |    80 |    3 |
|   17 | 小青   | 英语    |    70 |    4 |
|    7 | 小红   | 语文    |   100 |    1 |
|   17 | 小青   | 语文    |   100 |    2 |
|   12 | 小绿   | 语文    |    92 |    3 |
|    2 | 小黄   | 语文    |    90 |    4 |
+------+--------+---------+-------+------+
20 rows in set (0.00 sec)

--rank:数学成绩90 排第几名:

root@my3308.sock-8.0.11>[test]>select * from window_test where subject='数学' order by score desc;
+------+--------+---------+-------+
| id   | name   | subject | score |
+------+--------+---------+-------+
|    1 | 小黄   | 数学    |   100 |
|    6 | 小红   | 数学    |    90 |
|   11 | 小绿   | 数学    |    90 |
|   16 | 小青   | 数学    |    30 |
+------+--------+---------+-------+
4 rows in set (0.00 sec)

root@my3308.sock-8.0.11>[test]>select * from (
select *,rank() over(partition by subject order by score desc
) as 'rank' from window_test) x 
where  x.subject='数学' and   x.score=90;
+------+--------+---------+-------+------+
| id   | name   | subject | score | rank |
+------+--------+---------+-------+------+
|    6 | 小红   | 数学    |    90 |    2 |
|   11 | 小绿   | 数学    |    90 |    2 |
+------+--------+---------+-------+------+
2 rows in set (0.00 sec)

--dense_rank:查询每门课的前2名:

root@my3308.sock-8.0.11>[test]>select name,subject,score from (select *,dense_rank()  over(partition by subject order by score desc) as 'rank' from window_test) x where x.rank<=2;
+--------+---------+-------+
| name   | subject | score |
+--------+---------+-------+
| 小黄   | 化学    |    99 |
| 小青   | 化学    |    96 |
| 小黄   | 数学    |   100 |
| 小红   | 数学    |    90 |
| 小绿   | 数学    |    90 |
| 小黄   | 物理    |   100 |
| 小绿   | 物理    |    94 |
| 小绿   | 英语    |    93 |
| 小黄   | 英语    |    80 |
| 小红   | 英语    |    80 |
| 小红   | 语文    |   100 |
| 小青   | 语文    |   100 |
| 小绿   | 语文    |    92 |
+--------+---------+-------+
13 rows in set (0.00 sec)
相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。 &nbsp; 相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/mysql&nbsp;
目录
相关文章
|
存储 SQL 关系型数据库
mysql 的ReLog和BinLog区别
MySQL中的重做日志和二进制日志是确保数据库稳定性和可靠性的关键组件。重做日志主要用于事务的持久性和原子性,通过记录数据页的物理修改信息来恢复未提交的事务;而二进制日志记录SQL语句的逻辑变化,支持数据复制、恢复和审计。两者在写入时机、存储方式及配置参数等方面存在显著差异。
509 6
|
存储 关系型数据库 MySQL
MySQL中的int(10)、char(10)与varchar(10)的类型和区别
在选择正确的数据类型时,需要仔细考虑每列的数据特点及应用程序的使用情况。合理的数据类型选择可以优化存储空间的使用,提高查询速度和数据库的整体性能。
912 14
|
存储 缓存 关系型数据库
【MySQL进阶篇】存储引擎(MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案)
MySQL的存储引擎是其核心组件之一,负责数据的存储、索引和检索。不同的存储引擎具有不同的功能和特性,可以根据业务需求 选择合适的引擎。本文详细介绍了MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案。
2753 57
【MySQL进阶篇】存储引擎(MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案)
|
存储 关系型数据库 MySQL
MySQL数据库中的 char 与 varchar的区别是什么
MySQL中的char和varchar均用于存储字符串,但有显著区别。char为定长类型,固定长度,存储空间始终为设定值,适合长度固定的数据如手机号。varchar为变长类型,仅占用实际数据所需空间,适合长度不固定的内容如用户名。二者在性能与空间利用上各有优劣,应根据实际场景合理选择。
748 0
|
SQL Oracle 关系型数据库
MySQL 和 Oracle 的区别?
本文对比了Oracle和MySQL数据库的多个方面。Oracle适用于大型数据库,支持高并发和大访问量,市场占有率为40%,安装占用空间较大,约3G;而MySQL适合中小型应用,是开源免费的,安装仅需152M。两者在主键生成、字符串处理、SQL语句、事务处理等方面存在差异。Oracle功能更为强大,尤其在企业级应用中表现突出,而MySQL则以简单易用见长。
1743 7
MySQL 和 Oracle 的区别?
|
SQL 关系型数据库 MySQL
MySQL8 窗口函数
MySQL 8 引入了窗口函数,这是一种强大的分析工具,可以在查询结果集中执行计算而无需将数据分组到多个输出行中。本文介绍了窗口函数的基本概念和使用方法,并通过几个实际案例展示了如何使用窗口函数进行成绩和排名统计、销售数据分析等操作。
1083 1
MySQL8 窗口函数
|
SQL 关系型数据库 MySQL
MySQL 窗口函数详解:分析性查询的强大工具
MySQL 窗口函数从 8.0 版本开始支持,提供了一种灵活的方式处理 SQL 查询中的数据。无需分组即可对行集进行分析,常用于计算排名、累计和、移动平均值等。基本语法包括 `function_name([arguments]) OVER ([PARTITION BY columns] [ORDER BY columns] [frame_clause])`,常见函数有 `ROW_NUMBER()`, `RANK()`, `DENSE_RANK()`, `SUM()`, `AVG()` 等。窗口框架定义了计算聚合值时应包含的行。适用于复杂数据操作和分析报告。
955 11
|
存储 关系型数据库 MySQL
MYSQL支持的存储引擎有哪些, 有什么区别
MYSQL存储引擎有很多, 常用的就二种 : MyISAM和InnerDB , 者两种存储引擎的区别 ; ● MyISAM支持256TB的数据存储 , InnerDB只支持64TB的数据存储 ● MyISAM 不支持事务 , InnerDB支持事务 ● MyISAM 不支持外键 , InnerDB支持外键
|
存储 关系型数据库 MySQL
MySQL引擎InnoDB和MyISAM的区别?
InnoDB是MySQL默认的事务型存储引擎,支持事务、行级锁、MVCC、在线热备份等特性,主索引为聚簇索引,适用于高并发、高可靠性的场景。MyISAM设计简单,支持压缩表、空间索引,但不支持事务和行级锁,适合读多写少、不要求事务的场景。
566 9
|
SQL 存储 关系型数据库
简单聊聊MySQL的三大日志(Redo Log、Binlog和Undo Log)各有什么区别
在MySQL数据库管理中,理解Redo Log(重做日志)、Binlog(二进制日志)和Undo Log(回滚日志)至关重要。Redo Log确保数据持久性和崩溃恢复;Binlog用于主从复制和数据恢复,记录逻辑操作;Undo Log支持事务的原子性和隔离性,实现回滚与MVCC。三者协同工作,保障事务ACID特性。文章还详细解析了日志写入流程及可能的异常情况,帮助深入理解数据库日志机制。
2244 0

推荐镜像

更多