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

于 2024-07-25 19:14:08 发布
阅读量541 收藏 14
点赞数 7
文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_43582896/article/details/140642932
版权

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

文章目录

前言

在日常业务开发中涉及到报表的统计，或者取某一个用户最新的一条记录并对他进行业务分析等等，现总结了常见的几种方法去取这这一类数据 这里以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.根据elder_id 分组后排序

select * from yh_assessment_record
group by elder_id
order by assessment_time desc

得到的不符合预期的期望
在这里插入图片描述

2.内部排序后再分组

select t.* from (
select * from yh_assessment_record 
order by assessment_time desc  ) t 
group by elder_id

这个排序也没有生效
在这里插入图片描述

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

在这里插入图片描述
该方法只能保证 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

以下的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

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)

写法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 )

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)

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.先执行

select * from yh_assessment_record where elder_id = 3

得到结果集数据
在这里插入图片描述
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;

这个补充一个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';

四、窗口函数，组内排序

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;

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

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

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 -- 在联表的时候过滤，提前锁定最新一条

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

是小章啊
关注
  • 7
    点赞
  • 踩
  • 14
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值