如何基于关联表的最新日期筛选Access记录？

问题描述 #

我是个非专业人员，想用简单方法实现需求：我有个简易数据库，包含Client Table和Review Table，Review表的每条记录通过ClientID关联客户。我想在关联Client表的表单里查看每个客户的最新审核记录。

我试了两种查询方法，都有问题：

第一种方法 #

SQL语句：

SELECT TableClient.ClientID, TableClient.ClientFullName, Max(TableReviews.ReviewDate) AS LastReview
FROM TableClient INNER JOIN TableReviews ON TableClient.ClientID = TableReviews.ReviewClient
GROUP BY TableClient.ClientID, TableClient.ClientFullName;

这个语句能返回最新审核日期，但没法获取Review表的其他数据（比如ReviewID或审核备注），一添加就返回所有Review记录，我知道是没搞懂GROUP BY的用法。

第二种方法 #

直接查Review表的语句：

SELECT TableReviews.ReviewClient, TableReviews.ReviewID, Max(TableReviews.ReviewDate) AS LastReview
FROM TableReviews
GROUP BY TableReviews.ReviewClient, TableReviews.ReviewID;

这个语句返回了Review表的所有结果，没法限制成每个客户的最新审核记录。

我搞不定正确的分组或筛选SQL，可能用错了方法，有没有其他方式获取每个客户的最新审核记录？

补充说明

我需要的查询要显示每个客户的最新审核记录，示例TableReviews数据：

ReviewID    ReviewDate  ReviewClient
1           17/10/2022  Johnny Smith
2           4/10/2022   Neddy Not-Here
3           13/10/2022  Johnny Smith
4           3/10/2022   Johnny Smith

期望结果：

ReviewID    ReviewDate  ReviewClient
1           17/10/2022  Johnny Smith
2           4/10/2022   Neddy Not-Here

解决方案 #

你之前的问题核心在于：GROUP BY要求SELECT中的非聚合字段必须出现在GROUP BY列表里，所以第二种方法加入ReviewID后，每个ReviewID都会单独成组，自然返回所有记录；第一种方法没法直接添加其他Review字段，因为它们不在GROUP BY中，也未被聚合函数处理。

这里提供两种可行的解决方法：

方法一：子查询关联法（兼容绝大多数数据库） #

先通过子查询获取每个客户的最新审核日期，再关联原表拿到完整的审核记录，最后关联Client表获取客户信息：

SELECT r.ReviewID, r.ReviewDate, r.ReviewClient, c.ClientFullName
FROM TableReviews r
INNER JOIN (
    -- 子查询：提取每个客户的最新审核日期
    SELECT ReviewClient, MAX(ReviewDate) AS LastReviewDate
    FROM TableReviews
    GROUP BY ReviewClient
) latest 
    ON r.ReviewClient = latest.ReviewClient 
    AND r.ReviewDate = latest.LastReviewDate
-- 关联Client表（如果不需要客户全名可省略此JOIN）
INNER JOIN TableClient c 
    ON r.ReviewClient = c.ClientID;

方法二：窗口函数法（适用于支持窗口函数的数据库，如MySQL 8+、SQL Server、PostgreSQL等） #

利用窗口函数给每个客户的审核记录按日期倒序排名，筛选出排名第一的记录即可：

SELECT ReviewID, ReviewDate, ReviewClient, ClientFullName
FROM (
    SELECT 
        r.ReviewID, 
        r.ReviewDate, 
        r.ReviewClient, 
        c.ClientFullName,
        -- 按客户分组，按审核日期倒序排序，最新记录排名为1
        ROW_NUMBER() OVER (PARTITION BY r.ReviewClient ORDER BY r.ReviewDate DESC) AS rn
    FROM TableReviews r
    INNER JOIN TableClient c ON r.ReviewClient = c.ClientID
) ranked
WHERE rn = 1;

注意点 #

如果同一个客户在同一天有多条审核记录，方法一会返回所有当天的记录；方法二则只会返回其中一条（具体哪条取决于数据库默认排序，若需指定可在ORDER BY后添加ReviewID DESC）。

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