mysql查询根据时间戳识别和删除重复项

Question

我正在尝试构建一个 mysql 查询以列出单个表中具有重复列 b 的所有列 a。诀窍是我在行上有一个时间戳，所以我需要从根本上确定哪个是较旧的重复项，以便我可以删除它。任何帮助将不胜感激。

Answer 1

举个例子——这个查询返回重复的帖子，现在你只需要执行删除

id| title     | text_desc          | created 
-------------------------------------------------------
1 | The title | description here   |2012-02-21 10:58:58
2 | The title | description here 1 |2012-02-21 10:58:58
3 | The title | description here 3 |2012-02-21 10:58:58

    select bad_rows.*
     from posts as bad_rows
      inner join (
       select title, MIN(id) as min_id
          from posts
           group by title
             having count(*) > 1
          ) as good_rows on good_rows.title = bad_rows.title
            and good_rows.min_id <> bad_rows.id;

这是返回行

id| title     | text_desc          | created 
-------------------------------------------------------
2 | The title | description here 1 |2012-02-21 10:58:58
3 | The title | description here 3 |2012-02-21 10:58:58