数据库-根据某个字段去重/分组并查询最新的一条记录

今天在工作中，遇到一个业务：根据某个字段去重查询最新的记录列表，于是建了一张测试表，并记录下两种不同类型数据库下的查询方式（postgresql/mysql），如果不想看中间博主的思路和分析，直接跳到文章末尾看总结部分即可

一、准备工作

postgresql建表语句：

DROP TABLE IF EXISTS "public"."t_group_member";
CREATE TABLE "public"."t_group_member" (
  "id" int4 NOT NULL,
  "group_id" varchar(255) COLLATE "pg_catalog"."default",
  "member_name" varchar(255) COLLATE "pg_catalog"."default",
  "join_time" timestamp(6),
  "level" varchar(255) COLLATE "pg_catalog"."default"
)
;
COMMENT ON COLUMN "public"."t_group_member"."id" IS '主键';
COMMENT ON COLUMN "public"."t_group_member"."group_id" IS '组织编号';
COMMENT ON COLUMN "public"."t_group_member"."member_name" IS '成员姓名';
COMMENT ON COLUMN "public"."t_group_member"."join_time" IS '加入时间';
COMMENT ON COLUMN "public"."t_group_member"."level" IS '等级（1，2，3）';

INSERT INTO "public"."t_group_member" VALUES (1, '001', '鸣人', '2022-05-17 17:37:56', '3');
INSERT INTO "public"."t_group_member" VALUES (2, '002', '路飞', '2022-05-17 13:35:11', '3');
INSERT INTO "public"."t_group_member" VALUES (3, '003', '孙悟空', '2022-05-17 17:50:14', '3');
INSERT INTO "public"."t_group_member" VALUES (4, '001', '自来也', '2022-05-10 17:50:46', '2');
INSERT INTO "public"."t_group_member" VALUES (5, '003', '短笛', '2022-05-09 17:51:20', '1');
INSERT INTO "public"."t_group_member" VALUES (6, '001', '我爱罗', '2022-05-10 17:52:18', '3');
INSERT INTO "public"."t_group_member" VALUES (7, '002', '索隆', '2022-05-19 17:52:39', '2');
INSERT INTO "public"."t_group_member" VALUES (8, '002', '山治', '2022-05-02 17:53:41', '2');
INSERT INTO "public"."t_group_member" VALUES (9, '003', '孙悟饭', '2022-05-08 17:55:14', '1');

mysql建表语句：

DROP TABLE IF EXISTS `t_group_member`;
CREATE TABLE `t_group_member` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `group_id` varchar(255) DEFAULT NULL,
  `member_name` varchar(255) DEFAULT NULL,
  `join_time` datetime DEFAULT NULL,
  `level` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of t_group_member
-- ----------------------------
INSERT INTO `t_group_member` VALUES ('1', '001', '鸣人', '2022-05-17 17:37:56', '3');
INSERT INTO `t_group_member` VALUES ('2', '002', '路飞', '2022-05-17 13:35:11', '3');
INSERT INTO `t_group_member` VALUES ('3', '003', '孙悟空', '2022-05-17 17:50:14', '3');
INSERT INTO `t_group_member` VALUES ('4', '001', '自来也', '2022-05-10 17:50:46', '2');
INSERT INTO `t_group_member` VALUES ('5', '003', '短笛', '2022-05-09 17:51:20', '1');
INSERT INTO `t_group_member` VALUES ('6', '001', '我爱罗', '2022-05-10 17:52:18', '3');
INSERT INTO `t_group_member` VALUES ('7', '002', '索隆', '2022-05-19 17:52:39', '2');
INSERT INTO `t_group_member` VALUES ('8', '002', '山治', '2022-05-02 17:53:41', '2');
INSERT INTO `t_group_member` VALUES ('9', '003', '孙悟饭', '2022-05-08 17:55:14', '1');

表建好之后，查看一下自己的mysql版本（这个细节很关键）

同样，查看postgresql的版本

二、需求

首先看一下建好的测试表数据

需求：查询每个小组最新加入的成员信息

（1）方式一：通过分组加排序的方式（mysql5.7.5以下）

SELECT * FROM
	(SELECT * FROM t_group_member ORDER BY join_time DESC) b 
GROUP BY group_id

这样写表面看上去没啥问题，但实际查询结果却让我感到失望

很明显，查出来的数据是不正确的，此时的解决办法是加上limit

SELECT * FROM
	(SELECT * FROM t_group_member ORDER BY join_time DESC limit 1000) b 
GROUP BY group_id

这样，就准确的查出想要的数据了

但是

同样的sql，把它放在mysql5.7.5及以上版本，就报错了,至于报错的原因，简单来讲就是mysql从5.7.5版本开始，默认完全遵守“SQL92标准”，而该标准规定：如果select后面的字段没有全部出现在group by后面，就是非法的sql语句，除非，使用any_value()函数包裹除了在group by后面出现的字段,sql如下：

SELECT
 group_id,
 ANY_VALUE(member_name),
 ANY_VALUE(join_time) 
FROM
 ( SELECT group_id, member_name, join_time FROM t_group_member ORDER BY join_time DESC ) b 
GROUP BY group_id

此时，我们也发现这个sql的弊端了：

• 如果数据量特别大，limit 1000显得就不够用了，sql查询到的数据就不对
• 该方式只适用于mysql5.7.5版本以下，即便使用了any_value()函数来避免语法问题，但这种抑制语法的方式，还是不推荐
• 该sql在postgresql上也完全不兼容

所以，该方式不推荐

（2）方式二：通过关联查询+聚合函数的方式（mysql/postgresql）

SELECT t.* FROM 
(SELECT group_id,max(join_time) as join_time FROM t_group_member GROUP BY group_id) a 
LEFT JOIN 
t_group_member t 
ON t.group_id=a.group_id where t.join_time=a.join_time

此时，我们发现，select后面的max(join_time)并没有出现在group by后面啊，这个sql会不会在mysql5.7.5以上版本也报错啊？

答案是：不会

因为除了方式一所说的使用any_value()函数来绕开“SQL92标准”语法外，还有一种更“正规合法”的语法是不会导致报错的，那便是聚合函数（max,min，sum等），虽然select后面的max(join_time)并没有出现在group
by后面，但是join_time字段使用了max聚合函数，这种情况是符合“SQL92标准”的，所以不会出现上面方式一的错误

该方式无论在mysql的高版本或者低版本，亦或者在postgresql中，均可适用，博主推荐

（3）方式三：通过row_number() over()函数实现（mysql5.7.5以上/postgresql）

语法：row_number() over(partition by 分组列 order by 排序列 desc) as 别名

 SELECT * FROM (
 	SELECT *, row_number() over(PARTITION BY group_id ORDER BY join_time DESC) rn FROM t_group_member
 ) t WHERE t.rn = 1

该方式在mysql5.7.5以下的版本并不适用，所以可根据具体情况决定是否使用

三、总结

当遇到根据某个字段分组并取出最新一条记录时，可以套用以下sql，兼容性比较可靠

SELECT t.* FROM 
(SELECT group_id,max(join_time) as join_time FROM t_group_member GROUP BY group_id) a 
LEFT JOIN 
t_group_member t 
ON t.group_id=a.group_id where t.join_time=a.join_time