如何删除没有临时表的 MySQL 表中的所有重复记录

2022-01-10 00:00:00 sql duplicates mysql sql-delete unique-index

我已经看到了这方面的许多变体，但没有一个完全符合我想要完成的目标.

I've seen a number of variations on this but nothing quite matches what I'm trying to accomplish.

我有一个表格，TableA，其中包含用户对可配置问卷的回答.这些列是 member_id、quiz_num、question_num、answer_num.

I have a table, TableA, which contain the answers given by users to configurable questionnaires. The columns are member_id, quiz_num, question_num, answer_num.

不知何故，一些成员的答案被提交了两次.所以我需要删除重复的记录，但要确保留下一行.

Somehow a few members got their answers submitted twice. So I need to remove the duplicated records, but make sure that one row is left behind.

没有主要列，因此可能有两三行包含完全相同的数据.

There is no primary column so there could be two or three rows all with the exact same data.

是否有删除所有重复项的查询?

Is there a query to remove all the duplicates?

推荐答案

在你的表上添加唯一索引:

ALTER IGNORE TABLE `TableA`   
ADD UNIQUE INDEX (`member_id`, `quiz_num`, `question_num`, `answer_num`);

另一种方法是:

在您的表中添加主键，然后您可以使用以下查询轻松地从表中删除重复项:

Add primary key in your table then you can easily remove duplicates from your table using the following query: 

DELETE FROM member  
WHERE id IN (SELECT * 
             FROM (SELECT id FROM member 
                   GROUP BY member_id, quiz_num, question_num, answer_num HAVING (COUNT(*) > 1)
                  ) AS A
            );

相关文章