在mysql中使用group by子句选择带有日期和时间列的最新记录

Question

我有一个表，我必须根据每个检查点的日期和时间列从该表中选择最新的行

我尝试了以下查询，但没有返回每个检查点的最新数据。

SELECT checkpost_id,current_rate,date,time FROM revisionrates 
WHERE date IN (SELECT max(date) FROM revisionrates GROUP BY checkpost_id)

预期输出为

Answer 1

您可以使用窗口函数:

select rr.*
from (select rr.*,
             row_number() over (partition by checkpost_id order by date desc, time desc) as seqnum
      from revisionrates rr
     ) rr
where seqnum = 1;

这需要MySQL8.0。在MySQL的早期版本中，这有点棘手，但有一种方法使用元组

select rr.*
from rr
where (date, time) in (select rr2.date, rr2.time
                       from revisionrates rr2
                       where rr2.checkpoint_id = rr.checkpoint_id
                       order by rr2.date desc, rr2.time desc
                       limit 1
                      );