mysql-group分组之后取每组最新的一条记录

create table device_suspect ( id int auto_increment comment '主键id自增' primary key, device_id varchar(20) not null comment '设备编号', abnormal_msg varchar(30) not null comment '异常信息', create_time datetime not null comment '创建时间' ); create table device_info ( device_id bigint(12) not null comment '设备编号' primary key, state int null comment '设备在线状态', address varchar(50) null comment '部署地址' ); insert into lgw.device_info (device_id, state, address) values (10001, null, 'zg'), (10002, 1, 'mg'), (10003, 0, 'rb'), (10004, 1, 'hg'); insert into lgw.device_suspect (id, device_id, abnormal_msg, create_time) values (1, '10001', '设备cpu占用过高', '2023-01-03 16:54:31'), (2, '10002', '设备磁盘占用过高', '2023-01-03 16:54:31'), (3, '10003', '设备内存占用过高', '2023-01-03 16:54:31'), (4, '10004', '设备温度过高', '2023-01-03 16:54:31'), (5, '10001', '设备cpu占用过高', '2023-02-01 16:54:31'), (6, '10002', '设备磁盘占用过高', '2023-02-01 16:54:31'), (7, '10003', '设备内存占用过高', '2023-02-01 16:54:31'), (8, '10004', '设备温度过高', '2023-02-01 16:54:31');