如何投影最后插入的子项？优化SQL查询获取最新插入记录

要拿到vehicule_request_id = 10133对应的最新插入记录，我给你推荐几种实用的解决办法：

方法1：ORDER BY + LIMIT（最简单直接） #

这是处理单条ID最快捷的方式，直接对结果按inserted_at降序排序，只取第一条即可：

select 
  vrs.label as label1, 
  vrhs.label as label2, 
  vrh.inserted_at 
from vehicule_request vr 
inner join vehicule_request_history vrh on vr.vehicule_request_id = vrh.vehicule_request_id 
inner join vehicule_request_status vrs on vr.vehicule_request_status_id = vrs.vehicule_request_status_id 
inner join vehicule_request_status vrhs on vrh.vehicule_request_status_id = vrhs.vehicule_request_status_id 
where vrh.vehicule_request_id = 10133
order by vrh.inserted_at desc
limit 1;

适用场景：只需要查询单个vehicule_request_id的最新记录，语法简洁，执行效率高（如果inserted_at字段有索引的话会更快）。

方法2：窗口函数ROW_NUMBER()（适合批量场景） #

如果以后需要同时查询多个vehicule_request_id的最新记录，用窗口函数会更灵活。我们可以给每个请求的历史记录按插入时间降序编号，然后取编号为1的记录：

with ranked_history as (
  select 
    vrs.label as label1, 
    vrhs.label as label2, 
    vrh.inserted_at,
    row_number() over (partition by vrh.vehicule_request_id order by vrh.inserted_at desc) as rn
  from vehicule_request vr 
  inner join vehicule_request_history vrh on vr.vehicule_request_id = vrh.vehicule_request_id 
  inner join vehicule_request_status vrs on vr.vehicule_request_status_id = vrs.vehicule_request_status_id 
  inner join vehicule_request_status vrhs on vrh.vehicule_request_status_id = vrhs.vehicule_request_status_id 
)
select label1, label2, inserted_at
from ranked_history
where vehicule_request_id = 10133 and rn = 1;

适用场景：需要批量处理多个请求的最新记录，扩展性强，不会因为新增查询ID而大幅修改逻辑。

方法3：子查询匹配最大插入时间 #

通过子查询先找到目标请求的最大inserted_at值，再关联原查询获取对应记录：

select 
  vrs.label as label1, 
  vrhs.label as label2, 
  vrh.inserted_at 
from vehicule_request vr 
inner join vehicule_request_history vrh on vr.vehicule_request_id = vrh.vehicule_request_id 
inner join vehicule_request_status vrs on vr.vehicule_request_status_id = vrs.vehicule_request_status_id 
inner join vehicule_request_status vrhs on vrh.vehicule_request_status_id = vrhs.vehicule_request_status_id 
where vrh.vehicule_request_id = 10133
and vrh.inserted_at = (
  select max(inserted_at) 
  from vehicule_request_history 
  where vehicule_request_id = 10133
);

适用场景：当vehicule_request_history表中(vehicule_request_id, inserted_at)有复合索引时，这种方法的性能会非常出色，而且逻辑直观易懂。

内容的提问来源于stack exchange，提问作者Stephane