如何用SQL标记Access表中全字段重复记录？

首先，咱们来聊聊你遇到的错误原因：你写的双字段检测代码里，WHERE myTable.[CompanyID] AND myTable.[Product] IN (...)这种写法是不对的。IN子查询只能匹配单个字段的结果，当子查询返回多个字段时，Access就会抛出3306错误。正确的做法是用EXISTS子查询来关联主表和分组后的重复记录。

先修正双字段检测的代码 #

先把你的双字段代码改成用EXISTS的版本，这样就能正常运行了：

Call Module1.RunSQL("UPDATE myTable " & _
"SET myTable.DuplicateRecord = TRUE " & _
"WHERE EXISTS (" & _
"SELECT 1 FROM " & _
"(SELECT myTable.[CompanyID], myTable.[Product] " & _
"FROM myTable " & _
"GROUP BY myTable.[CompanyID], myTable.[Product] " & _
"HAVING COUNT(*) > 1) AS T1 " & _
"WHERE T1.[CompanyID] = myTable.[CompanyID] " & _
"AND T1.[Product] = myTable.[Product])")

这里的EXISTS会检查主表的每条记录，是否在分组后的重复记录集合里存在完全匹配的CompanyID和Product组合，只要存在就标记为重复。

扩展到15个字段的重复检测 #

要实现15个字段的全字段重复检测，只需要把所有字段都加到GROUP BY里，然后在EXISTS的关联条件里逐一匹配每个字段就行。示例代码如下（把Field1到Field15替换成你实际的字段名）：

Call Module1.RunSQL("UPDATE myTable " & _
"SET myTable.DuplicateRecord = TRUE " & _
"WHERE EXISTS (" & _
"SELECT 1 FROM " & _
"(SELECT [Field1], [Field2], [Field3], [Field4], [Field5], " & _
"[Field6], [Field7], [Field8], [Field9], [Field10], " & _
"[Field11], [Field12], [Field13], [Field14], [Field15] " & _
"FROM myTable " & _
"GROUP BY [Field1], [Field2], [Field3], [Field4], [Field5], " & _
"[Field6], [Field7], [Field8], [Field9], [Field10], " & _
"[Field11], [Field12], [Field13], [Field14], [Field15] " & _
"HAVING COUNT(*) > 1) AS T1 " & _
"WHERE T1.[Field1] = myTable.[Field1] " & _
"AND T1.[Field2] = myTable.[Field2] " & _
"AND T1.[Field3] = myTable.[Field3] " & _
"AND T1.[Field4] = myTable.[Field4] " & _
"AND T1.[Field5] = myTable.[Field5] " & _
"AND T1.[Field6] = myTable.[Field6] " & _
"AND T1.[Field7] = myTable.[Field7] " & _
"AND T1.[Field8] = myTable.[Field8] " & _
"AND T1.[Field9] = myTable.[Field9] " & _
"AND T1.[Field10] = myTable.[Field10] " & _
"AND T1.[Field11] = myTable.[Field11] " & _
"AND T1.[Field12] = myTable.[Field12] " & _
"AND T1.[Field13] = myTable.[Field13] " & _
"AND T1.[Field14] = myTable.[Field14] " & _
"AND T1.[Field15] = myTable.[Field15])")

更简便的替代方案：拼接字段成唯一键 #

如果觉得写15个字段的关联条件太繁琐，可以把所有字段拼接成一个字符串（注意用Nz处理NULL值，否则NULL会导致拼接结果无效），然后通过这个拼接后的"唯一键"来检测重复：

Call Module1.RunSQL("UPDATE myTable " & _
"SET myTable.DuplicateRecord = TRUE " & _
"WHERE EXISTS (" & _
"SELECT 1 FROM " & _
"(SELECT " & _
"Nz([Field1],'') & Nz([Field2],'') & Nz([Field3],'') & " & _
"Nz([Field4],'') & Nz([Field5],'') & Nz([Field6],'') & " & _
"Nz([Field7],'') & Nz([Field8],'') & Nz([Field9],'') & " & _
"Nz([Field10],'') & Nz([Field11],'') & Nz([Field12],'') & " & _
"Nz([Field13],'') & Nz([Field14],'') & Nz([Field15],'') AS CombinedKey " & _
"FROM myTable " & _
"GROUP BY " & _
"Nz([Field1],'') & Nz([Field2],'') & Nz([Field3],'') & " & _
"Nz([Field4],'') & Nz([Field5],'') & Nz([Field6],'') & " & _
"Nz([Field7],'') & Nz([Field8],'') & Nz([Field9],'') & " & _
"Nz([Field10],'') & Nz([Field11],'') & Nz([Field12],'') & " & _
"Nz([Field13],'') & Nz([Field14],'') & Nz([Field15],'') " & _
"HAVING COUNT(*) > 1) AS T1 " & _
"WHERE T1.CombinedKey = " & _
"Nz(myTable.[Field1],'') & Nz(myTable.[Field2],'') & Nz(myTable.[Field3],'') & " & _
"Nz(myTable.[Field4],'') & Nz(myTable.[Field5],'') & Nz(myTable.[Field6],'') & " & _
"Nz(myTable.[Field7],'') & Nz(myTable.[Field8],'') & Nz(myTable.[Field9],'') & " & _
"Nz(myTable.[Field10],'') & Nz(myTable.[Field11],'') & Nz(myTable.[Field12],'') & " & _
"Nz(myTable.[Field13],'') & Nz(myTable.[Field14],'') & Nz(myTable.[Field15],''))")

这个方法的好处是代码更简洁，但要注意：如果你的字段内容本身包含特殊字符或者拼接后可能出现不同字段组合却生成相同字符串的情况，还是用第一种逐一匹配字段的方法更可靠。

额外提醒 #

• 执行UPDATE操作前，一定要先备份你的表，避免误操作导致数据丢失。
• 如果你的表数据量很大，15个字段的GROUP BY可能会有点慢，这是Access的性能限制，你可以考虑分批处理或者优化表的索引（给要分组的字段加索引）来提升速度。

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