MySQL8数据库中如何查询分组后每组中的最新一条记录

说明

mysql8 以上可以使用row_number()窗口函数，避免使用max求出最新时间后拿时间二次关联自身表

建表语句

-- test.sbuser definition

CREATE TABLE `sbuser` (
  `group` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `user_name` varchar(100) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO test.sbuser
(`group`, user_name, create_time)
VALUES('小组A', '啊啊啊', '2022-01-01 00:00:00');
INSERT INTO test.sbuser
(`group`, user_name, create_time)
VALUES('小组A', '表版本', '2022-01-02 00:00:00');
INSERT INTO test.sbuser
(`group`, user_name, create_time)
VALUES('小组A', '擦擦擦', '2022-01-03 00:00:00');
INSERT INTO test.sbuser
(`group`, user_name, create_time)
VALUES('小组B', '单独的', '2022-01-02 00:00:00');
INSERT INTO test.sbuser
(`group`, user_name, create_time)
VALUES('小组B', '嗯嗯嗯', '2022-01-04 00:00:00');

查询sql

select 
tmp.`group`,
tmp.user_name,
tmp.create_time 
from (
-- 按照group
select *,ROW_NUMBER() over(PARTITION by `group` order by create_time desc) r from sbuser
)tmp 
where tmp.r=1