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

1.背景

有个业务场景需要获取每个设备最新时间的异常记录还有当前状态和部署位置，然后返回给前台渲染。记录一下写的sql以及里面一些小坑。

2.分析

首先查询涉及两张表，设备信息表和设备异常表，设备异常表需要分组之后查询每个分组最新的一条记录，然后根据设备编号关联查询设备信息表，最终将结果返回

3.建表sql以及测试数据

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');

4.查询sql

# 分组查询取每个分组最新的一条# 查询每个设备最新的异常异常记录以及该设备的地址和状态select tmp.device_id,       tmp.abnormal_msg,       tmp.create_time,       case info.state           when '0' then '在线'           when '1' then '离线' end as state,       info.addressfrom    (select d.device_id, d.abnormal_msg, d.create_time     from device_suspect d     where d.id in (select max(id) from device_suspect group by device_id)    ) tmpleft join device_info infoon info.device_id = tmp.device_idwhere state is not null;