Access SQL 查询：为表中的每个不同条目查找具有最新日期的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1704260/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Access SQL query: find the row with the most recent date for each distinct entry in a table
提问by mattstuehler
All,
全部，
I'm sure this is a pretty simple SQL query question, but I'm sure there's a good way, and a very BAD way, to do this. Left to my own devices, I'm liable to end up with the latter. So...
我确信这是一个非常简单的 SQL 查询问题，但我确信有一个很好的方法，也是一个非常糟糕的方法，可以做到这一点。留给我自己的设备，我很可能会得到后者。所以...
I have a table in Access with data that looks like this:
我在 Access 中有一个表，其中的数据如下所示：
ID Value As_of
1173 156 20090601
1173 173 20081201
1173 307 20080901
1173 305 20080601
127 209 20090301
127 103 20081201
127 113 20080901
127 113 20080601
1271 166 20090201
1271 172 20081201
1271 170 20080901
1271 180 20080601
...
What I'd like to get is the "Value" for each unique ID with the most recent "As Of" date (which is in YYYYMM format).
我想得到的是每个唯一 ID 的“值”以及最近的“截至”日期（采用 YYYYMM 格式）。
So, my result set should look like this:
所以，我的结果集应该是这样的：
ID Value As_of
1173 156 20090601
127 209 20090301
1271 166 20090201
Note that different IDs will have different "As Of" dates. In other words, I can't simply indentify the most recent as of globally, then select every row with that date.
请注意，不同的 ID 将具有不同的“截止”日期。换句话说，我不能简单地识别全球最新的，然后选择具有该日期的每一行。
For what it's worth, this table has about 200,000 total rows, and about 10,000 unique IDs.
就其价值而言，该表总共有大约 200,000 行和大约 10,000 个唯一 ID。
Many thanks in advance!
提前谢谢了！
回答by Radu094
If you need both the date and the value, you need to do a join:
如果您需要日期和值，则需要进行连接：
SELECT ID, Value,As_of
from yourTable a inner join
(SELECT ID, MAX(As_of) as As_of
from yourTable group by ID) b
on a.ID=b.ID and a.As_of = b.As_of
回答by boydc7
@Funka, that will not work if you have duplicate "value" values for different ID's - that will basically give you a grouped list by "value", not by id...
@Funka，如果您对不同的 ID 有重复的“值”值，这将不起作用 - 这将基本上为您提供按“值”而不是 id 的分组列表...
@Joe Fair, aggregates aren't allowed in where clauses without a subquery/having combo as well, at least not in ANSI...
@Joe Fair，在没有子查询/具有组合的 where 子句中也不允许聚合，至少在 ANSI 中不允许......
This will give you the list, but will give duplicates as well if you have multiple rows with the same id/As_of values:
这将为您提供列表，但如果您有多个具有相同 id/As_of 值的行，也会提供重复项：
select t1.id, t1.value, t1.As_of
from tableName t1
join (
select id as id, max(As_of) as max_as_of
from tableName
group by id
) t2
on t1.id = t2.id
and t1.As_of = t2.max_as_of
If you want to remove duplicates from that, you'd just want to add a distinct to the top select, like this:
如果您想从中删除重复项，您只需在顶部选择中添加一个不同的选项，如下所示：
select distinct t1.id, t1.value, t1.As_of
from tableName t1
join (
select id as id, max(As_of) as max_as_of
from tableName
group by id
) t2
on t1.id = t2.id
and t1.As_of = t2.max_as_of
回答by Adriaan Stander
Try something like this
尝试这样的事情
SELECT t1.*
FROM (SELECT Table1.ID, Max(Table1.As_Of) AS MaxOfAs_Of
FROM Table1
GROUP BY Table1.ID
) AS MaxIDS INNER JOIN Table1 t1 ON MaxIDS.ID = t1.ID
and MaxIDS.MaxOfAs_Of = t1.As_Of
回答by Funka
Not sure what platform you're looking to do this on, but in T-SQL you can do the following:
不确定您要在哪个平台上执行此操作，但在 T-SQL 中您可以执行以下操作：
SELECT t.*
FROM (
SELECT ID, MAX(As_Of) as r1
FROM myTable
GROUP BY ID
) as dt
INNER JOIN myTable t ON dt.ID = t.ID and dt.r1 = t.As_Of
Good luck!
祝你好运！
EDIT: Well my poor answer was bothering me so i've fixed it, even though this answer already exists elsewhere on the page now.
编辑：好吧，我糟糕的答案困扰着我，所以我已经修复了它，即使这个答案现在已经存在于页面上的其他地方。
回答by Joe Fair
I think what you're looking for is this:
我想你要找的是这个：
select id, value, as_of from table_name where as_of = max(as_of) group by id
select id, value, as_of from table_name where as_of = max(as_of) group by id
This says for each id, find the max as_of, and get that value.
这表示对于每个 id，找到最大 as_of，并获得该值。
This is generic sql. I'm not sure about access. I'm sure if this doesn't work there is something similar.
这是通用的sql。我不确定访问。我敢肯定，如果这不起作用，还有类似的东西。
Good luck! Joe
祝你好运！乔
