删除所有重复值的行，只保留一行

Question

我有一个三列表格：KEY、VALUE和LAST_UPDATED

存在重复的VALUE字段。我想要删除所有与其他行具有相同VALUE，除非是最近更新的那一行。

因此，如果表格包含以下行：

1, "A", 2013-11-08
2, "B", 2013-10-30
3, "A", 2013-11-07
4, "A", 2013-11-01
5, "B", 2013-11-01

我希望只保留这些行：

1, "A", 2013-11-08
5, "B", 2013-11-01

你如何在SQL中实现这个功能呢？我想DELETE FROM table WHERE key IN (SELECT key FROM table GROUP BY value HAVING count(*)>1)可以删除重复值中的任意一行，但是如何使其删除除最近更新的行之外的所有行呢？

- Will

---

好的，我想问题应该是这样的。里面有没有一个字段可以让我们知道哪个是最近更新的？ - Mike Perrenoud

在 T-SQL 中，您可以使用 row_number() over(partition by order by LAST_UPDATED desc) 来实现。尝试在 MYSQL 中采用类似的方法。 - Prahalad Gaggar

@neoistheone的LAST_UPDATED列 - Will

可能是在MySQL中删除重复行的重复问题。 - D Mac

@DMac，你链接的问题并没有尝试保留特定行，这就是为什么回答该问题的SQL语句如此不同的原因。 - Will

Answer 1

类似以下代码应该可以运行：

DELETE t FROM myTable t
INNER JOIN
(
 SELECT VALUE,MAX(LAST_UPDATED) as mostRecent 
 FROM myTable 
 GROUP BY VALUE
 HAVING COUNT(*) > 1           
) t2 ON t.VALUE = t2.VALUE 
     AND t.LAST_UPDATED != t2.mostRecent;

- Tom Mac

---

非常干净的解决方案 - Prahalad Gaggar

我没有点踩，但我更喜欢另一个解决方案，因为你的解决方案使用了子查询 - 这是不必要的。只需像 collusionbdbh 显示的那样进行自连接即可。 - D Mac

啊哈。明白了。我想我自己更喜欢使用 LEFT JOIN 的方法，现在这么想起来... - Tom Mac

Answer 2

你可以使用左连接来实现这个目标：

DELETE t
FROM table t
LEFT JOIN table t2 ON t.value = t2.value
AND t2.last_updated > t.last_updated
WHERE t2.key IS NOT NULL

这意味着对于每一行数据，它会查找具有更新日期的最新行，如果存在这样的行，则删除该行。您可能需要考虑使用日期差值而不是使用大于号来比较日期，因为它更可靠。

我预计在这种情况下左连接的性能要比创建并连接内联表要好得多，但如果性能成为问题，则最好尝试两种方法并选择最一致表现最佳的方式。

- collusionbdbh