选择每天ID的最新记录-Oracle pl SQL
如何编写一条按ID每天返回最新记录的SQL语句。例.数据如下。
Id Name Comment Value DateTime
1 Tim Test 100 02/06/2020 15:05:12
2 Sue House 200 03/06/2020 08:25:01
1 Tim Test 150 02/06/2020 18:05:12
3 Doug Cars 680 10/05/2019 04:45:10
2 Sue Tennis 200 03/06/2020 10:35:15
我会得到:
Id Name Comment Value DateTime
1 Tim Test 150 02/06/2020 18:05:12
3 Doug Cars 680 10/05/2019 04:45:10
2 Sue Tennis 200 03/06/2020 10:35:15
我是否需要按最大日期时间分组的子选择查询?
解决方案
我将采用窗口函数:
select id, name, comment, value, dateTime from
(
select id, name, comment, value, dateTime
, last_value(dateTime) over( partition by id, trunc(datetime)
order by dateTime
rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) lv
)
where dateTime=lv
相关文章