不能直接用 max(timestamp) 在 join 条件里，因为 sql 标准禁止在 on 子句中使用聚合函数，且 max() 是全表聚合，无法实现“每 id 各自最新”的关联需求。
为什么不能直接用 MAX(timestamp) 在 JOIN 条件里
因为 SQL 标准中，JOIN 的 ON 子句不允许出现聚合函数（如 MAX()），除非它在子查询或 CTE 中预先计算好。直接写 ON a.id = b.id AND b.timestamp = MAX(b.timestamp) 会报错：ERROR: aggregate functions are not allowed in JOIN conditions。
更关键的是，即使语法允许，MAX() 是全表聚合，无法按主表每行“各自找自己最新的历史记录”——这需要的是“关联驱动的 Top-1”，不是全局最大值。
- 错误写法：
JOIN history_table b ON a.id = b.id AND b.timestamp = (SELECT MAX(timestamp) FROM history_table)（返回全局最新一行，所有主表行都连到同一行） - 正确思路：对每个
id，先算出其对应的最大timestamp，再用这个结果去匹配
用窗口函数 ROW_NUMBER() 获取每 ID 最新版本
这是最常用、语义清晰、且支持多字段去重（比如时间相同时按 version_id 排序）的方式。核心是给每个 id 分组内按时间倒序编号，取 rn = 1 的记录。
SELECT a.*, b.*
FROM main_table a
JOIN (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY timestamp DESC, version_id DESC) AS rn
FROM history_table
) b ON a.id = b.id AND b.rn = 1;
- 必须用
PARTITION BY id，否则就是全表排序，失去“每 ID 各自最新”的意义 -
ORDER BY timestamp DESC是基础，但建议补上次级排序（如version_id DESC），避免时间戳重复时结果不确定 - MySQL 8.0+、PostgreSQL、SQL Server、Oracle 都支持；SQLite 3.25+ 也支持
用相关子查询 + WHERE timestamp = (SELECT MAX(...)) 的陷阱
这种写法看似直观，但容易因数据倾斜或 NULL 值导致漏关联。它适用于小表或已知 timestamp 非空且唯一性有保障的场景。
SELECT a.*, b.* FROM main_table a JOIN history_table b ON a.id = b.id WHERE b.timestamp = ( SELECT MAX(b2.timestamp) FROM history_table b2 WHERE b2.id = a.id );
- 如果某个
a.id在history_table中不存在记录，整个a行会被过滤掉（JOIN变成INNER）；改用LEFT JOIN+ 子查询放 ON 里更安全，但部分数据库不支持 ON 中的子查询 - 若
timestamp为NULL，MAX()返回NULL，而NULL = NULL结果为UNKNOWN，该行不会被选中——需提前WHERE timestamp IS NOT NULL - 性能上，对每个
a.id执行一次子查询，N×M 复杂度，大表慎用
当历史表极大时，如何避免全表扫描
无论用窗口函数还是子查询，如果没有索引，每次找某 id 的最新 timestamp 都可能触发全表扫描。必须建复合索引：
CREATE INDEX idx_history_id_ts ON history_table (id, timestamp DESC);
- 顺序很重要：
id必须在前（用于等值过滤），timestamp DESC在后（用于范围/排序） - 如果还依赖
version_id去重，可扩展为(id, timestamp DESC, version_id DESC) - PostgreSQL 中还可考虑部分索引（如只索引
status = 'active'的记录），进一步缩小范围
没索引时，窗口函数方案和子查询方案性能差异不大，都会慢；加了索引后，两者都能利用索引快速定位每个 id 的最新块。
