如何设计MS Access SQL查询模拟Excel删除重复项功能

需求：编写SQL实现类似Excel「删除重复项」的功能——移除Specimen和ADDON组合重复的记录，每组重复记录始终保留至少一条。

现有问题查询 #

问题查询1：删除了重复组的所有记录 #

该查询会将所有存在重复的Specimen+ADDON组的记录全部排除，不符合保留至少一条的需求：

SELECT 
    ReportX.[Specimen], ReportX.[ADDON], ReportX.[Dept], ReportX.[Location], ReportX.[MRN], 
    ReportX.[Solcited_Status], ReportX.[FillerOrderNumber], ReportX.[PlaceGroupNumber], ReportX.[OrderType], 
    ReportX.[Investigation], ReportX.[Rejected], ReportX.[Mis_Un_labelled], ReportX.[No_samp_recd], 
    ReportX.[Amendedreport], ReportX.[OrderedBy], ReportX.[RespClinican], ReportX.[Collecteddate], 
    ReportX.[CollecteddateTime], ReportX.[messagedat]
FROM 
    ReportX
WHERE 
    ReportX.[Specimen] NOT IN (
        SELECT 
            [Specimen] 
        FROM 
            [ReportX] AS Tmp 
        GROUP BY 
            [Specimen], [ADDON] 
        HAVING 
            Count(*) >= 1  
            AND 
            [ADDON] = [ReportX].[ADDON]
    )
ORDER BY 
    ReportX.[Specimen], ReportX.[ADDON];

问题查询2：仍返回重复记录 #

该查询仅分组提取了Specimen和ADDON的第一条值，但关联主表时又拉取了该组的所有记录，导致重复：

SELECT 
    SubQ.[Specimen], 
    SubQ.[ADDON], 
    ReportX.[Dept], 
    ReportX.[Location], 
    ReportX.[MRN], 
    ReportX.[Solcited_Status], 
    ReportX.[FillerOrderNumber], 
    ReportX.[PlaceGroupNumber], 
    ReportX.[OrderType], 
    ReportX.[Investigation], 
    ReportX.[Rejected], 
    ReportX.[Mis_Un_labelled], 
    ReportX.[No_samp_recd], 
    ReportX.[Amendedreport], 
    ReportX.[OrderedBy], 
    ReportX.[RespClinican], 
    ReportX.[Collecteddate], 
    ReportX.[CollecteddateTime], 
    ReportX.[messagedat]
FROM 
    (SELECT 
        FIRST(ReportX.[Specimen]) AS [Specimen], 
        FIRST(ReportX.[ADDON]) AS [ADDON]
    FROM 
        ReportX
    GROUP BY 
        ReportX.[Specimen], ReportX.[ADDON]) AS SubQ
INNER JOIN 
    ReportX ON SubQ.[Specimen] = ReportX.[Specimen] AND SubQ.[ADDON] = ReportX.[ADDON];

样本数据 #

解决方案 #

1. 查询去重后的记录（保留每组一条） #

该查询会提取每个Specimen+ADDON组中最早的一条记录（按CollecteddateTime排序），如果表有主键（如ID），建议用主键替代CollecteddateTime以确保唯一性：

SELECT r.*
FROM ReportX AS r
WHERE r.[CollecteddateTime] IN (
    SELECT TOP 1 [CollecteddateTime]
    FROM ReportX AS t
    WHERE t.[Specimen] = r.[Specimen] AND t.[ADDON] = r.[ADDON]
    ORDER BY [CollecteddateTime]
)
ORDER BY r.[Specimen], r.[ADDON];

2. 直接删除表中的重复记录（仅保留每组一条） #

注意：执行删除操作前务必备份数据！
该SQL会删除每组重复记录中除最早一条外的所有记录：

DELETE FROM ReportX
WHERE [CollecteddateTime] NOT IN (
    SELECT TOP 1 [CollecteddateTime]
    FROM ReportX AS t
    WHERE t.[Specimen] = ReportX.[Specimen] AND t.[ADDON] = ReportX.[ADDON]
    ORDER BY [CollecteddateTime]
);

关键说明 #

• 原查询1的错误：NOT IN子查询会匹配所有存在重复的Specimen+ADDON组，导致这些组的所有记录都被排除；
• 原查询2的错误：分组仅提取了Specimen和ADDON的首个值，但关联主表时未限定唯一记录，因此拉取了该组的全部记录，仍有重复。

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