如何在MS Access查询中获取去重最新记录并修复现有查询语法错误

原语句错误原因 #

• 子查询添加了WHERE ID=M.ID限制，只会匹配到外层当前行的同一条记录，逻辑上无法实现去重取最新值的需求
• 没有搭配GROUP BY就直接使用HAVING子句，不符合SQL语法规范，大部分数据库都会直接抛出语法错误
• 没有明确分组逻辑：从示例数据来看，你需要的是VOLNO、REGSNO、FULLCODE三个字段完全相同的重复分组下，仅保留日期最新的记录

调整后的可用SQL #

注意：如果你的数据库日期转换函数和MySQL不同，替换STR_TO_DATE即可，比如SQL Server用CONVERT(DATE, DAY, 103)，Oracle用TO_DATE(DAY, 'dd-mm-yyyy')

写法1：兼容所有主流数据库的EXISTS写法 #

SELECT *
FROM TotalPendingList AS M
WHERE STR_TO_DATE(M.DAY, '%d-%m-%Y') = (
    SELECT MAX(STR_TO_DATE(DAY, '%d-%m-%Y'))
    FROM TotalPendingList
    WHERE VOLNO = M.VOLNO AND REGSNO = M.REGSNO AND FULLCODE = M.FULLCODE
)

写法2：支持窗口函数的数据库（MySQL8.0+、SQL Server、PostgreSQL等）更简洁的写法 #

WITH ranked_data AS (
    SELECT *,
        ROW_NUMBER() OVER (
            PARTITION BY VOLNO, REGSNO, FULLCODE
            ORDER BY STR_TO_DATE(DAY, '%d-%m-%Y') DESC
        ) AS row_rank
    FROM TotalPendingList
)
SELECT ID, DAY, VOLNO, REGSNO, FULLCODE
FROM ranked_data
WHERE row_rank = 1;

结果说明 #

上述SQL执行后会自动保留每个VOLNO、REGSNO、FULLCODE分组下日期最新的记录，和你给出的预期结果一致。如果你给出的预期结果中确实不需要ID为5的记录，可以额外加对应过滤条件即可。

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