根据一列值删除重复的行 [英] remove duplicate rows based on one column value

问题描述

我有下表，现在我需要删除具有重复"refID"但至少有一行与该引用对应的行，即我需要删除第4行和第5行.请为此提供帮助

I have the below table and now I need to delete the rows which are having duplicate "refIDs" but have atleast one row with that ref, i.e i need to remove row 4 and 5. please help me on this

+----+-------+--------+--+
| ID | refID |  data  |  |
+----+-------+--------+--+
|  1 |  1023 | aaaaaa |  |
|  2 |  1024 | bbbbbb |  |
|  3 |  1025 | cccccc |  |
|  4 |  1023 | ffffff |  |
|  5 |  1023 | gggggg |  |
|  6 |  1022 | rrrrrr |  |
+----+-------+--------+--+

推荐答案

这类似于Gordon Linoff的查询，但没有子查询:

This is similar to Gordon Linoff's query, but without the subquery:

DELETE t1 FROM table t1
  JOIN table t2
  ON t2.refID = t1.refID
  AND t2.ID < t1.ID

这将使用内部联接仅删除其中另一行具有相同的refID但具有较低ID的行.

This uses an inner join to only delete rows where there is another row with the same refID but lower ID.

避免子查询的好处是能够利用索引进行搜索.该查询在refID + ID上的多列索引下应能很好地执行.

The benefit of avoiding a subquery is being able to utilize an index for the search. This query should perform well with a multi-column index on refID + ID.

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