在每组记录中为多个组选择最新和特定版本

Question

问题：

我有一个表格，记录 foo 中的数据行。每次更新行时，都会插入一个新行以及一个修订号。该表如下所示：

 id  rev field
1   1   test1
2   1   fsdfs
3   1   jfds
1   2   test2

注意：最后一条记录是第一行的更新版本。

是否有一种有效的方法来查询最新版本的记录和特定版本的记录？

例如，查询 rev=2 将返回第 2 行、第 3 行和第 4 行（虽然不是替换的第 1 行），而查询 rev=1 产生 rev <= 1 和如果 id 重复，则选择修订号较高的那个（记录：1、2、3）。

我不希望以迭代的方式返回结果。

原文由 orange 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

要仅获取最新版本：

 SELECT * from t t1
WHERE t1.rev =
  (SELECT max(rev) FROM t t2 WHERE t2.id = t1.id)

要获得特定修订，在这种情况下为 1（如果项目没有修订但下一个最小修订）：

 SELECT * from foo t1
WHERE t1.rev =
  (SELECT max(rev)
   FROM foo t2
   WHERE t2.id = t1.id
   AND t2.rev <= 1)

这可能不是最有效的方法，但现在我想不出更好的方法来做到这一点。

原文由 Tim 发布，翻译遵循 CC BY-SA 3.0 许可协议

Answer 2

这是一种替代解决方案，它会产生 更新 成本，但在 读取 最新数据行时效率更高，因为它避免了计算 MAX(rev) 。当您对表的子集进行批量更新时，它也可以工作。我需要这种模式来确保我可以有效地切换到通过长时间运行的批量更新更新的新数据集，而没有任何时间窗口可以看到部分更新的数据。

老化

• 将 rev 列替换为 age 列
• 使用过滤器创建当前最新数据的视图： age = 0
• 要创建数据的新版本…
• 插入：带有 age = -1 的新行—这是我运行缓慢的批处理过程。
• 更新： UPDATE table-name SET age = age + 1 用于子集中的所有行。这会将视图切换到新的最新数据 (age = 0)，并且还会在单个事务中对旧数据进行老化。
• 删除：子集中具有 age > N 的行 - 可选择清除旧数据

索引

• 用 age 然后 id 创建一个复合索引，这样视图会很好，很快，也可以用来按 id 查找。尽管此键实际上是唯一的，但当您对行进行老化时（在 UPDATE SET age=age+1 期间），它暂时不唯一，因此您需要使其不唯一，最好是聚集索引。 If you need to find all versions of a given id ordered by age , you may need an additional non-unique index on id then age .

回滚

最后……假设你今天过得很糟糕，批处理中断了。您可以通过运行以下命令快速恢复到以前的数据集版本：

• UPDATE table-name SET age = age - 1 -- 回滚一个版本
• DELETE table-name WHERE age < 0 -- 清理坏东西

现有表

假设您有一个现在需要支持老化的现有表。您可以通过首先重命名现有表来使用此模式，然后添加 age 列和索引，然后创建包含 与原始表名称相同名称 的 age = 0 条件的视图.

此策略可能会或可能不会起作用，具体取决于依赖于原始表的技术层的性质，但在许多情况下，将视图交换为表应该很好。

笔记

我建议将 age 列命名为 RowAge 以指示正在使用此模式，因为它更清楚地表明它是与数据库相关的值，并且它补充了 SQL Server 的 RowVersion 命名约定。它也不会与需要返回一个人年龄的列或视图冲突。

与其他解决方案不同，此模式适用于非 SQL Server 数据库。

如果您要更新的子集非常大，那么这可能不是一个好的解决方案，因为您的最终事务不仅会更新当前记录，还会更新该子集中记录的所有过去版本（甚至可能是整个表！）所以你最终可能会锁定桌子。

原文由 Tony O‘Hagan 发布，翻译遵循 CC BY-SA 4.0 许可协议