如何在最大时间戳上正确执行组？

Question

我正在使用MySQL 5.7.38版本。 我有以下查询，该查询正在返回正确的示例数据，如下所示：

SELECT
   t.r_id,
   t.hostname,
   t.a_tmstamp,
   max(t.r_status) as r_status,
   max(t.message) as message,
   max(t.m_id) as 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'
   ORDER BY  a_timestamp DESC
     ) AS t 
 GROUP BY t.r_id, t.hostname, t.a_tmstamp
 ORDER BY a_tmstamp DESC;

示例数据：

+-----------+----------+-----------------------+--------------+------------+---------+
| r_id      | hostname | a_tmstamp             |  r_status    |  message   |  m_id   |
+-----------+----------------------------------+--------------+------------+---------+
| a1ffadc9  | abc01    | 6/9/2022  6:15:17 PM  | PROGRESS     |  message1  | d759df3 |
| a1ffadc9  | abc02    | 6/9/2022  6:15:17 PM  | PROGRESS     |  message2  | d759df3 |
| a1ffadc9  | abc01    | 6/9/2022  6:14:17 PM  | PROGRESS     |  null      | fdb2f19 |
| a1ffadc9  | abc02    | 6/9/2022  6:14:17 PM  | PROGRESS     |  null      | fdb2f19 |
| 246cc315  | cad01    | 6/9/2022  4:43:52 PM  | FAILED       |  message3  | f5435f0 |
| 246cc315  | cad02    | 6/9/2022  4:43:52 PM  | FAILED       |  message4  | f5435f0 |
| 258e55ac  | cad01    | 6/9/2022  4:42:52 PM  | FAILED       |  reason1   | 432ca62 |
| 258e55ac  | cad02    | 6/9/2022  4:42:52 PM  | FAILED       |  reason2   | 432ca62 |
+-----------+----------+-----------------------+--------------+------------+---------+

如何执行max（T.A_TMSTAMP）的组进行组，以便我低于输出？

所需的输出：

+-----------+----------+-----------------------+--------------+------------+---------+
| r_id      | hostname | a_tmstamp             |  r_status    |  message   |  m_id   |
+-----------+----------------------------------+--------------+------------+---------+
| a1ffadc9  | abc01    | 6/9/2022  6:15:17 PM  | PROGRESS     |  message1  | d759df3 |
| a1ffadc9  | abc02    | 6/9/2022  6:15:17 PM  | PROGRESS     |  message2  | d759df3 |
| 246cc315  | cad01    | 6/9/2022  4:43:52 PM  | FAILED       |  message3  | f5435f0 |
| 246cc315  | cad02    | 6/9/2022  4:43:52 PM  | FAILED       |  message4  | f5435f0 |
+-----------+----------+-----------------------+--------------+------------+---------+

Answer 1

您可以使用一些技巧来获取最新A_TMPSTAMP的状态/消息/M_ID：

SELECT
   t.r_id,
   t.hostname,
   max(t.a_tmstamp) max_tmstamp,
   substr(max(concat(t.a_tmstamp,t.r_status)),24) as r_status,
   substr(max(concat(t.a_tmstamp,t.message)),24) as message,
   substr(max(concat(t.a_tmstamp,t.m_id)),24) as 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'
   ORDER BY  a_timestamp DESC
     ) AS t 
 GROUP BY t.r_id, t.hostname
 ORDER BY max_tmstamp DESC;