SQL:根据最近的日期选择具有一个唯一字段的多个字段 [英] SQL: Selecting multiple fields with one unique field based on most recent date

问题描述

我正在尝试编写 SQL 语句以根据最近的日期选择唯一的部件号.如果我只有两个字段 PartNo 和 ReceiveDate 我可以这样做:

I'm attempting to write an SQL statement to select a unique part number based on the most recent date. If I have just the two fields PartNo and ReceiveDate I could do:

"SELECT PartNo, Max(ReceiveDate) FROM Table GROUP BY PartNo;"

这将返回唯一的 PartNo 和最近的日期.

and this would return the unique PartNo and the most recent date.

问题是我还想包括 VendorName 和 Qty 字段(但我只希望 PartNo 是唯一的).我试过了:

The problem is that I also want to include the fields VendorName and Qty (But I just want PartNo to be unique). I've tried:

"SELECT PartNo, VendorName, Qty, Max(ReceiveDate) FROM Table GROUP BY PartNo;"

和

"SELECT PartNo, VendorName, Qty, Max(ReceiveDate) FROM Table GROUP BY PartNo, VendorName, Qty;"

我明白为什么这两个 SELECT 语句是错误的，第一个没有运行，因为 VendorName 和 Qty 不在 GROUP BY 子句或聚合函数的一部分中，而在第二个中它选择一个基于唯一记录关于零件编号和供应商名称和数量，而不仅仅是零件编号.

I understand why these two SELECT statements are wrong, the first one doesn't run since VendorName and Qty aren't in the GROUP BY clause or part of an aggregate function, and in the second one it selects a unique record based on PartNo AND VendorName AND Qty, not just PartNo.

如果有人能帮我写出正确的 SQL 语句，那将不胜感激.我正在使用 Microsoft Access，它使用与 T-SQL 非常相似的 Jet SQL.

If someone could help me write the correct SQL statement that would be must appreciated. I'm using Microsoft Access which uses Jet SQL which is very similar to T-SQL.

推荐答案

我建议使用相关子查询:

I would suggest a correlated subquery:

SELECT t.*
FROM Table as t
WHERE t.ReceiveDate = (SELECT MAX(t2.ReceiveDate)
                       FROM Table as t2
                       WHERE t2.PartNo = t.PartNo
                      );

特别是，这可以利用 (PartNo, ReceiveDate) 上的索引.

In particular, this can take advantage of an index on (PartNo, ReceiveDate).

这篇关于SQL:根据最近的日期选择具有一个唯一字段的多个字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！