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

2021-11-20 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
            );

相关文章