如何删除重复的行并将其保留在Access数据库中? [英] How to remove duplicate rows and keep one in an Access database?

问题描述

我需要删除Access数据库中的重复行，有人可以通过泛型查询来做到这一点吗?由于我有多个表这个问题

I need to remove duplicate rows in my Access database, does anyone have generic query to do this? As I have this problem with multiple tables

推荐答案

您需要做两件事，

1. 确定唯一记录的标准-列的列表是什么，其中两个或更多记录将被视为重复记录，例如JobbID和HisGuid
2. 确定要对重复记录执行的操作-是要硬删除它们还是要设置表上的IsDeleted标志

1. Determine what the criteria are for a unique record - what is the list of columns where two, or more, records would be considered duplicates, e.g. JobbID and HisGuid
2. Decide what you want to do with the duplicate records - do you want to hard delete them, or set the IsDeleted flag that you have on the table

一旦确定了要用于唯一性的条件，则需要从每组重复项中选择 1 条记录以保留.类似于以下内容的查询:

Once you've determined the criteria that you want to use for uniqueness you then need to pick 1 record from each group of duplicates to retain. A query along the lines of:

SELECT  MAX(ID)
FROM    MyTable
GROUP
BY      JobbID, HisGuid

将为您( ID 列是一个自动递增/标识列，在表中的所有记录中都是唯一的)最高 JobbID 和 HisGuid 都相同的每组记录的值.您可以根据需要使用 MIN(ID)，具体取决于您-您只需要从每个组中选择一个记录保留.

Will give you (and I've assumed that the ID column is an auto-increment/identity column that is unique across all records in the table) the highest value for each group of records where JobbID and HisGuid are both the same. You could use MIN(ID) if you want, it's up to you - you just need to pick ONE record from each group to keep.

假设您要在不想保留的记录上设置 IsDeleted 标志，然后可以将其合并到更新查询中:

Assuming that you want to set the IsDeleted flag on the records you don't want to keep, you can then incorporate this into an update query:

UPDATE  MyTable
SET     IsDeleted = 1
WHERE   ID NOT IN
(
    SELECT  MAX(ID)
    FROM    MyTable
    GROUP
    BY  JobbID, HisGuid
)

这将获取查询的结果，该查询将检索最高ID ，并使用该结果对所有ID都不为" set IsDeleted 设为1"对于 JobbID 和 HisGuid 相同的每组记录，最高ID.

This takes the result of the query that retrieves the highest IDs and uses it to say set IsDeleted to 1 for all the records where the ID isn't the highest ID for each group of records where JobbID and HisGuid are the same.

我唯一无法帮助您的部分是在Access中运行这些查询，因为我没有将其安装在我现在使用的PC上，并且我的内存有点生锈关于如何/在何处运行任意查询.

The only part I can't help you with is running these queries in Access as I don't have it installed on the PC I'm using right now and my memory is a bit rusty regarding how/where to run arbitrary queries.

这篇关于如何删除重复的行并将其保留在Access数据库中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！