mysql8 的window function 解决分组后多行取某一行的完整数据（ONLY_FULL_GROUP_BY模式下）

mysql8 的window function 解决分组后多行取某一行的完整数据（ONLY_FULL_GROUP_BY模式下）

注：非ONLY_FULL_GROUP_BY模式的就不再赘述！
参考https://dev.mysql.com/doc/refman/8.0/en/window-functions.html

表结构

CREATE TABLE `aa` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `total` int(11) NOT NULL,
  `code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `other` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC;

数据

INSERT INTO `aa` VALUES (1, 10, '001', 'aaaa');
INSERT INTO `aa` VALUES (2, 20, '003', 'bbbb');
INSERT INTO `aa` VALUES (3, 30, '002', 'cccc');
INSERT INTO `aa` VALUES (4, 40, '002', 'dddd');
INSERT INTO `aa` VALUES (5, 40, '002', 'eeee');
INSERT INTO `aa` VALUES (6, 25, '002', 'ffff');
INSERT INTO `aa` VALUES (7, 5, '001', 'gggg');
INSERT INTO `aa` VALUES (8, 35, '003', 'hhhh');

需求：获取每种code下的total最大值坐在的整行数据 即

注：这里可以看出来 id为4和5的数据都是total都是40，code都是002，这里还可以按照other排序来获取结果显示id为4的还是5的数据（下面会体现）

先不用窗口函数(window function)想一下思路：

先根据code分组，再取最大的total，但是发现code为002的数据有两条最大的都是40，mysql会选择哪条的other字段呢？
ONLY_FULL_GROUP_BY模式 是取不到other的，如果再join这张表，code=code and total = total 那也会出现两条

还是很难筛选，所以不采用这种办法！

好在mysql8.0提供了类似于oracle数据库的窗口函数

来看下window function的实现，甚至都不需要group by

先根据code（PARTITION） 分区（和mysql里的分区表没有任何关系，更像是分组）
再分区之后对total字段进行order by 降序
再row_number() 体现分区内的行数，递增

SELECT
	id,
	total,
	other,
	CODE,
	ROW_NUMBER() over ( PARTITION BY CODE ORDER BY total DESC, other DESC ) AS rn 
FROM
	aa

或者 定义一个带name 的window , name 为 "w" ,方便重复使用

SELECT
	id,
	total,
	other,
	CODE,
	ROW_NUMBER() over w AS rn 
FROM
	aa window w AS ( PARTITION BY CODE ORDER BY total DESC )) AS t

得到的结果如下

可以看出来 同一个code下最大的total 所对应的rn都是1

那只需要对这个临时表进行查询，条件为rn = 1 就可以得到所需要的数据了

SELECT
	* 
FROM
	(
	SELECT
		id,
		total,
		other,
		CODE,
		ROW_NUMBER() over w AS rn 
	FROM
	aa window w AS ( PARTITION BY CODE ORDER BY total DESC )) AS t 
WHERE
	rn = 1

以上完成了需求的功能。

可以看出上图标红线的id为4的数据，还记得id为5 的数据也是code为002但是other为eeee，那我现在想要other为dddd的怎么办？（id4 和 id5 区别在于other字段一个为dddd一个为eeee）
好办，只需要在分区里面加上对other加上排序

可以看出顺序已经变了，在查询一下这个临时表

结果正确