“按版本号的最新值聚合归档”指对每个业务主键（如product_id）取version最大且严格递增的记录作为当前有效快照；因version非时间戳且需确保唯一性，应使用ROW_NUMBER()窗口函数按PARTITION BY product_id和ORDER BY version DESC取rn=1行，而非MAX(version)聚合或DISTINCT ON。
什么是“按版本号的最新值聚合归档”
这不是标准 SQL 语义，而是业务归档常见需求：一张表存了多次变更（比如 product_id、price、version、updated_at），要对每个 product_id 取 version 最大的那条记录，作为该实体当前有效快照。关键点在于——version 是数值型且严格递增，不是时间戳，所以不能靠 MAX(updated_at) 替代。
用窗口函数 ROW_NUMBER() 筛选每组最大版本
这是最通用、可读性最强的做法，兼容 PostgreSQL、SQL Server、Oracle、MySQL 8.0+、Trino 等主流引擎。核心逻辑是：先按 product_id 分组，再按 version 降序排序，取序号为 1 的行。
SELECT product_id, price, version, updated_at
FROM (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY product_id
ORDER BY version DESC
) AS rn
FROM product_history
) t
WHERE rn = 1;
- 必须用
ORDER BY version DESC，升序会拿到最早版本 -
PARTITION BY字段要和业务主键一致；若需按复合主键（如(product_id, store_id)）归档，这里也要写全 - 如果存在相同
version的多条记录（比如并发写入未加锁），ROW_NUMBER()会任意选一条；此时应改用RANK()+ 额外去重逻辑，或前置清洗
用 GROUP BY + 关联子查询的兼容写法（适配老版本 MySQL）
MySQL 5.7 或 SQLite 等不支持窗口函数的场景，只能靠两层查询：先查出每个 product_id 的最大 version，再用 JOIN 拿到完整记录。
SELECT h1.product_id, h1.price, h1.version, h1.updated_at FROM product_history h1 INNER JOIN ( SELECT product_id, MAX(version) AS max_version FROM product_history GROUP BY product_id ) h2 ON h1.product_id = h2.product_id AND h1.version = h2.max_version;
- 注意：如果同一
product_id有多个相同最大version的记录，这个写法会返回全部，不是“一条”，和ROW_NUMBER()行为不同 - 性能上，子查询的
GROUP BY如果没在(product_id, version)上建联合索引，容易慢 - 别用
IN写法（如WHERE (product_id, version) IN (...)），某些旧版 MySQL 不支持多列IN
为什么不用 DISTINCT ON（PostgreSQL 专属）？
PostgreSQL 支持 DISTINCT ON，写起来更短：
SELECT DISTINCT ON (product_id) product_id, price, version, updated_at FROM product_history ORDER BY product_id, version DESC;
但它有几个硬限制：
– 必须出现在 ORDER BY 开头（product_id 先于 version）
– 仅 PostgreSQL 支持，跨数据库迁移时直接报错
– 如果 ORDER BY 后字段没覆盖所有 SELECT 列，可能触发非确定性结果（比如漏掉 updated_at 排序）
除非你确定只跑在 PG 且团队熟悉该语法，否则优先选 ROW_NUMBER() 方案。
真正容易被忽略的是：归档结果是否要求强一致性？如果 product_history 正在被高频写入，上述任何查询都可能读到部分更新中的中间状态。生产归档建议加事务隔离（如 REPEATABLE READ）或在低峰期执行，而不是依赖单条 SQL 自动解决并发问题。
