mysql实现排名函数三种方式

最新推荐文章于 2024-08-15 15:59:36 发布
最新推荐文章于 2024-08-15 15:59:36 发布
阅读量5.7k 收藏 1
点赞数 2
分类专栏： mysql 文章标签： mysql 排名

MySQL没有提供排名函数，但是我们可以通过一些技巧来实现开窗函数的效果。

1. 环境搭建、目标结果集


 
 
  1. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     CREATE 
     
     TABLE 
     
     `tem` (
    
    
    
   
   
  2. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
       
     
     `id` 
     
     int(
     
     11) 
     
     NOT 
     
     NULL AUTO_INCREMENT,
    
    
    
   
   
  3. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
       
     
     `str` 
     
     char(
     
     1) 
     
     DEFAULT 
     
     NULL,
    
    
    
   
   
  4. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
       PRIMARY 
     
     KEY (
     
     `id`)
    
    
    
   
   
  5. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     ) 
     
     ENGINE=
     
     InnoDB AUTO_INCREMENT=
     
     9 
     
     DEFAULT 
     
     CHARSET=utf8;
    
    
    
   
   
  6. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
     
    
    
    
   
   
  7. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
     
    
    
    
   
   
  8. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     INSERT 
     
     INTO 
     
     `test`.
     
     `tem`(
     
     `id`, 
     
     `str`) 
     
     VALUES (
     
     1, 
     
     'A');
    
    
    
   
   
  9. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     INSERT 
     
     INTO 
     
     `test`.
     
     `tem`(
     
     `id`, 
     
     `str`) 
     
     VALUES (
     
     2, 
     
     'B');
    
    
    
   
   
  10. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     INSERT 
     
     INTO 
     
     `test`.
     
     `tem`(
     
     `id`, 
     
     `str`) 
     
     VALUES (
     
     3, 
     
     'A');
    
    
    
   
   
  11. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     INSERT 
     
     INTO 
     
     `test`.
     
     `tem`(
     
     `id`, 
     
     `str`) 
     
     VALUES (
     
     4, 
     
     'C');
    
    
    
   
   
  12. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     INSERT 
     
     INTO 
     
     `test`.
     
     `tem`(
     
     `id`, 
     
     `str`) 
     
     VALUES (
     
     5, 
     
     'A');
    
    
    
   
   
  13. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     INSERT 
     
     INTO 
     
     `test`.
     
     `tem`(
     
     `id`, 
     
     `str`) 
     
     VALUES (
     
     6, 
     
     'C');
    
    
    
   
   
  14. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     INSERT 
     
     INTO 
     
     `test`.
     
     `tem`(
     
     `id`, 
     
     `str`) 
     
     VALUES (
     
     7, 
     
     'B');
    
    
    
   
   
  15. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     INSERT 
     
     INTO 
     
     `test`.
     
     `tem`(
     
     `id`, 
     
     `str`) 
     
     VALUES (
     
     8, 
     
     'A');

num

id

str

11A
23A
35A
48A
12B
27B
14C
26C

2.实现方法

2.1 连表查询


 
 
  1. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     SELECT
    
    
    
   
   
  2. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
         
     
     count(*) 
     
     num,
    
    
    
   
   
  3. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
         t1.*
    
    
    
   
   
  4. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     FROM
    
    
    
   
   
  5. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
         tem t1
    
    
    
   
   
  6. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
         
     
     INNER 
     
     JOIN tem t2 
     
     ON t1.str = t2.str 
     
     AND t1.id >= t2.id
    
    
    
   
   
  7. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     GROUP 
     
     BY
    
    
    
   
   
  8. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
         t1.id
    
    
    
   
   
  9. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     ORDER 
     
     BY
    
    
    
   
   
  10. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
         t1.str, t1.id;

让我们看看这个查询的执行计划


 
 
  1. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     +----+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+
    
    
    
   
   
  2. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     | id | select_type 
     
     | table | type 
     
     | possible_keys | key  
     
     | key_len | ref  
     
     | rows | Extra                                           
     
     |
    
    
    
   
   
  3. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     +----+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+
    
    
    
   
   
  4. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     |  
     
     1 
     
     | SIMPLE      | t1    
     
     | ALL  | PRIMARY       
     
     | NULL | NULL    
     
     | NULL |    
     
     8 
     
     | Using temporary; Using filesort                 |
    
    
    
   
   
  5. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     |  1 | SIMPLE      
     
     | t2    | ALL  
     
     | PRIMARY       | NULL 
     
     | NULL    | NULL 
     
     |    8 | Using where; Using join buffer (flat, BNL join) 
     
     |
    
    
    
   
   
  6. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     +----+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+
    
    
    
   
   
  7. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     2 rows in set (0.06 sec)

非常低效：临时表、文件排序、循环嵌套都用上了

2.2 子查询


 
 
  1. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     SELECT
    
    
    
   
   
  2. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
         (
     
     SELECT 
     
     COUNT(*) 
     
     FROM tem t2 
     
     WHERE t1.str = t2.str 
     
     AND t1.id >= t2.id) 
     
     num,
    
    
    
   
   
  3. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
         t1.*
    
    
    
   
   
  4. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     FROM
    
    
    
   
   
  5. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
         tem t1
    
    
    
   
   
  6. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     ORDER 
     
     BY
    
    
    
   
   
  7. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
         t1.str, t1.id;

再看看执行计划


 
 
  1. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     +----+--------------------+-------+------+---------------+------+---------+------+------+----------------+
    
    
    
   
   
  2. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     | id | select_type        
     
     | table | type 
     
     | possible_keys | key  
     
     | key_len | ref  
     
     | rows | Extra          
     
     |
    
    
    
   
   
  3. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     +----+--------------------+-------+------+---------------+------+---------+------+------+----------------+
    
    
    
   
   
  4. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     |  
     
     1 
     
     | PRIMARY            | t1    
     
     | ALL  | NULL          
     
     | NULL | NULL    
     
     | NULL |    
     
     8 
     
     | Using filesort |
    
    
    
   
   
  5. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     |  2 | DEPENDENT SUBQUERY 
     
     | t2    | ALL  
     
     | PRIMARY       | NULL 
     
     | NULL    | NULL 
     
     |    8 | Using where    
     
     |
    
    
    
   
   
  6. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     +----+--------------------+-------+------+---------------+------+---------+------+------+----------------+
    
    
    
   
   
  7. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     2 rows in set (0.06 sec)

很好，两次全扫描，而且只用到了文件排序，如果加上索引，文件排序也可以避免。

2.3MySQL变量


 
 
  1. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     SELECT
    
    
    
   
   
  2. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
         @
     
     num := 
     
     IF(@
     
     str = 
     
     str, @
     
     num + 
     
     1, 
     
     1) 
     
     num,
    
    
    
   
   
  3. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
         
     
     id,
    
    
    
   
   
  4. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
         @
     
     str := 
     
     str 
     
     str
    
    
    
   
   
  5. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     FROM
    
    
    
   
   
  6. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
         tem, (
     
     SELECT @
     
     str := 
     
     '', @
     
     num := 
     
     0) t1
    
    
    
   
   
  7. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     ORDER 
     
     BY
    
    
    
   
   
  8. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
         
     
     str, 
     
     id;

执行计划


 
 
  1. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     +----+-------------+------------+--------+---------------+------+---------+------+------+----------------+
    
    
    
   
   
  2. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     | id | select_type 
     
     | table      | type   
     
     | possible_keys | key  
     
     | key_len | ref  
     
     | rows | Extra          
     
     |
    
    
    
   
   
  3. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     +----+-------------+------------+--------+---------------+------+---------+------+------+----------------+
    
    
    
   
   
  4. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     |  
     
     1 
     
     | PRIMARY     | <derived2> 
     
     | system | NULL          
     
     | NULL | NULL    
     
     | NULL |    
     
     1 
     
     |                |
    
    
    
   
   
  5. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     |  1 | PRIMARY     
     
     | tem        | ALL    
     
     | NULL          | NULL 
     
     | NULL    | NULL 
     
     |    8 | Using filesort 
     
     |
    
    
    
   
   
  6. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     |  
     
     2 
     
     | DERIVED     | NULL       
     
     | NULL   | NULL          
     
     | NULL | NULL    
     
     | NULL | NULL 
     
     | No tables used |
    
    
    
   
   
  7. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     +----+-------------+------------+--------+---------------+------+---------+------+------+----------------+
    
    
    
   
   
  8. 
   
   
    
    
    
    
   
   
    
   
   
    
    
    
    
     
     3 rows 
     
     in set (
     
     0.
     
     06 sec)

效率最高，一次全扫描搞定。文件排序是因为没有索引。

Ethan Chen
关注
专栏目录
MYSQL实现排名函数RANK，DENSE_RANK和ROW_NUMBER
qq_25933249的博客
02-08 420
1. 排名分类 1.1 区别RANK，DENSE_RANK和ROW_NUMBER RANK并列跳跃排名，并列即相同的值，相同的值保留重复名次，遇到下一个不同值时，跳跃到总共的排名。 DENSE_RANK并列连续排序，并列即相同的值，相同的值保留重复名次，遇到下一个不同值时，依然按照连续数字排名。 ROW_NUMBER连续排名，即使相同的值，依旧按照连续数字进行排名。 区别如图： 1.2 分组排名 将数据分组后排名，区别如图： 2. 准备数据 创建一张分数表，里面有字段：分数score，课程号course
mysql四大排名函数解析
Super_Tr的博客
05-14 486
row_number()：依次递增排名，无重复排名 rank()：相同分数有重复排名，但是重复后下一个人按照实际排名 dense_rank()：分数一致排名一致，分数不一致排名+1 NTILE(4)：分组排名，里面的数字是几，最多排名就是几，里面的数字是4，最多的排名就是4 有如下表1.row_number row_number在排名时序号 连续 不重复，即使遇到表中的两个3时亦如此 执行 select row_number() OVER(order by e.salary desc) as...
Mysql实现排名函数的三种方法
09-11 9340
MySQL没有提供排名函数，但是我们可以通过一些技巧来实现开窗函数的效果。 1. 环境搭建、目标结果集 CREATE TABLE `tem` (   `id` int(11) NOT NULL AUTO_INCREMENT,   `str` char(1) DEFAULT NULL,   PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=9 ...
mysql的排名函数怎么用_MySQL排名函数
weixin_28730665的博客
02-19 704
MySQL排名函数MySQL使用一种排名功能，该函数使我们能够对数据库中分区的每一行进行排名。排名函数还是MySQL窗口函数的一部分。 MySQL中的排名函数可以与以下子句一起使用：他们总是使用OVER()他们基于ORDER BY为每行分配一个等级他们按顺序为每行分配一个等级。他们总是为行分配一个等级，每个新分区都以一个等级开始。注意：请注意，自8.0版起，MySQL提供了对排名和窗口函数的支持。...
MySQL（四）：使用SQL实现排名
最新发布
欧阳方超的专栏
08-15 1206
RANK()与ROW_NUMBER()和DENSE_RANK()的区别在于，ROW_NUMBER()为每一行分配唯一的排名，而DENSE_RANK()则不会跳过排名。
mysql四大排名函数
weixin_28952343的博客
04-03 477
注意：在使用row_number() 实现分页时需要特别注意一点，over子句中的order by 要与SQL排序记录中的order by保持一致，否则得到的序号可能不是连续的 select *,row_number() OVER(order by number ) as row_num from num ORDER BY id。总结：ROW_NUMBER()连续不重复， Rank()不连续重复，dense_rank() 连续重复，ntile()分组。
Mysql 学习
qq_34064680的博客
04-01 1704
Mysql 学习安装使用新的改变功能快捷键合理的创建标题，有助于目录的生成如何改变文本的样式插入链接与图片如何插入一段漂亮的代码片生成一个适合你的列表创建一个表格设定内容居中、居左、居右SmartyPants创建一个自定义列表如何创建一个注脚注释也是必不可少的KaTeX数学公式新的甘特图功能，丰富你的文章UML 图表FLowchart流程图导出与导入导出导入 安装 检查是否安装 rpm -qa|grep -i mysql 使用 你好！ 这是你第一次使用 Markdown编辑器 所展示的欢迎页。如果你想
MySQL四大排名函数
sky2019116的博客
02-02 1566
本文解释MySQL中四大排名函数的具体功能，并做了的使用示例。具体效果会根据实际数据和排序条件不同而有所差异。
MYSQL实现排名及查询指定用户排名功能（并列排名功能）实例代码
12-16
在MySQL数据库中，实现排名和查询特定用户排名的功能是一项常见的需求，特别是在数据分析和竞赛类应用中。本篇文章将详细介绍如何利用SQL语句实现这一功能，包括处理并列排名的情况。 首先，我们需要创建一个用于...
MySQL实现排名/分组排名/合计（8.0窗口函数rank/dense_rank、5.6/5.7自行模拟）
reading-conference-writing
10-28 6704
介绍MySQL查询时直接显示排名/分组排名/分组合计的方法，支持5.6/5.7/8.0版本。涉及`rank()`和`dense_rank()`的使用、`window`窗口函数、MySQL变量的一些用法
mysql 怎么实现组内排名_MySQL 实现排名（分组排名）
weixin_39770506的博客
01-18 1563
在MYSQL的最新版本MYSQL8已经支持了排名函数RANK，DENSE_RANK和ROW_NUMBER。但是在5.*版本中还不支持这些函数，只能自己实现。实现方法主要用到了条件判断语句(CASE WHEN或IF)和添加临时变量。基本知识：sql语句中，使用@来定义一个变量。如：@abcsql语句中，使用:=来给变量赋值,：@abc:=123,则变量abc的值为123sql语句中，if(A,B,C...
mysql xor 索引_mysql构架,索引,视图，查询语句
weixin_29133959的博客
01-27 262
mysql构架：connection pool：因为单进程多线程，所以需要一个线程池接收请求提供并发，线程重用，还能完成认证SQL interface：接收并分析SQL语句Parser：分析器，翻译sql语句，验证用户权限，执行响应指令，生成执行树Optimizer：优化器，通过分析索引结构，统计情况等衡量多个访问路径哪个开销最小，生成统计数据，查询语句改写Cache & Buffer：热...
mysql四大排名函数(row_number、rank、dense_rank、ntile)使用简介
qq_31476455的博客
05-09 2万+
有如下表 1.row_number row_number在排名时序号 连续 不重复，即使遇到表中的两个3时亦如此 执行 select row_number() OVER(order by e.salary desc) as row_num , e.salary from employee e 结果如图 注意：在使用row_number实现分页时需要特别注意一点，over子句中的order by...
Mysql高级
09-20 148
文章目录1. mysql的架构介绍Mysql安装（linux环境）配置文件修改字符集配置主要配置文件逻辑架构介绍存储引擎2. 索引优化分析前言常见通用的join查询索引简介性能分析索引优化3. 查询截取分析查询优化慢查询日志show profile全局查询日志4. Mysql锁机制5. 主从复制 1. mysql的架构介绍 Mysql安装（linux环境） cd /opt rpm -qa|grep -i mysql ：查询是否安装过mysql（qa:query-all） rpm -e RPM软件包名 删除
mysql Using join buffer (Block Nested Loop) join连接查询优化
热门推荐
u013958151的博客
01-11 4万+
最近在优化链表查询的时候发现就算链接的表里面不到1w的数据链接查询也需要10多秒，这个速度简直不能忍受 通过EXPLAIN发现，extra中有数据是Using join buffer (Block Nested Loop)，而其它关联的表都是Using index，最后使用排除法发现当extra为Using join buffer (Block Nested Loop)是最慢的，就算加了索...
记自己一次sql优化（使用伪列）
fjq5a的专栏
01-18 546
起因 未优化之前的s q l,大表内连接次数太多，严重影响效率。因此需要优化。 sql 要满足的逻辑 以status_id分组，weight_id最大的行中（同一组中可能有好几个weight_id相同的行），mid最大的那一行 表结构 CREATE TABLE `at_x_0` ( `uixd` , `mixd` , `status_ixd` , `message_type`
分组排序纠结者的福音来了
pp的博客
12-01 628
在MYSQL的最新版本MYSQL8已经支持了排名函数RANK，DENSE_RANK和ROW_NUMBER。但是在就得版本中还不支持这些函数，只能自己实现。实现方法主要用到了条件判断语句（CASE WHEN或IF）和添加临时变量。 1. 排名分类 1.1 区别RANK，DENSE_RANK和ROW_NUMBER RANK并列跳跃排名，并列即相同的值，相同的值保留重复名次，遇到下一个不同值时，跳跃到总共的排名。 DENSE_RANK并列连续排序，并列即相同的值，相同的值保留重复名次，遇到下一个不同值.
mysql实现排名并列
05-22
在MySQL中实现排名并列的方法有很多，其中比较常用的是使用用户定义变量（User-Defined Variables）和子查询来实现。以下是使用用户定义变量的方法： ``` SELECT score, @rank := IF(@score=score, @rank, @rank+1...
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值