Mysql分组取最新一条数据总结（多种方法比较）_mysql分组后取最新的一条记录

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

---

前言

在日常业务开发中涉及到报表的统计，或者取某一个用户最新的一条记录并对他进行业务分析等等，现总结了常见的几种方法去取这这一类数据 这里以mysql 5.7为例子

---

一、mysql查询数据准备

这里准备了一张表yh_assessment_record ，表中有4个用户，每一个用户有一条最新的记录根据 assessment_time 字段来确定

CREATE TABLE `yh_assessment_record` (
  `record_id` varchar(32) NOT NULL COMMENT '主键',
  `elder_id` varchar(32) NOT NULL DEFAULT '' COMMENT '老人id',
  `elder_name` varchar(32) NOT NULL DEFAULT '' COMMENT '老人姓名（冗余）',
  `assessment_time` bigint(20) DEFAULT '0' COMMENT '评估时间',
  `assessment_result` varchar(32) DEFAULT NULL COMMENT '评估结果',
  `is_least` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否最新一条评估记录(0:否 1：是)',
  `is_delete` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否删除0:未删除1:删除',
  `create_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '生成时间',
  `update_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '修改时间',
  PRIMARY KEY (`record_id`),
  KEY `elder_id_index` (`elder_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='人员评估记录表';


INSERT INTO `yh_serve_test`.`yh_assessment_record` (`record_id`, `elder_id`, `elder_name`, `assessment_time`, `assessment_result`, `is_least`, `is_delete`, `create_time`, `update_time`) VALUES ('1', '1', '用户1', 1721382926, '评估结果1', 0, 0, 1721382926, 1721382926);
INSERT INTO `yh_serve_test`.`yh_assessment_record` (`record_id`, `elder_id`, `elder_name`, `assessment_time`, `assessment_result`, `is_least`, `is_delete`, `create_time`, `update_time`) VALUES ('10', '3', '用户3', 1731383990, '评估结果(最新)', 1, 0, 1721382926, 1721382926);
INSERT INTO `yh_serve_test`.`yh_assessment_record` (`record_id`, `elder_id`, `elder_name`, `assessment_time`, `assessment_result`, `is_least`, `is_delete`, `create_time`, `update_time`) VALUES ('11', '4', '用户4', 1732382927, '评估结果1', 0, 0, 1721382926, 1721382926);
INSERT INTO `yh_serve_test`.`yh_assessment_record` (`record_id`, `elder_id`, `elder_name`, `assessment_time`, `assessment_result`, `is_least`, `is_delete`, `create_time`, `update_time`) VALUES ('12', '4', '用户4', 1732382998, '评估结果2', 0, 0, 1721382926, 1721382926);
INSERT INTO `yh_serve_test`.`yh_assessment_record` (`record_id`, `elder_id`, `elder_name`, `assessment_time`, `assessment_result`, `is_least`, `is_delete`, `create_time`, `update_time`) VALUES ('13', '4', '用户4', 1732382927, '评估结果3', 0, 0, 1721382926, 1721382926);
INSERT INTO `yh_serve_test`.`yh_assessment_record` (`record_id`, `elder_id`, `elder_name`, `assessment_time`, `assessment_result`, `is_least`, `is_delete`, `create_time`, `update_time`) VALUES ('15', '4', '用户4', 1732382998, '评估结果4', 0, 0, 1721382926, 1721382926);
INSERT INTO `yh_serve_test`.`yh_assessment_record` (`record_id`, `elder_id`, `elder_name`, `assessment_time`, `assessment_result`, `is_least`, `is_delete`, `create_time`, `update_time`) VALUES ('16', '4', '用户4', 1832383990, '评估结果(最新)', 1, 0, 1721382926, 1721382926);
INSERT INTO `yh_serve_test`.`yh_assessment_record` (`record_id`, `elder_id`, `elder_name`, `assessment_time`, `assessment_result`, `is_least`, `is_delete`, `create_time`, `update_time`) VALUES ('2', '1', '用户1', 1721382927, '评估结果1', 0, 0, 1721382926, 1721382926);
INSERT INTO `yh_serve_test`.`yh_assessment_record` (`record_id`, `elder_id`, `elder_name`, `assessment_time`, `assessment_result`, `is_least`, `is_delete`, `create_time`, `update_time`) VALUES ('3', '1', '用户1', 1721382998, '评估结果2', 0, 0, 1721382926, 1721382926);
INSERT INTO `yh_serve_test`.`yh_assessment_record` (`record_id`, `elder_id`, `elder_name`, `assessment_time`, `assessment_result`, `is_least`, `is_delete`, `create_time`, `update_time`) VALUES ('4', '1', '用户1', 1721383990, '评估结果(最新)', 1, 0, 1721382926, 1721382926);
INSERT INTO `yh_serve_test`.`yh_assessment_record` (`record_id`, `elder_id`, `elder_name`, `assessment_time`, `assessment_result`, `is_least`, `is_delete`, `create_time`, `update_time`) VALUES ('5', '2', '用户2', 1721382927, '评估结果1', 0, 0, 1721382926, 1721382926);
INSERT INTO `yh_serve_test`.`yh_assessment_record` (`record_id`, `elder_id`, `elder_name`, `assessment_time`, `assessment_result`, `is_least`, `is_delete`, `create_time`, `update_time`) VALUES ('6', '2', '用户2', 1721382998, '评估结果2', 0, 0, 1721382926, 1721382926);
INSERT INTO `yh_serve_test`.`yh_assessment_record` (`record_id`, `elder_id`, `elder_name`, `assessment_time`, `assessment_result`, `is_least`, `is_delete`, `create_time`, `update_time`) VALUES ('7', '2', '用户2', 1721383990, '评估结果(最新)', 1, 0, 1721382926, 1721382926);
INSERT INTO `yh_serve_test`.`yh_assessment_record` (`record_id`, `elder_id`, `elder_name`, `assessment_time`, `assessment_result`, `is_least`, `is_delete`, `create_time`, `update_time`) VALUES ('8', '3', '用户3', 1731382927, '评估结果1', 0, 0, 1721382926, 1721382926);
INSERT INTO `yh_serve_test`.`yh_assessment_record` (`record_id`, `elder_id`, `elder_name`, `assessment_time`, `assessment_result`, `is_least`, `is_delete`, `create_time`, `update_time`) VALUES ('9', '3', '用户3', 1731382998, '评估结果2', 0, 0, 1721382926, 1721382926);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

二、常见错误写法

1.根据elder_id 分组后排序

select * from yh_assessment_record
group by elder_id
order by assessment_time desc 
1
2
3

得到的不符合预期的期望

2.内部排序后再分组

select t.* from (
select * from yh_assessment_record 
order by assessment_time desc  ) t 
group by elder_id 
1
2
3
4

这个排序也没有生效

3.直接在查询的时候使用max（）函数

select 
max(assessment_time),
record_id,
elder_id,
elder_name,
assessment_result
 from yh_assessment_record 
 group by elder_id
 order by assessment_time desc
1
2
3
4
5
6
7
8
9

该方法只能保证 max(assessment_time) 这一列是最新的时间，不可保证整行是最新一条数据，具体可看 assessment_result

三、常见正确的几种写法

1.补充limit 关键字

select t.* from (
select * from yh_assessment_record 
order by assessment_time desc  limit 10) t 
group by elder_id 
1
2
3
4

以下的sql执效果均是此图

这个排序会生效，但是有一个弊端这个limit 后的数量你要提前评估好，否则会造成某一个 elder_id 数据缺失

2.使用 DISTINCT 关键字

select t.* from (
select DISTINCT * from yh_assessment_record 
order by assessment_time desc  ) t 
group by elder_id 
1
2
3
4

即查出的每行记录都是唯一的，过滤相同的行记录,如果行有三个字段，怎会保证这三个字段拼接起来是唯一的

3.使用 max（）函数取最新一条

如果assessment_time 有两条相同的最大时间，则会查出两条
写法1

select * from yh_assessment_record
where assessment_time in (select max(assessment_time) from yh_assessment_record
group by elder_id) 
1
2
3

写法2

select * from yh_assessment_record  t2
where assessment_time = (select max(assessment_time) 
from yh_assessment_record t1 where t1.elder_id 
= t2.elder_id )
1
2
3
4

4.使用 NOT EXISTS 取最新一条

如果assessment_time 有两条相同的最大时间，则会查出两条

select * from yh_assessment_record t1 where NOT EXISTS 
(select 1 from yh_assessment_record t2 
where t2.assessment_time > t1.assessment_time and t1.elder_id = t2.elder_id)
1
2
3

4.1.EXISTS和NOT EXISTS用法解释
EXISTS 执行规则逻辑
1.先执行一次外部查询并缓存 （这里取到表 t1的结果集select * from yh_assessment_record t1 ）
2.遍历外部查询结果集的每一行记录，将t1.assessment_time 和 t1.elder_id 作为参数传递到子查询中作为条件进行查询
3.子查询判断最终结果是否是 ture 或者 false （有结果集就是true 否则是false），true则返回 外部表 t1中的行。
4.对于EXISTS 子查询必须是有结果集返回，对于 NOT EXISTS 子查询必须是 无结果集返回才满足条件。
eg：已用户3为例子以及这个 EXISTS 为例子

select * from yh_assessment_record t1 where  EXISTS 
(select 1 from yh_assessment_record t2 
where t2.assessment_time > t1.assessment_time and t1.elder_id = t2.elder_id )
and t1.elder_id = 3
1
2
3
4

1.先执行

select * from yh_assessment_record where elder_id = 3
1

得到结果集数据

2.开始遍历结果集的行
先遍历第一行（record_id 等于10 的数据），因为t2表中assessment_time 最大的值为 1731383990,所以 t2.assessment_time > t1.assessment_time 查出的结果集是空，返回false，record_id 等于10 这一行数据被剔除，后遍历 record_id = 8 的数据，t2表中assessment_time 最大的值为 1731383990,
t2.assessment_time > t1.assessment_time （1731383990record_id = 10这一行 > 1731382927record_id = 8这一行） 这个条件成立， record_id =8 的数据被保留，以此类推，record_id = 9 的数据也被保留。

3.最终结果集

将sql 中的 EXISTS 换成 NOT EXISTS，这二者是互斥的，且满足子查询返回结果是false 才符合条件

4.NOT EXISTS取最新一条

1.取第一条数据（record_id = 10 的数据），从t2表中遍历找数据，找到（ t2.assessment_time = 1731383990，1731382927，1731382998）的数据 均不满足 t2.assessment_time > t1.assessment_time 查出的结果集是空，返回false，所以该行满足条件，后遍历到 record_id = 8 的数据时，发现t2.assessment_time = 1731383990 数据满足t2.assessment_time > t1.assessment_time 为true，所以该行不满足，依此 record_id = 9 的数据也不满足。
所以最终返回

5.使用 HAVING 取最新一条

select t.* from (
select * from yh_assessment_record HAVING 1  order by assessment_time desc ) t
group by t.elder_id;
1
2
3

这个补充一个having 1 是让排序生效，否则取不了最新的一条
有关having 的解释
5.1 HAVING 通常与 GROUP BY 配合使用，为了删选分组后的数据
WHERE 是在 GROUP BY分组之前进行条件筛选的，而且后面不可以跟聚合函数

eg：以上面的数据表

-- 1.取评估记录大于 3 的用户信息
select elder_id,elder_name,count(*) from yh_assessment_record 
group by elder_id HAVING COUNT(*) > 3
-- 2.分组取 用户1 的信息
select elder_id,elder_name,count(*) from yh_assessment_record 
group by elder_id HAVING  elder_name = '用户1';
1
2
3
4
5
6

四、窗口函数，组内排序

mysql版本是8.0以上可以使用该函数来实现

		SELECT *
	FROM (
	   SELECT *,
	    ROW_NUMBER() OVER (PARTITION BY elder_id ORDER BY assessment_time DESC) as rn
	   FROM yh_assessment_record
	) t
	WHERE t.rn = 1;
1
2
3
4
5
6
7

五、提前在表中维护标记位（如 is_least字段）

is_least 标识了这条数据是最新一条

-- 直接取最新一条
select t2.*  from  yh_assessment_record t2  where  t2.is_least = 1 

-- 取最新一条及附属信息
select t2.*,t1.* from yh_elder  t1 -- 假设有一个 yh_elder 用户表
left join yh_assessment_record t2 on t1.elder_id = t2.elder_id  
and t2.is_least = 1 -- 在联表的时候过滤，提前锁定最新一条
1
2
3
4
5
6
7

该方法需要在代码插入记录的时候维护该字段,这样避免了后期联表多 分组和排序 比较耗时的操作

总结

1.当数据量大的时候（自测了160万数据）使用了LIMIT ，DISTINCT，HAVING 函数他们的效率（这三者的效率是差不多的）是要比 not exists 要快10倍左右（这里具体业务具体分析，毕竟表结构不一样，过滤的业务字段不一样，走的索引也有差异），使用 max和in 的组合效率是最低的，使用is_least 标记位的方法是最快的
2.回顾 exists 和 in的区别：
exists 是先查出外表，对外表做loop循环，将外表参数传递到内表子查询中，找到与之匹配的条件，有则返回true否则返回false，所以如果外表的数据量小则适合使用 exists 查询

in 则与之相反，in是先执行子查询内表信息，并把外表和内表作hash 连接，之后循环外表，找出符合条件的外表信息（这里做信息字段匹配都是在内存中操作），所以如果子查询数据量较小则可以使用 in来处理

对于 not in 和 not exists ：
not in 是全表扫描不走索引的，not exists 走索引，所以not exists 在相同的条件下是比 not in 的效率快