mysql 分组查询最新一条数据

臭大佬 2022-06-21 12:05:10 638
MYSQL 
简介 mysql 分组查询最新一条数据

需求

下面是一张日志表

CREATE TABLE `user_audit_log` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'user表主键',
  `status` tinyint(2) NOT NULL COMMENT '审核状态1: 通过 2:不通过',
  `reason` varchar(2048) NOT NULL DEFAULT '' COMMENT '审核不通过原因',
  `auditor_id` int(10) unsigned NOT NULL COMMENT 'sys_users表主键',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_uid` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=235 DEFAULT CHARSET=utf8mb4 COMMENT='用户入驻审核记录表';

现在要查询每个用户状态为3的,最新一条数据;

可能我们第一时间想到的就是,先对id进行倒叙,然后group一下 user_id,sql如下:

SELECT `id`, `user_id`, `status`, `reason`  FROM `user_audit_log`  WHERE `status` = 3  GROUP BY `user_id` ORDER BY `id` DESC

但是,当把分组去掉的时候,你会发现,其实得到的不是最新一条的记录

SELECT `id`, `user_id`, `status`, `reason`  FROM `user_audit_log`  WHERE `status` = 3  ORDER BY `id` DESC

截图中user_id=53的数据,reason字段最新记录的值是会一样吗,但分组后的却不是,说明这样是有问题的。

这是因为mysql先分组再排序,这边排序是对已经分组过滤掉的数据进行排序的,而默认分组取的是每个分组的第一条,所以,结果不是我们想要的。

因为 user_id=53 的数据会比较多,我们在上面的基础上,再加一个条件 user_id=53 来验证结果。下面是user_id=53的倒叙数据。

既然分组后排序不起作用,那我们先进行倒叙,再把数据进行分组,这样取得第一条就是我们想要的,

SELECT
    * 
FROM
    ( SELECT `id`, `user_id`, `status`, `reason` FROM `user_audit_log` WHERE `status` = 3 AND `user_id` = 53 ORDER BY `id` DESC ) AS t 
GROUP BY
    t.user_id 
ORDER BY
    user_id DESC,
    id DESC

!

从结果上看,并不是我们想要的,
我们调整order字段,效果也是一样的

SELECT
    * 
FROM
    ( SELECT `id`, `user_id`, `status`, `reason` FROM `user_audit_log` WHERE `status` = 3 AND `user_id` = 53 ORDER BY `id` DESC ) AS t 
GROUP BY
    t.user_id 
ORDER BY
    id DESC,
    user_id DESC

这是因为当子查询的order by语句后面没有limit关键字时,数据库会自动优化,即忽略order by语句。因此只需要添加limit关键字即可。

FROM
    ( SELECT `id`, `user_id`, `status`, `reason` FROM `user_audit_log` WHERE `status` = 3  ORDER BY `id` DESC LIMIT 1000 ) AS t 
GROUP BY
    t.user_id 
ORDER BY
    user_id DESC,
    id DESC

这次就是我们想要的结果了。

总结

group by先于order by执行;
当子查询的order by语句后面没有limit关键字时,数据库会自动优化,即忽略order by语句;