group by 分组取时间最新的一条数据的实战经验

码农界赵子龙

于 2025-03-17 19:06:49 发布

阅读量581

点赞数 4

分类专栏：数据库文章标签： java 数据库 linux

本文链接：https://blog.youkuaiyun.com/wind1_rain/article/details/146323149

版权

数据库专栏收录该内容

7 篇文章

订阅专栏

一、创建测试表：

CREATE TABLE `t_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID主键',
  `name` varchar(100) NOT NULL DEFAULT '' COMMENT '名称',
  `userId` int(11) DEFAULT NULL COMMENT 'userId',
  `bu` varchar(4) NOT NULL DEFAULT '' COMMENT 'bu',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_create_time` (`create_time`),
  KEY `idx_update_time` (`update_time`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COMMENT='测试调整表';

二、插入测试数据：

INSERT INTO `t_test` (`name`, `userId`, `bu`, `create_time`, `update_time`)
VALUES
	('小明', '1', '1', '2025-03-13 19:45:54', '2025-03-13 19:45:57'),
	('小明', '2', '1', '2025-03-13 19:46:09', '2025-03-13 19:46:09'),
	('小李', '212', '2', '2025-03-13 19:46:19', '2025-03-13 19:46:22'),
	('火女', '12', '1', '2025-03-13 19:46:30', '2025-03-13 19:46:30'),
	('白牛', '22', '1', '2025-03-13 19:46:36', '2025-03-13 19:46:46'),
	('影魔', '21', '1', '2025-03-13 19:46:37', '2025-03-13 19:46:45');

三、查询全部：

select * from t_test

四、单表查询：

4.1 使用group by 分组，不添加排序。使用name字段：

select * from t_test  group by name

可以看到 name 重复的包括：小明和影魔。现在默认展示的两条都是id 较小的一条。

可以看出单表group by 返回的是每组的第一条数据

4.2 使用group by 分组，添加排序。使用name字段：

select * from t_test   group by name order by id desc;

可以看出排序是在分组结果之后排序的。对分组没有影响。

4.3 结论：单表查询无法获取分组中最新的一条

五、尝试使用复杂查询：

5.1 子查询先排序后分组,结果。

select t1.* from 
(select * from t_test  order by id desc ) t1
 group by t1.name;

查询结果，分组后依旧没有获取最新的一条。

5.2 查看无法排序的原因：

执行sql,两行sql 同时执行哦：

explain  select t1.* from 
(select * from t_test  order by id desc) t1
 group by t1.name;
 show warnings;

以看到两条语句被合并成了一条，子查询的排序没了。

5.3 正常关联查询也无法获取分组最新一条的结论：

猜测应该是sql 优化器，优化了sql.

5.4 解决方法一：排序使用limit 限制。后分组。

select t1.* from 
(select * from t_test  order by id desc  limit 100) t1
 group by t1.name;

可以看出这里分组后，取的是分组内最新的一条。

5.5 解决方法二：使用 distinct 在子查询中去重后。分组

 select t1.* from 

(select distinct  * from todo.t_test  order by id desc ) t1

 group by t1.name;

5.6 解决方法三：使用max,取出分组中最大的id. 关联查询（性能最优）

使用限制：必须是自增id. Id 和创建时间排序一致。

 select t1.* from  todo.t_test  t1
         inner join 
(select max(id) as id  from todo.t_test   group by name ) as t2  on t1.id = t2.id