MS Access SQL:获取具有最高版本和修订版本的数据集 [英] MS Access SQL: Get datasets with highest versions and revisions

问题描述

我有一个数据库表，其中包含(针对此问题)三列:

I have a database table with (for this question) three columns:

1. 文档ID
2. 修订版
3. 版本

每个文档都有1..n版本，每个版本都有1..n版本.

Each document has got 1..n Revisions and each Revision has got 1..n Versions.

对于所有文档的最高修订版本和最高修订版本，我的SQL语句是什么?

What is my SQL statement for all Documents in their highest Revisions and the highest Versions of these revisions?

Doc Title   Revision   Version    Should be selected
Dok 1       01         01
Dok 1       01         02
Dok 1       01         03         
Dok 1       02         01
Dok 1       02         02         * Because Rev 02 / V02 is the highest
Dok 2       01         01
Dok 2       02         01
Dok 2       03         01         * Because Rev 03 / V01 is the highest

D

推荐答案

如果每个 row 都有唯一的ID(我建议所有表都使用该ID)，则相关子查询是实现此目标的一种方法前往:

If you have a unique id for each row (which I recommend for all tables), then a correlated subquery is the way to go:

select d.*
from documents as d
where d.pk = (select top (1) d2.pk
              from documents as d2
              where d2.documentid = d.documentid
              order by d2.version desc, d2.revision desc, d2.pk desc
             );

没有主键，您可以通过将值连接在一起来实现:

Without a primary key, you can do this by concatenating the values together:

select d.*
from documents as d
where d.version & "-" & d.revision =
          (select top (1) d2.version & "-" & d2.revision
           from documents as d2
           where d2.documentid = d.documentid
           order by d2.version desc, d2.revision desc
          );

这篇关于MS Access SQL:获取具有最高版本和修订版本的数据集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！