如何删除MySQL表中没有临时表的所有重复记录 [英] How do I delete all the duplicate records in a MySQL table without temp tables

问题描述

我已经看到了一些变化，但没有什么比较完美的。

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
            );

这篇关于如何删除MySQL表中没有临时表的所有重复记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！