MS Access SQL查询：重复账户仅保留ComputationPeriodStartingDate最新记录

最终可直接运行的SQL语句 #

SELECT [TDS LOANS].Account, [TDS Escrow Projections].ComputationPeriodStartingDate, [TDS LOANS].LastName, [TDS LOANS].Categories
FROM ([TDS Escrow Projections] 
INNER JOIN [TDS LOANS] ON [TDS Escrow Projections].LoanRecID = [TDS LOANS].RecID)
INNER JOIN (
    SELECT LoanRecID, MAX(ComputationPeriodStartingDate) AS MaxStartDate
    FROM [TDS Escrow Projections]
    GROUP BY LoanRecID
) AS tLatest ON [TDS Escrow Projections].LoanRecID = tLatest.LoanRecID 
            AND [TDS Escrow Projections].ComputationPeriodStartingDate = tLatest.MaxStartDate
WHERE ([TDS LOANS].PrinBal<>0)
ORDER BY [TDS LOANS].Account;

实现逻辑说明 #

• 新增子查询tLatest，先对[TDS Escrow Projections]表按LoanRecID分组，计算每个贷款ID对应的最新ComputationPeriodStartingDate值
• 在原有两表关联的基础上新增和tLatest的关联，仅保留同时满足贷款ID匹配、计算期起始日期等于最新日期的记录，自动过滤掉同一账户下日期较旧的重复条目
• 保留了你原有的PrinBal不等于0的过滤条件，排序逻辑可根据需要调整

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