下表中的此查询
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
Table:
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
它获取每个 ID 的 As_of 的 MAX 并返回...(来自另一个Question https://stackoverflow.com/questions/1704260/access-sql-query-find-the-row-with-the-most-recent-date-for-each-distinct-entry在堆栈溢出上)
我有基本上相同的表,但不是作为“物理表”,而是作为查询结果:
Query:
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”查询,但几个小时后我不能......我认为它是一个子查询......但我一生都无法得到正确的语法......
之后的结果:每个犯人 ID 的最后日期的分类
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