sql - 从 id A 中选择最新数据
问题描述
我有这样的数据:
ID TRANS_ID CREATED_DATE STATUS
----------------------------------------------
1 AA 2017-05-19 02:00:00 WAITING
2 AA 2017-05-20 02:00:00 IN_PROCESS
3 BB 2017-05-19 02:00:00 WAITING
4 CC 2017-05-19 02:00:00 WAITING
5 CC 2017-05-20 02:00:00 IN_PROCESS
我想在表格视图中显示数据,如下所示:
ID TRANS_ID CREATED_DATE STATUS
----------------------------------------------
2 AA 2017-05-20 02:00:00 IN_PROCESS
3 BB 2017-05-19 02:00:00 WAITING
5 CC 2017-05-20 02:00:00 IN_PROCESS
我从每个获取最新数据trans_id
并运行此查询,但它不起作用
select id, max(created_date), trans_id, status
from table_a
group by a.transaction_id
解决方案
一种选择是使用相关子查询进行过滤:
select t.*
from mytable t
where t.created_date = (
select max(t1.created_date) from mytable t1 where t1.trans_id = t.trans_id
)
或者,您可以使用窗口函数:
select id, trans_id, created_date, status
from (
select t.*, rank() over(partition by trans_id order by created_date desc) rn
from mytable t
) t
where rn = 1
这允许顶级关系,如果有的话。如果你不想打领带,你可以使用row_number()
代替rank()
.