如何按日期和ID删除MySQL表中的重复项？[副本]

Question

我有一个来自应用程序的结果表。有些结果是重复的，我可以看到哪个“survey_question_id”有重复的答案。

通过该查询，我可以查看每个“survey_question_id”有多少个重复项:

    SELECT `id`,`survey_question_id`,
   `question`,`date`, COUNT(*) 
   FROM answers 
   GROUP BY `survey_question_id`, `date` 
   HAVING COUNT(*) > 1 and `date` > '2015-10-15'

表格结果:

如何删除重复项？

Answer 1

请尝试以下操作:

DELETE FROM answers
WHERE date > '2015-10-15'
AND id NOT IN 
  (SELECT id
      FROM (SELECT MIN(id) AS id  FROM answers
            WHERE date > '2015-10-15'
            GROUP BY survey_question_id) x
  )

Answer 2

对于重复记录的删除，可以使用:

如果您想保持最低id，则可以使用以下内容:

DELETE n1 FROM answers n1, answers n2 WHERE n1.id > n2.id 
AND n1.id <> n2.id
AND n1.survey_question_id = n2.survey_question_id 
AND n1.question = n2.question 
AND n1.date = n2.date

如果您想保持最高id，可以使用以下内容:

DELETE n1 FROM answers n1, answers n2 WHERE n1.id < n2.id 
AND n1.id <> n2.id
AND n1.survey_question_id = n2.survey_question_id 
AND n1.question = n2.question 
AND n1.date = n2.date

如果您认为今后可能再次发生这种情况，请添加唯一索引。

ALTER IGNORE TABLE answers   
ADD UNIQUE INDEX (id,survey_question_id,
   question,date);

边注:在执行任何类型的批量删除之前，请确保您有此表的副本。

Answer 3

你可以

1. 将表your_table2创建为select distinct*from original_table，然后仅创建
2. 删除具有某些级联约束的original_table
3. 更改your_table2，重命名它并重新添加所有约束，或者只是删除original_table中的所有数据，然后只需插入到original_table中，选择*from your_table2

我想这和你的问题差不多。