正在尝试创建返回所有记录的最旧日期的查询

Question

我有一个SQL Server数据库和两个tables，Certificates和Categories。这是一对多的关系，每个Certificate可以有多个categories。我正在尝试创建一个查询，该查询将显示数据库中的所有证书，但只返回每个类别的最早exp日期的一条记录。我查了一篇文章，它几乎和我想要的一模一样

但是它搜索的记录数量是有限的。我在其他类型的数据库中找到了不同的方法来做到这一点，但在ms sql server中没有有效的方法。

Answer 1

据我所知，在大多数数据库中有一种有效的方法来解决这类查询，称为JOIN。再加上PK-FK关系和索引，对于您遇到的大多数目的来说，这将是足够有效的。试着学习这些，你就会发现我在说什么。

对于这个特定的问题，您只需要在category字段上使用MIN聚合函数JOIN GROUP这两个表。如果你能提供表的结构，我们也许也能写出查询。

Answer 2

为此，您需要使用row_number()函数：

select c.*
from (select c.*,
             row_number() over (partition by category order by expdate desc) as seqnum
      from certificates c
     ) c
where seqnum = 1

这适用于SQL Server 2005版和更高版本。

在旧版本或Access中，您需要执行联接才能获得以下内容：

select c.*
from certifications c join
     (select category, max(expdate) as maxexpdate
      from certifications 
      group by category
     ) csum
     on c.category = csum.category and
        c.expddate = csum.maxexpdate

Answer 3

下面的查询将满足需要。

SELECT *
    FROM dbo.Certificates Cert INNER JOIN
    (SELECT CerificateId,MIN(ExpiryDate)
    FROM dbo.Categories
    GROUP BY CerificateId) AS LatestCategories
    ON Cert.CerificateId = LatestCategories.CerificateId