ACCESS数据库：按唯一值首次出现筛选行的查询问题

方法一：使用窗口函数（Access 2010及以上版本支持） #

利用ROW_NUMBER()窗口函数为每个ITEM_ID的关联记录按Project_Date降序编号，随后筛选出编号为1的记录（即每个ITEM_ID最晚日期的任意一条）：

SELECT 
    t.ITEM_ID,
    t.ACTIVITY_ID,
    t.PROJECT_ID,
    t.Project_Date
FROM (
    SELECT 
        ITEMS_TABLE.ITEM_ID,
        ACTIVITY_TABLE.ACTIVITY_ID,
        PROJECT_TABLE.PROJECT_ID,
        PROJECT_TABLE.Project_Date,
        ROW_NUMBER() OVER (PARTITION BY ITEMS_TABLE.ITEM_ID ORDER BY PROJECT_TABLE.Project_Date DESC) AS rn
    FROM 
        ITEMS_TABLE
    INNER JOIN ACTIVITY_TABLE ON ITEMS_TABLE.ACTIVITY_ID = ACTIVITY_TABLE.ACTIVITY_ID
    INNER JOIN PROJECT_TABLE ON ACTIVITY_TABLE.PROJECT_ID = PROJECT_TABLE.PROJECT_ID
) AS t
WHERE t.rn = 1

方法二：兼容低版本Access的子查询写法 #

如果你的Access版本不支持窗口函数，可以通过子查询获取每个ITEM_ID的最大Project_Date，再关联回原表筛选符合条件的记录，最后通过TOP 1确保每个ITEM_ID只返回一条：

SELECT 
    i.ITEM_ID,
    a.ACTIVITY_ID,
    p.PROJECT_ID,
    p.Project_Date
FROM 
    ITEMS_TABLE i
INNER JOIN ACTIVITY_TABLE a ON i.ACTIVITY_ID = a.ACTIVITY_ID
INNER JOIN PROJECT_TABLE p ON a.PROJECT_ID = p.PROJECT_ID
WHERE 
    p.Project_Date = (
        SELECT MAX(p2.Project_Date)
        FROM PROJECT_TABLE p2
        INNER JOIN ACTIVITY_TABLE a2 ON p2.PROJECT_ID = a2.PROJECT_ID
        INNER JOIN ITEMS_TABLE i2 ON a2.ACTIVITY_ID = i2.ACTIVITY_ID
        WHERE i2.ITEM_ID = i.ITEM_ID
    )
GROUP BY 
    i.ITEM_ID,
    a.ACTIVITY_ID,
    p.PROJECT_ID,
    p.Project_Date
HAVING 
    a.ACTIVITY_ID = (
        SELECT TOP 1 a3.ACTIVITY_ID
        FROM ACTIVITY_TABLE a3
        INNER JOIN PROJECT_TABLE p3 ON a3.PROJECT_ID = p3.PROJECT_ID
        INNER JOIN ITEMS_TABLE i3 ON a3.ACTIVITY_ID = i3.ACTIVITY_ID
        WHERE i3.ITEM_ID = i.ITEM_ID AND p3.Project_Date = p.Project_Date
    )

原理说明 #

你之前的查询仅能获取每个ITEM_ID的最大日期，但未关联回原表获取对应的ACTIVITY_ID和PROJECT_ID。添加ACTIVITY_ID时报错是因为Access要求GROUP BY子句中必须包含所有非聚合字段，直接添加未做聚合处理的ACTIVITY_ID不符合规则。
上述两种方法均先确定每个ITEM_ID的最晚日期，再筛选出该日期对应的关联记录，同时确保每个ITEM_ID只返回一条结果（若存在多条同日期记录，任意取其一）。

内容的提问来源于stack exchange，提问作者Kromen