MySQL 5.7如何按max时间戳正确执行GROUP BY分组查询

核心逻辑：先取出每个r_id分组下对应的最大a_tmstamp值，再关联回原明细数据，筛选出时间戳完全匹配的记录即可。不要直接对多字段混用max()聚合，否则会出现不同字段最大值来自不同行的错位问题；MySQL 5.7不支持窗口函数，用子查询关联的方式兼容性最好。

改好的可直接执行的SQL如下：

SELECT
   t.r_id,
   t.hostname,
   t.a_tmstamp,
   t.r_status,
   t.message,
   t.m_id
FROM (
-- 原明细查询逻辑保留
SELECT
   audit.r_id as r_id,
   nhost.host_name as hostname,
   meta.r_status as r_status,   
   meta.step as step,
   meta.id as m_id,
   meta.message as message,
   audit.a_timestamp as a_tmstamp,
   npr.nas_provider as nas_provider             
FROM audit
   INNER JOIN npr ON npr.nr_id = audit.r_id
   AND audit.a_timestamp BETWEEN now() - interval 30 DAY AND now()
   INNER JOIN nhost ON audit.r_id = nhost.nr_id      
   INNER JOIN meta ON audit.audit_m_id = meta.id                                  
   INNER JOIN nprw ON npr.pw_id = nprw.id   
   AND nprw.ap_step = meta.step                
   WHERE meta.r_status regexp 'FAIL'
) AS t 
INNER JOIN (
-- 先聚合算出每个r_id对应的最新时间戳
SELECT 
   audit.r_id, 
   max(audit.a_timestamp) as max_tmstamp
FROM audit
   INNER JOIN npr ON npr.nr_id = audit.r_id
   AND audit.a_timestamp BETWEEN now() - interval 30 DAY AND now()
   INNER JOIN meta ON audit.audit_m_id = meta.id
   INNER JOIN nprw ON npr.pw_id = nprw.id AND nprw.ap_step = meta.step
WHERE meta.r_status regexp 'FAIL'
GROUP BY audit.r_id
) AS t_max ON t.r_id = t_max.r_id AND t.a_tmstamp = t_max.max_tmstamp
ORDER BY t.a_tmstamp DESC;

补充说明 #

• 如果你的分组维度是(r_id, hostname)（即每个主机对应的每个r_id单独取最新记录），只需要把上面t_max子查询的分组字段改成GROUP BY audit.r_id, nhost.host_name，关联条件同步加上t.hostname = t_max.hostname即可。
• 去掉了原明细子查询里的ORDER BY：MySQL 5.7中子查询不带LIMIT时，内部排序会被优化器直接忽略，写了不会生效还会增加额外排序开销。
• 该写法返回的r_status/message/m_id都是最新时间点对应行的原生值，不会出现字段值错位问题，不需要额外用max()做聚合。
• 如果需要追加过滤规则（比如只保留最新状态为FAILED的记录、排除特定r_id），直接在最外层查询追加WHERE条件即可。

内容的提问来源于stack exchange，提问作者meallhour