从mysql数据库获取最新更新的数据
问题描述:
我需要从表中检索每个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
)