为多个组选择每组记录中的最新和特定版本 [英] Selecting most recent and specific version in each group of records, for multiple groups

问题描述

问题:
我有一个表，记录 foo 中的数据行.每次更新行时，都会插入一个新行以及修订号.表格看起来像:

The problem:
I have a table that records data rows in foo. Each time the row is updated, a new row is inserted along with a revision number. The table looks like:

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

请注意，表中的最后一条记录是第一行的更新版本.

Note that in the table the last record is a newer version of the first row.

有谁知道查询最新版本的行和特定版本的记录的有效方法吗?例如，对 rev=2 的查询将返回第 2、3 和 4 行(尽管不是替换的第一行)，而对 rev=1 的查询将产生这些行rev <= 1 并且在重复 id 的情况下，选择具有较高修订号的那个(记录:1、2、3).

Does anyone know of an efficient way to query for the latest version of the rows, ans a specific version of records? For instance, a query for rev=2 would return the 2, 3 and 4th row (not the replaced 1st row though) while a query for rev=1 yields those rows with rev <= 1 and in case of duplicated ids, the one with the higher revision number is chosen (record: 1, 2, 3).

我实际上不确定这在 SQL Server 中是否可行...

I'm not actually sure if this is even possible in SQL Server...

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

I would not prefer to return the result in an iterative way.

推荐答案

仅获取最新修订:

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

要获得特定修订，在本例中为 1(如果某项还没有修订，则为下一个最小修订):

To get a specific revision, in this case 1 (and if an item doesn't have the revision yet the next smallest revision):

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

这可能不是最有效的方法，但现在我想不出更好的方法.

It might not be the most efficient way to do this, but right now I cannot figure a better way to do this.

这篇关于为多个组选择每组记录中的最新和特定版本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！