删除MS Access中带有GROUP BY子句的SELECT查询的反面

Question

我在MS Access中有一个冗长的SELECT查询。基本上，我想从表中删除未被查询选中的所有内容。谢谢!

    SELECT [Benefit Refused Cancellation Report].BENEFIT_PERIOD, 
        [Benefit Refused Cancellation Report].BENEFIT_TYPE, 
        [Benefit Refused Cancellation Report].CARRIER_NAME, 
        [Benefit Refused Cancellation Report].PLAN_NAME, 
        [Benefit Refused Cancellation Report].PERSON_TYPE, 
        [Benefit Refused Cancellation Report].LAST_NAME, 
        [Benefit Refused Cancellation Report].FIRST_NAME, 
        [Benefit Refused Cancellation Report].MIDDLE_NAME, 
        [Benefit Refused Cancellation Report].SUFFIX, 
        [Benefit Refused Cancellation Report].GENDER, 
        [Benefit Refused Cancellation Report].DATE_OF_BIRTH, 
        [Benefit Refused Cancellation Report].DATE_OF_HIRE, 
        [Benefit Refused Cancellation Report].EFFECTIVE_DATE, 
        Max([Benefit Refused Cancellation Report].END_DATE) AS MaxOfEND_DATE, 
        [Benefit Refused Cancellation Report].SSN

FROM [Benefit Refused Cancellation Report]

GROUP BY [Benefit Refused Cancellation Report].BENEFIT_PERIOD, 
        [Benefit Refused Cancellation Report].BENEFIT_TYPE, 
        [Benefit Refused Cancellation Report].CARRIER_NAME, 
        [Benefit Refused Cancellation Report].PLAN_NAME, 
        [Benefit Refused Cancellation Report].PERSON_TYPE, 
        [Benefit Refused Cancellation Report].LAST_NAME, 
        [Benefit Refused Cancellation Report].FIRST_NAME, 
        [Benefit Refused Cancellation Report].MIDDLE_NAME, 
        [Benefit Refused Cancellation Report].SUFFIX, 
        [Benefit Refused Cancellation Report].GENDER, 
        [Benefit Refused Cancellation Report].DATE_OF_BIRTH, 
        [Benefit Refused Cancellation Report].DATE_OF_HIRE, 
        [Benefit Refused Cancellation Report].EFFECTIVE_DATE, 
        [Benefit Refused Cancellation Report].SSN;

Answer 1

您可以使用相关子查询来完成此操作：

delete from [Benefit Refused Cancellation Report]
    where END_DATE < (select max(brdr2.End_Date)
                      from [Benefit Refused Cancellation Report] as brcr2
                      where brcr2.SSN = [Benefit Refused Cancellation Report].SSN
                     );

这假设SSN唯一地定义了您想要的每一行。要真正匹配GROUP BY，您需要包含更多的字段--甚至所有字段。

需要注意的是:这没有考虑到NULL的值，所以用于匹配的键应该是非NULL的。