orale 主子表,一对多,关联查询获取子表最新数据
主子表的最新数据
子表tableB 的 question_id 关联主表tableA 的 ID
SQL如下:
select A.ID,
A.CODE
from tableA A
left join (select t.*,
row_number() over(partition by t.question_id order by t.CREATED_TIME desc) rn
from tableB t) B on B.question_id = A.ID and B.rn = 1
同一个表的最新数据
table表的分组字段是nodeid,获取每组的最新数据
select t.*
from (select A.*,
row_number() over(partition by A.nodeidorder by t.CREATED_TIME desc) rn
from table A) t
where t.rn = 1
排序序号
row_number() over(partition by 分组列 order by 排序列 desc)