从mysql数据库获取最新更新的数据 [英] Get a most recent updated data from mysql database

问题描述

我需要从表中检索每个ID的一系列数据。列的数据或行值必须是第二个最近的值。例如，我有
表-如下所示的 estimate_record

I need to retrieve a series of data for each id from a table. The data or the row value for the column need to be such that it is the second most recent value. For instance, I have Table- estimate_record as following

Id        value   last_updated
1         210     10/2018
1         205     11/2018
1         215     12/2018   -- current

I需要获取特定ID = 1的205

I need to get the 205 for that particular id =1

我使用了 Max（value），但是它获取的是 215 这是不对的。

I used Max(value), but it's getting 215 which is not right.

推荐答案

如果运行的是MySQL 8.0，则可以执行此操作带有窗口函数：

If you are running MySQL 8.0, you can do this with window functions:

select *
from (
    select t.*, row_number() over(partition by id order by last_updated desc) rn
    from mytable t
) t
where rn = 2

在早期版本中，一个选项使用子查询：

In earlier versions, one option uses a subquery:

select t.*
from mytable t
where t.last_updated = (
    select t1.last_updated
    from mytable t1
    where t1.id = t.id
    order by t1.last_updated desc
    limit 1 offset 1
)

这篇关于从mysql数据库获取最新更新的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！