从数据库中删除重复的行 [英] Delete duplicated rows from database

问题描述

我需要从数据库中删除重复的行。我可以用简单的sql查询吗？

I need to delete duplicated rows from database. Can i do it with simple sql query? If not, please, show me some quick algorythm to do it.

例如：

id| field_one | field_two |
1 | 0000000   | 11111111  |
2 | 2222222   | 33333333  |
3 | 2222222   | 33333333  |
4 | 4444444   | 55555555  |

我需要删除id为2（或3，不管他们是否相等，但不是都）。
感谢任何帮助

I need to delete row with id 2 (or 3, no matter, they are equal, but not both). Thanks for any help

推荐答案

delete from the_table where id in
   (select max(id) from the_table
      group by field_one, field_two
      having count(*) > 1)

正如评论中所指出的，如果一行出现三次，这将不起作用。您可以反复运行此（重）查询，直到它停止删除内容，或等待更好的答案...

As pointed out in the comments, this will not work if a row appears three times. You can run this (heavy) query repeatedly until it stops deleting stuff, or wait for a better answer...

这篇关于从数据库中删除重复的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！