Mysql-组分去重/排序-不同版本实现row_number()

2023-10-08 04:20

本文主要是介绍Mysql-组分去重/排序-不同版本实现row_number(),希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

一、 使用场景

  1. 在数据有重复情况下,取ID最大或者最小的数据
  2. 按照某个规则取提取数据行,比如需要分组排序后取topN数据。最简单的一个例子就比如提取一个班级不同科目排名前3同学的信息。下面以此为例进行mysql-5.7与mysql-8.0分析(5.7和8.0差异性比较大)。

二、创建模拟数据

DROP TABLE IF EXISTS `class_score`;
CREATE TABLE `class_score` (`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学号',`class` int(11) NOT NULL  COMMENT '科目',`score`  int(11) NOT NULL COMMENT '分数',PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT= 'class_score';INSERT INTO class_score (class,score) VALUES(1,88),(1,90),(1,70),(2,98),(2,86),(2,93),(3,87),(3,79),(3,95);

三、按照科目分组排序

MySql-8.0添加了窗口函数,功能更加强大,而5.7并没有窗口函数,可以通过在sql语句中定义变量来实现。

1. Mysql-8.0 版本实现

使用窗口函数很方便实现

selectid,class,score,row_number() over(partition by class order by score desc ) as scorerank,version() mysqlVersion
fromclass_score

说明: partition by 进行分桶,分桶之后排序。关于8.0窗口函数可参考:[[MySQL 8.0窗口函数 ROW_NUMBER() OVER()函数的使用]],英文好的大佬也可以在mysql官网查看。
在这里插入图片描述
如果需要取每科的第一名成绩,则在上一结果集中使用scorerank就可以控制

selectid,class,score
from(selectid,class,score,row_number() over(partition by class order by score desc ) as scorerankfromclass_score) row_score
wherescorerank = 1

在这里插入图片描述

注意:此sql在5.7环境执行会报错版本问题SQL 错误 [1064] [42000]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(partition by class order by score desc ) as scorerank

2. Mysql-5.7 版本实现row_number()

核心注意点

-- 条件是 =  赋值是 :=  (这个大坑会在 四、注意事项中详细说明)
-- 有一点需要注意的就是if之后的 @classgroup := cs.class 相当于 if 的语句块
-- 其中也可以写为 case when 语句
if(@classgroup = cs.class, @rownum := @rownum + 1, @rownum := 1) as rownum,@classgroup := cs.class-- case when 格式 @classgroup := cs.class 也相当于语句块
case when @classgroup = cs.class then  @rownum := @rownum + 1 else @rownum := 1 end as rownum,@classgroup := cs.class
2.1 方式一:先排序,后分组
selectcs.id,cs.class,cs.score,if(@classgroup = cs.class, @rownum := @rownum + 1, @rownum := 1) as rownum,@classgroup := cs.class
from(select @rownum := 0, @classgroup := 0) temp,(select id, class, score from class_score order by class, score desc) cs-- 有一点需要注意的就是if之后的 @classgroup := cs.class 相当于 if 的语句块
-- 其中也可以写为 case when 语句
if(@classgroup = cs.class, @rownum := @rownum + 1, @rownum := 1) as rownum,@classgroup := cs.class-- case when 格式
case when @classgroup = cs.class then  @rownum := @rownum + 1 else @rownum := 1 end as rownum,@classgroup := cs.class

结果如下
在这里插入图片描述
重点:这种方式有个缺点就是后面不能之间使用having,不管是5.7还是8.0都会导致rownum混乱,如下

selectcs.id,cs.class,cs.score,if(@classgroup = cs.class, @rownum := @rownum + 1, @rownum := 1) as rownum,@classgroup := cs.class
from(select @rownum := 0, @classgroup := 0) temp,(select id, class, score from class_score order by class, score desc) cs
having rownum <3

结果却是

是不是和预想不太一样,不是想要的结果呢,用此方式取topN则需要在查询一次才可以

select id, class ,score , rownum  from (selectcs.id,cs.class,cs.score,if(@classgroup = cs.class, @rownum := @rownum + 1, @rownum := 1) as rownum,@classgroup := cs.classfrom(select @rownum := 0, @classgroup := 0) temp,(select id, class, score from class_score order by class, score desc) cs) cs2
where rownum < 2

这种方式

2.2 方式二:先排序,后分组

这种方式对比方式一的有点就是取topN可以少查询一次,但是,但是重点来啦,这种方式在8.0上的结果和5.7的结果完全不一样,请看结果

-- having之前可以添加where条件
selectversion(),cs.id,cs.class,cs.score,casewhen @classgroup = cs.class then @rownum := @rownum + 1else @rownum := 1end as rownum,@classgroup := cs.class
from(select@rownum := 0,@classgroup := 0) temp,class_score cs
where class < 3
havingrownum < 2
order byclass,score desc

5.7是正确的结果,但是8.0就混乱了
在这里插入图片描述

在这里插入图片描述

四、注意事项(采坑记录)

由于开发和测试环境mysql版本不同,使用分组去重之后,在开发5.7版本环境下使用sql定义变量方式是正常的,但是在测试8.0环境下就出现问题了。。。好吧,有小伙伴可能会说怎么环境信息怎么还不一样?但是项目实际情况就是这样子,而且创建的表还有区别,请往下看。(偷偷抱怨一哈)

1. 同样的sql在不同版本的MySql执行结果不一致…

问题就出在使用了三、2.2的方式,5.7中是正常的,但是8.0就懵*了…业务是在sql中去重之后分页排序,所以只能在sql中去重,不然就要内存分页了(个人想法,有大佬有其他思路可以共同探讨),去重之后数据竟然在8.0环境上还有重复数据,还是作为返回体的主键冲突。

详见三、2.2,此处就不加赘述了。

2. 是关于 = 与 := 直接导致的问题(被 : 支配的一天)

出现了问题1,于是乎各种找问题(各种搜索,顺便问了一个对 Oracle 比较熟悉的朋友,好吧,本作者表示不熟悉),然后再 if后的将 = 改为 :=,更神奇的是既然效果实现了。。。但是,但是隐藏着一个大坑:加其他条件的时候基本只能查询到一条数据(实际上有多条)
注意:总感觉这个是个闪现的问题,因为有时候报的错误还不一样,但是能确定的是报错 3。有可能是mysql桌面化工具的问题,看看 = 与 :=的差别就很好理解了。

if(@classgroup := cs.class, @rownum := @rownum + 1, @rownum := 1) as rownum,@classgroup := cs.class

参考 https://blog.csdn.net/qq_37155959/article/details/81005287
具体差别如下
= 只有在set和update时才是和:=一样,赋值的作用,其它都是等于的作用。鉴于此,用变量实现行号时,必须用:=
:= 不只在set和update时时赋值的作用,在select也是赋值的作用。

3.关于5.7和8.0排序规则的不同导致的问题

SQL 错误 [1267] [HY000]: Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='

原因就是8.0默认排序规则是 utf8mb4_0900_ai_ci,但是5.7使用utf8mb4默认的是utf8mb4_general_ci,这里涉及到Mysql底层的东西,了解即可,毕竟不是专业的DBA,
在这里插入图片描述
具体可参考:https://www.yisu.com/zixun/167157.html
这个问题比较容易,网上的帖子搜索一下一大把,主要有个批量处理的sql说一下,参考https://blog.csdn.net/weixin_46080292/article/details/124195512

SELECTCONCAT('ALTER TABLE `',TABLE_NAME,'` MODIFY `',COLUMN_NAME,'` ',DATA_TYPE,'(',CHARACTER_MAXIMUM_LENGTH,') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci',( CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END ),';' 
) 
FROMinformation_schema.COLUMNS 
WHERETABLE_SCHEMA = '库名' AND (DATA_TYPE = 'varchar' OR DATA_TYPE = 'char')and TABLE_NAME = '表名'

动态生成修改排序规则的sql,不用一个个字段修改其排序规则

五、总结

1.一个项目最好使用相同版本的Mysql。

重要的事情说三遍:
不同版本MySql的坑比较多。
不同版本MySql的坑比较多。
不同版本MySql的坑比较多。

2.Mysql版本可能有变换处理方式:

case1

如果涉及到去重排序的使用5.7版本的2.1方式,虽然可以多查询了一次,但是能适配8.0版本。
重新写一遍sql吧

select id, class ,score , rownum  from (selectcs.id,cs.class,cs.score,if(@classgroup = cs.class, @rownum := @rownum + 1, @rownum := 1) as rownum,@classgroup := cs.classfrom(select @rownum := 0, @classgroup := 0) temp,(select id, class, score from class_score order by class, score desc) cs) cs2
where rownum < 2
case2

已经是8.0版本的,建议之间用窗口函数即可

selectid,class,score
from(selectid,class,score,row_number() over(partition by class order by score desc ) as scorerankfromclass_score) row_score
wherescorerank = 1

4. where和having区别

1.执行的时机不一样:where是分组之前进行限定,不满足where条件,则不参与分组,而having是分组之后对结果进行过滤
2.可判断的条件不一样:where不能对聚合函数进行判断,having可以。
3.执行顺序:where>聚合函数>having
参考:
https://blog.csdn.net/Trybravely/article/details/122435317
https://blog.csdn.net/weixin_38860401/article/details/81332605

5. sql关键字执行顺序

参考:https://blog.csdn.net/u014044812/article/details/51004754

这篇关于Mysql-组分去重/排序-不同版本实现row_number()的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/162664

相关文章

MySQL查询JSON数组字段包含特定字符串的方法

《MySQL查询JSON数组字段包含特定字符串的方法》在MySQL数据库中,当某个字段存储的是JSON数组,需要查询数组中包含特定字符串的记录时传统的LIKE语句无法直接使用,下面小编就为大家介绍两种... 目录问题背景解决方案对比1. 精确匹配方案(推荐)2. 模糊匹配方案参数化查询示例使用场景建议性能优

关于集合与数组转换实现方法

《关于集合与数组转换实现方法》:本文主要介绍关于集合与数组转换实现方法,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录1、Arrays.asList()1.1、方法作用1.2、内部实现1.3、修改元素的影响1.4、注意事项2、list.toArray()2.1、方

使用Python实现可恢复式多线程下载器

《使用Python实现可恢复式多线程下载器》在数字时代,大文件下载已成为日常操作,本文将手把手教你用Python打造专业级下载器,实现断点续传,多线程加速,速度限制等功能,感兴趣的小伙伴可以了解下... 目录一、智能续传:从崩溃边缘抢救进度二、多线程加速:榨干网络带宽三、速度控制:做网络的好邻居四、终端交互

mysql表操作与查询功能详解

《mysql表操作与查询功能详解》本文系统讲解MySQL表操作与查询,涵盖创建、修改、复制表语法,基本查询结构及WHERE、GROUPBY等子句,本文结合实例代码给大家介绍的非常详细,感兴趣的朋友跟随... 目录01.表的操作1.1表操作概览1.2创建表1.3修改表1.4复制表02.基本查询操作2.1 SE

MySQL中的锁机制详解之全局锁,表级锁,行级锁

《MySQL中的锁机制详解之全局锁,表级锁,行级锁》MySQL锁机制通过全局、表级、行级锁控制并发,保障数据一致性与隔离性,全局锁适用于全库备份,表级锁适合读多写少场景,行级锁(InnoDB)实现高并... 目录一、锁机制基础:从并发问题到锁分类1.1 并发访问的三大问题1.2 锁的核心作用1.3 锁粒度分

MySQL数据库中ENUM的用法是什么详解

《MySQL数据库中ENUM的用法是什么详解》ENUM是一个字符串对象,用于指定一组预定义的值,并可在创建表时使用,下面:本文主要介绍MySQL数据库中ENUM的用法是什么的相关资料,文中通过代码... 目录mysql 中 ENUM 的用法一、ENUM 的定义与语法二、ENUM 的特点三、ENUM 的用法1

MySQL count()聚合函数详解

《MySQLcount()聚合函数详解》MySQL中的COUNT()函数,它是SQL中最常用的聚合函数之一,用于计算表中符合特定条件的行数,本文给大家介绍MySQLcount()聚合函数,感兴趣的朋... 目录核心功能语法形式重要特性与行为如何选择使用哪种形式?总结深入剖析一下 mysql 中的 COUNT

java实现docker镜像上传到harbor仓库的方式

《java实现docker镜像上传到harbor仓库的方式》:本文主要介绍java实现docker镜像上传到harbor仓库的方式,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地... 目录1. 前 言2. 编写工具类2.1 引入依赖包2.2 使用当前服务器的docker环境推送镜像2.2

C++20管道运算符的实现示例

《C++20管道运算符的实现示例》本文简要介绍C++20管道运算符的使用与实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧... 目录标准库的管道运算符使用自己实现类似的管道运算符我们不打算介绍太多,因为它实际属于c++20最为重要的

Java easyExcel实现导入多sheet的Excel

《JavaeasyExcel实现导入多sheet的Excel》这篇文章主要为大家详细介绍了如何使用JavaeasyExcel实现导入多sheet的Excel,文中的示例代码讲解详细,感兴趣的小伙伴可... 目录1.官网2.Excel样式3.代码1.官网easyExcel官网2.Excel样式3.代码