多表关联查询：按时间戳获取唯一编码最新符合条件记录

表关联与需求说明 #

• 涉及三张表：item、unique_item_code、unique_hist
• 关联规则：
  • item 和 unique_item_code 通过 tid、sid、revision 关联，每个item对应的唯一编码存储在unique_item_code表中
  • unique_item_code 和 unique_hist 通过 unique_code 关联，unique_hist 存储该编码的多版本详情
• 查询需求：取出item和unique_item_code的全部数据，同时为每个unique_code筛选出**last_update_time小于对应item表flag_update_time的最新一条版本记录**

你尝试的错误SQL及问题 #

第一个SQL #

select it.*, 
       hist.unique_code, 
       hist.priority, 
       hist.class, 
       hist.type, 
       hist.last_updated_time
from item it 
join unique_item_code uniq 
  on it.tid = uniq.tid 
 and it.sid = uniq.sid and it.revision = uniq.revision 
join unique_hist hist 
  on uniq.unique_code = hist.unique_code 
where it.flag_updated_time < hist.last_updated_time

问题：会返回所有满足时间条件的unique_hist记录，无法保证每个unique_code只保留最新的那一条。

第二个SQL #

select it.*, 
       hist.unique_code, 
       hist.priority, 
       hist.class, 
       hist.type, 
       hist.last_updated_time 
from item it 
join unique_item_code uniq 
  on it.tid = uniq.tid 
 and it.sid = uniq.sid and it.revision = uniq.revision 
join (select top 1 
      from unique_hist hist 
      where it.flag_updated_time < last_updated_time 
        and uniq.unique_code = hist.unique_code)

问题：

1. 语法错误：子查询select top 1后未指定要查询的字段
2. 逻辑错误：子查询直接引用外部表字段，且无排序规则，无法保证取到最新的目标记录

正确解决方案 #

方案1：使用窗口函数（通用大多数数据库） #

通过ROW_NUMBER()窗口函数按unique_code分组，按last_updated_time倒序排序，取每组第一条（最新记录）：

select 
    it.*, 
    uniq.*, 
    hist.unique_code, 
    hist.priority, 
    hist.class, 
    hist.type, 
    hist.last_updated_time
from item it
inner join unique_item_code uniq 
    on it.tid = uniq.tid 
    and it.sid = uniq.sid 
    and it.revision = uniq.revision
inner join (
    select 
        uh.*,
        ROW_NUMBER() OVER (PARTITION BY uh.unique_code ORDER BY uh.last_updated_time DESC) AS row_num
    from unique_hist uh
    inner join unique_item_code uniq_sub 
        on uh.unique_code = uniq_sub.unique_code
    inner join item it_sub 
        on uniq_sub.tid = it_sub.tid 
        and uniq_sub.sid = it_sub.sid 
        and uniq_sub.revision = it_sub.revision
    where uh.last_updated_time < it_sub.flag_updated_time
) hist 
    on uniq.unique_code = hist.unique_code 
    and hist.row_num = 1

方案2：使用Cross Apply（SQL Server）/ Lateral Join（PostgreSQL） #

这种写法更直观，直接为每个unique_code筛选符合条件的最新记录：

select 
    it.*, 
    uniq.*, 
    hist.*
from item it
inner join unique_item_code uniq 
    on it.tid = uniq.tid 
    and it.sid = uniq.sid 
    and it.revision = uniq.revision
cross apply (
    select top 1 
        uh.unique_code, uh.priority, uh.class, uh.type, uh.last_updated_time
    from unique_hist uh
    where uh.unique_code = uniq.unique_code
      and uh.last_updated_time < it.flag_updated_time
    order by uh.last_updated_time desc
) hist

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