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 版本实现

使用窗口函数很方便实现

select
	id,
	class,
	score,
	row_number() over(partition by class order by score desc ) as scorerank,
	version() mysqlVersion
from
	class_score

说明： partition by 进行分桶，分桶之后排序。关于8.0窗口函数可参考：[[MySQL 8.0窗口函数 ROW_NUMBER() OVER()函数的使用]]，英文好的大佬也可以在mysql官网查看。

如果需要取每科的第一名成绩，则在上一结果集中使用scorerank就可以控制

select
	id,
	class,
	score
from
	(
	select
		id,
		class,
		score,
		row_number() over(partition by class order by score desc ) as scorerank
	from
		class_score) row_score
where
	scorerank = 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 方式一：先排序，后分组

select
	cs.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混乱，如下

select
	cs.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 (
	select
		cs.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) cs2
where rownum < 2

这种方式

2.2 方式二：先排序，后分组

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

-- having之前可以添加where条件
select
	version(),
	cs.id,
	cs.class,
	cs.score,
	case
		when @classgroup = cs.class then @rownum := @rownum + 1
		else @rownum := 1
	end as rownum,
	@classgroup := cs.class
from
	(
	select
		@rownum := 0,
		@classgroup := 0) temp,
		class_score cs
where class < 3
having
	rownum < 2
order by
	class,
	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

SELECT
    CONCAT(
        '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 ),
        ';' 
) 
FROM
    information_schema.COLUMNS 
WHERE
    TABLE_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 (
	select
		cs.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) cs2
where rownum < 2

case2

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

select
	id,
	class,
	score
from
	(
	select
		id,
		class,
		score,
		row_number() over(partition by class order by score desc ) as scorerank
	from
		class_score) row_score
where
	scorerank = 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