【MySql】group by分组查询最新一条数据

最新推荐文章于 2024-07-05 17:52:41 发布

望星瑜

最新推荐文章于 2024-07-05 17:52:41 发布

阅读量9.7k

点赞数 5

分类专栏： MySql 文章标签： mysql

本文链接：https://blog.csdn.net/Purepil/article/details/115979079

版权

MySql 专栏收录该内容

1 篇文章 1 订阅

订阅专栏

一、问题场景

在一个类似商城的系统中，一般都会有专门的表用于记录用户的商品浏览数据。而现在需要你获取每个用户的最新一条浏览数据。
假设表结构和部分数据如下：

表结构和数据示例
假设表名为t_user_goods_browse_record。
record_id为浏览记录表主键，user_id为用户表主键，goods_id为商品表主键，browse_time为浏览时间。
表结构和数据SQL如下：

DROP TABLE IF EXISTS `t_user_browse_goods_record`;
CREATE TABLE `t_user_browse_goods_record`  (
  `record_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NULL DEFAULT NULL,
  `goods_id` int(11) NULL DEFAULT NULL,
  `browse_time` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`record_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 20 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of t_user_browse_goods_record
-- ----------------------------
INSERT INTO `t_user_browse_goods_record` VALUES (1, 1, 1, '2021-04-23 19:08:00');
INSERT INTO `t_user_browse_goods_record` VALUES (2, 1, 1, '2021-04-23 19:09:00');
INSERT INTO `t_user_browse_goods_record` VALUES (3, 2, 1, '2021-04-23 19:10:00');
INSERT INTO `t_user_browse_goods_record` VALUES (4, 1, 2, '2021-04-23 19:11:00');
INSERT INTO `t_user_browse_goods_record` VALUES (5, 3, 3, '2021-04-23 19:12:00');
INSERT INTO `t_user_browse_goods_record` VALUES (6, 3, 3, '2021-04-23 19:13:00');
INSERT INTO `t_user_browse_goods_record` VALUES (7, 1, 4, '2021-04-23 19:15:00');
INSERT INTO `t_user_browse_goods_record` VALUES (8, 2, 1, '2021-04-23 19:16:00');
INSERT INTO `t_user_browse_goods_record` VALUES (9, 1, 2, '2021-04-23 19:17:00');
INSERT INTO `t_user_browse_goods_record` VALUES (10, 1, 3, '2021-04-23 19:18:00');
INSERT INTO `t_user_browse_goods_record` VALUES (11, 2, 4, '2021-04-23 19:19:00');
INSERT INTO `t_user_browse_goods_record` VALUES (12, 3, 4, '2021-04-23 19:20:00');
INSERT INTO `t_user_browse_goods_record` VALUES (13, 1, 1, '2021-04-23 19:21:00');
INSERT INTO `t_user_browse_goods_record` VALUES (14, 1, 1, '2021-04-23 19:22:00');
INSERT INTO `t_user_browse_goods_record` VALUES (15, 2, 2, '2021-04-23 19:23:00');
INSERT INTO `t_user_browse_goods_record` VALUES (16, 3, 2, '2021-04-23 19:24:00');
INSERT INTO `t_user_browse_goods_record` VALUES (17, 2, 2, '2021-04-23 19:25:00');
INSERT INTO `t_user_browse_goods_record` VALUES (18, 3, 3, '2021-04-23 19:26:00');
INSERT INTO `t_user_browse_goods_record` VALUES (19, 1, 3, '2021-04-23 19:27:00');

二、尝试过程

1、直接使用GROUP BY(user_id)查询。会查询出每个用户最初始的一条数据，并不会查询到最新的一条数据。

SELECT 
	record_id, user_id, goods_id, browse_time 
FROM 
	t_user_browse_goods_record 
GROUP BY USER_ID

查询结果1
直接使用GROUP BY(user_id)查询会查出每个用户最初始的一条数据，并不会查询到最新的一条数据。

2、在子查询中倒序查询，再从子查询的结果中查询。

SELECT
	t.record_id, t.user_id, t.goods_id, t.browse_time 
FROM
(
	SELECT * FROM t_user_browse_goods_record ORDER BY browse_time DESC
) t 
GROUP BY
	t.user_id

用此语句查询时，得到的结果与上面得到的结果是一样的。

3、使用MAX(browse_time)可以获取到最新的一条浏览的时间。

SELECT
	record_id, user_id, goods_id, MAX(browse_time)
FROM
	t_user_browse_goods_record 
GROUP BY
	user_id

查询结果2

使用此语句，确实能够在浏览时间上获取到用户最新一条浏览数据的时间，但此时的goods_id仍然是最早的一条数据的goods_id，并没有得到想要的结果；
虽然没有得到想要的结果，但已知用户最新一条浏览数据的浏览时间，即可以此作为约束查询到用户最新的一条浏览记录。

4、使用MAX(browse_time)和user_id作为条件约束进行表连接查询

SELECT
	l.record_id, l.user_id, l.goods_id, l.browse_time 
FROM
	t_user_browse_goods_record l
RIGHT JOIN 
( 
	SELECT MAX( browse_time ) browse_time 
	FROM t_user_browse_goods_record 
	GROUP BY user_id
) r 
ON l.browse_time = r.browse_time

查询结果3
显然，这样得到的数据就是我们想要的。
如果想要按照浏览时间倒序排序，在最后加上ORDER BY l.browse_time DESC可以得到想要的结果

SELECT
	l.record_id, l.user_id, l.goods_id, l.browse_time 
FROM
	t_user_browse_goods_record l
RIGHT JOIN 
( 
	SELECT MAX( browse_time ) browse_time 
	FROM t_user_browse_goods_record 
	GROUP BY user_id
) r
ON l.browse_time = r.browse_time
ORDER BY l.browse_time DESC

而在右连接的结果中使用ORDER BY(browse_time) DESC，最后的结果时间却仍然是时间正序的。

SELECT
	l.record_id, l.user_id, l.goods_id, l.browse_time 
FROM
	t_user_browse_goods_record l
RIGHT JOIN 
( 
	SELECT MAX( browse_time ) browse_time 
	FROM t_user_browse_goods_record 
	GROUP BY user_id
	ORDER BY browse_time DESC
) r 
ON l.browse_time = r.browse_time

三、参考和相关

1、 MySQL分组查询每组最新的一条数据(通俗易懂)

在搜索相关问题时，有相当一部分给出的是上面2.2中的结果。而这篇文章则说明了这种查询方式失效的原因：

在mysql5.7的时候，子查询的排序已经变为无效。

最后他的解决方法也和我类似。

2、解决MySQL子查询排序失效的问题

这篇文章提出了如何避免order by失效的解决方法：

当子查询的order by语句后面没有limit关键字时，数据库会自动优化，即忽略order by语句。因此只需要添加limit关键字即可。

SELECT
	t.record_id, t.user_id, t.goods_id, t.browse_time 
FROM
(
	SELECT * FROM t_user_browse_goods_record ORDER BY browse_time DESC LIMIT 1000
) t 
GROUP BY
	t.user_id

使用LIMIT关键字明显不够灵活。
他的另一种方式也是用MAX()函数取最大值，但不是用的表连接。
使用他的方式对于以上例子为：

SELECT
	* 
FROM
	t_user_browse_goods_record,
( 
	SELECT max( browse_time ) AS time 
	FROM t_user_browse_goods_record GROUP BY user_id 
) t 
WHERE
	t.time = t_user_browse_goods_record.browse_time 
ORDER BY
	browse_time DESC;