Access SQL：删除Year为2013的指定多字段重复全量记录

需求说明 #

删除carTable表中满足以下条件的所有重复实例（全部删除，不保留任何一条）：

• Brand、SerialNr、Seats、LastRepair字段值完全重复
• Year字段值为2013

原表数据 #

期望目标表 #

尝试过的方法及失败SQL #

曾尝试查找重复查询、外连接方法，编写的SQL如下（未成功）：

DELETE Exists (SELECT 1 
   FROM carTable As t2 
   WHERE t1.Brand = t2.Brand AND t1.SerialNr = t2.SerialNr AND t1.Seats = t2.Seats AND t1.LastRepair = t2.LastRepair
   HAVING Count(*) > 1
), t1.[FilNr], *
FROM carTable AS t1, carTable
WHERE (((Exists (SELECT 1 
   FROM carTable As t2 
   WHERE t1.Brand = t2.Brand AND t1.SerialNr = t2.SerialNr AND t1.Seats = t2.Seats AND t1.LastRepair = t2.LastRepair
   HAVING Count(*) > 1
))<>False) AND ((t1.[year])=2013));

解决方案 #

核心思路 #

先找出Year=2013范围内，Brand、SerialNr、Seats、LastRepair组合重复的分组（即该分组下记录数>1），再删除原表中属于这些分组且Year=2013的所有记录。

方法1：使用IN子查询（Access支持多字段IN） #

DELETE FROM carTable
WHERE Year = 2013
AND (Brand, SerialNr, Seats, LastRepair) IN (
    SELECT Brand, SerialNr, Seats, LastRepair
    FROM carTable
    WHERE Year = 2013
    GROUP BY Brand, SerialNr, Seats, LastRepair
    HAVING COUNT(*) > 1
);

方法2：使用INNER JOIN（兼容所有Access版本） #

DELETE t1.*
FROM carTable AS t1
INNER JOIN (
    SELECT Brand, SerialNr, Seats, LastRepair
    FROM carTable
    WHERE Year = 2013
    GROUP BY Brand, SerialNr, Seats, LastRepair
    HAVING COUNT(*) > 1
) AS t2
ON t1.Brand = t2.Brand 
AND t1.SerialNr = t2.SerialNr 
AND t1.Seats = t2.Seats 
AND t1.LastRepair = t2.LastRepair
WHERE t1.Year = 2013;

原SQL错误分析 #

1. Exists子句中未使用GROUP BY就直接用HAVING，逻辑不成立——HAVING必须配合GROUP BY使用
2. 多余的carTable表连接导致查询逻辑混乱，无实际意义
3. DELETE语句的字段列表写法错误，Access中删除整行只需指定表或别名加*

内容的提问来源于stack exchange，提问作者Lily of the Valley