访问:根据记录中的最新日期进行分组(嵌套查询) [英] Access: grouping according to most recent date in a record (nested query)
问题描述
下表中的此查询
SELECT ID,
Value,
As_of
FROM Table a
INNER JOIN (
SELECT ID,
MAX(As_of) AS As_of
FROM Table
GROUP BY ID
) b
ON a.ID=b.ID
AND a.As_of = b.As_of
表格:
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
使它看起来像这样
Result
ID Value As_of
1173 156 20090601
127 209 20090301
1271 166 20090201
It gets the MAX for the As_of for each ID and returns that... (from another Question on stackoverflow)
我有基本相同的表，但不是物理表"，而是查询结果:
I have basically the same table but not as a "physical table" but as a query result:
查询:
SELECT tblClassificationHistory.inmateID,
tblClassificationHistory.classificationID
FROM tblinmate
INNER JOIN tblClassificationHistory
ON tblinmate.inmateID = tblClassificationHistory.inmateID
GROUP BY tblClassificationHistory.inmateID,
tblClassificationHistory.classificationID,
tblClassificationHistory.reclassificationDate
ORDER BY tblClassificationHistory.inmateID;
获得此结果:
inmateID classificationID reclassificationDate
2 3 9/22/2015
2 4 9/13/2015
2 8 9/8/2015
3 8 9/13/2015
4 4 9/15/2015
4 8 6/16/2015
5 3 9/15/2015
5 4 9/16/2015
5 8 7/7/2015
6 8 9/14/2015
7 7 9/13/2015
8 3 8/4/2015
8 8 6/1/2015
9 3 9/15/2015
10 6 9/13/2015
11 3 9/13/2015
12 3 8/3/2015
12 4 9/9/2015
12 6 9/13/2015
12 8 7/6/2015
13 8 9/13/2015
14 8 9/13/2015
15 4 9/13/2015
16 4 9/13/2015
17 8 9/14/2015
18 8 9/14/2015
19 3 9/15/2015
我想在我的数据库中使用"His"查询，但是几个小时后我无法...我认为它是一个子查询..但是我无法终生获得正确的语法...
I want to use "His" query in mine but after a few hours i cant...i presume its a subquery..but i cant for the life of me get the syntax right...
之后的结果:每个犯人ID的最后日期的分类ID
Result Im after: classificationID for Last date for each inmateID
inmateID classificationID reclassificationDate
2 3 9/22/2015
3 8 9/13/2015
4 4 9/15/2015
5 4 9/16/2015
6 8 9/14/2015
7 7 9/13/2015
8 3 8/4/2015
9 3 9/15/2015
10 6 9/13/2015
11 3 9/13/2015
12 6 9/13/2015
13 8 9/13/2015
14 8 9/13/2015
15 4 9/13/2015
16 4 9/13/2015
17 8 9/14/2015
18 8 9/14/2015
19 3 9/15/2015
推荐答案
SELECT a.inmateID,
a.classificationID,
b.max_date
FROM (
SELECT tblClassificationHistory.inmateID,
tblClassificationHistory.classificationID,
tblClassificationHistory.reclassificationDate
FROM tblinmate
INNER JOIN tblClassificationHistory
ON tblinmate.inmateID = tblClassificationHistory.inmateID
) a
INNER JOIN (
SELECT tblClassificationHistory.inmateID,
MAX(tblClassificationHistory.reclassificationDate) as max_date
FROM tblinmate
INNER JOIN tblClassificationHistory
ON tblinmate.inmateID = tblClassificationHistory.inmateID
GROUP BY tblClassificationHistory.inmateID
) b
ON a.inmateID = b.inmateID
AND a.reclassificationDate = b.max_date
ORDER BY a.inmateID;
您可以将其分为两个子查询(a和b).在b中，我们将在表中找到MAX日期，不包括categoryID和仅按inmateID分组的日期.我们将得到一个这样的表:
You can break it down into two subqueries (a and b). In b, we'll find the MAX date in our table, excluding classificationID and grouping only by inmateID. We'll wind up with a table like this:
+----------+-----------+
| inmateID | max_date |
+----------+-----------+
| 2 | 9/22/2015 |
| 3 | 9/13/2015 |
| 4 | 9/15/2015 |
| 5 | 9/16/2015 |
| 6 | 9/14/2015 |
| 7 | 9/13/2015 |
| ... | ... |
+----------+-----------+
在a中，我们将简单地获取未聚合的值，包括此分类的分类ID:
In a, we'll simply grab the unaggregated values, including classificationID this go-around:
+----------+------------------+----------------------+
| inmateID | classificationID | reclassificationDate |
+----------+------------------+----------------------+
| 2 | 3 | 9/22/2015 |
| 2 | 4 | 9/13/2015 |
| 2 | 8 | 9/15/2015 |
| 3 | 8 | 9/16/2015 |
| 4 | 4 | 9/14/2015 |
| 4 | 8 | 9/13/2015 |
| ... | ... | ... |
+----------+------------------+----------------------+
我们加入两个子查询，在a中查找行，其中reclassificationDate等于在b中刚刚计算出的最大日期.然后，我们可以从那些连接的行中获取适当的分类ID，以构建最终表.
We join the two subqueries, finding rows in a where reclassificationDate is equal to the MAX date with just calculated in b. We can then grab the appropriate classificationID from those joined rows to build our final table.
这篇关于访问:根据记录中的最新日期进行分组(嵌套查询)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
