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

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.address
from
    (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)
    ) tmp
left join device_info info
on info.device_id = tmp.device_id
where state is not null;

注意：

1.当前因为主键自增，id越大那么时间越新，所以可以用这种查询方式

2.left join on可以保留主表的所有记录，如果要在过滤主表记录需要写在where后面，on后面不会生效。

5.查询结果