MySQL分组后取最新的一条数据

MySQL分后取最新的一条数据

前言：在工作中经常遇到需要拿到某个分组后某个字段最大或最小的整行数据

准备

表结构

CREATE TABLE `StuScore` (
  `id` int(11) DEFAULT NULL ,
  `name` varchar(255) DEFAULT NULL COMMENT '学生名称',
  `score` double DEFAULT NULL COMMENT '课程分数',
  `courseName` varchar(255)  DEFAULT NULL COMMENT '课程名称'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

初始化数据

工具：Navicat 直接通过数据生成，生成测试数据即可。

实现

需求：查询每个学生课程分数最高的那一条数据。

思路1：先使用Group By拿到每个学生最高的成绩，再通过内连接与自身连接，连接条件为 a.name = b.name and a.max_score = b.score 这样就可以拿到每个学生分数最高的那一条数据。

说明：如果一个学生的两门都是最高成绩那么会出现一个学生多条数据。通过再次group by来解决

SELECT
	b.courseName
FROM
	( SELECT NAME, max( score ) max_score FROM StuScore GROUP BY NAME ) a
	JOIN StuScore b ON a.NAME = b.NAME AND a.max_score = b.score 
ORDER BY
	NAME;
> 0.005s左右

思路2：跟思路1大同小异，这种是通过子查询的方式来实现的。

说明：也是会出现思路1一样的问题，但性能比思路1要差不少，因为每一行数据都会去进行这个子查询。（自己的理解）

SELECT
	* 
FROM
	StuScore a 
WHERE
	score = ( SELECT max( score ) max_score FROM StuScore b WHERE b.NAME = a.NAME GROUP BY b.NAME );
> 0.400s左右

思路3：采用左连接的方式与自身连接，连接条件为a.NAME = b.NAME AND a.score < b.score。这样只有当同一个学生，他的课程成绩是小于其他课程成绩的b表中才会有数据。那么这个时候只需要再过滤掉b表中有数据的部分就能拿到每个学生分数最高的那一条数据。

说明：也会有出现思路1一样的问题。但性能会比思路2好一些。

SELECT
	a.*
FROM
	StuScore a
	LEFT JOIN StuScore b ON a.NAME = b.NAME 
	AND a.score < b.score
	WHERE b.id is null;
> 0.150s左右

思路4：优化思路3。我们不需要知道LEFT JOIN 后得到的结果集，只需要知道他是否存在数据就能知道他是否是最高的哪一行数据。这里就直接使用 NOT EXISTS 在WHERE中过滤即可。

说明：也会有出现思路1一样的问题。但性能与思路3好一些。

SELECT
	* 
FROM
	StuScore a 
WHERE
	NOT EXISTS ( SELECT 1 FROM StuScore b WHERE b.NAME = a.NAME AND a.score < b.score );
> 0.030s左右

思路5（推荐）：mysql8.0后 采用窗口函数 ROW_NUMBER()，使用 PARTITION BY NAME 根据名称来分组，并使用ORDER BY score DESC逆序排列，拿到本行在分区内的行号。最后再嵌套一层查询，拿到行号为1的数据就能拿到每个学生分数最高的那一条数据。

解释：ROW_NUMBER():拿到本在在分区内的行号

问题：不会出现每个学生会有多行数据的情况的，且性能与思路1相当

SELECT
	* 
FROM
	( SELECT *, ROW_NUMBER() OVER ( PARTITION BY NAME ORDER BY score DESC ) AS pm FROM StuScore ) a 
WHERE
	pm = 1
> 0.005s左右

思路6（推荐）：使用了GROUP_CONCAT与SUBSTRING_INDEX函数。根据NAME分组后，使用GROUP_CONCAT把所有课程通过课程成绩倒序的方式拼接到一起。再通过SUBSTRING_INDEX取出你想要的前几名课程名称即可。

解释：

GROUP_CONCAT([DISTINCT] column1 [ORDER BY column2 ASC\DESC] [SEPARATOR seq]) 通常结合group by 一起使用 将分组后每组的数据通过seq分隔符拼接到一起。

SUBSTRING_INDEX(str,delim,count) 字符串通过delim分割后取第一个分隔符前的所有内容。

说明：不会出现每个学生会有多行数据的情况的，且性能与思路1相当。这个比较灵活一点需要什么样的时候直接拼接到group_concat中即可，且不需要再去学习窗口函数的知识

SELECT NAME,
	SUBSTRING_INDEX( GROUP_CONCAT( courseName ORDER BY score DESC SEPARATOR '-' ), '-', 1 ) 
FROM
	StuScore 
GROUP BY
NAME
> 0.005s左右

结语：已上都是思路。都可都其进行扩展。使用自己擅长且有把握的即可。注意，数据量多了后这里的性能可能会不一致。