mysql - 根据匹配的 3 列中的值删除 mysql 中的重复项

Question

我有下面的查询，它在我的表中显示了重复项。我想知道如何将其转换为删除查询以删除这些重复行但只留下一个。我的表确实有一个自动递增的 id 列。

SELECT * FROM tbl_user_tmp AS t1
INNER JOIN (
SELECT name, activity, class, COUNT(1) AS cnt FROM tbl_user_tmp
WHERE user = 'test' AND disregard = 0
GROUP BY name, activity, class
HAVING cnt > 1
) AS t2
ON t1.name = t2.name AND t1.activity = t2.activity AND t1.class = t2.class

WHERE user = 'test' AND disregard = 0
GROUP BY t1.name, t1.activity, t1.class

我已经尝试了下面的查询并且似乎有效，但恐怕我遗漏了一些东西。看起来正确吗？

delete from tbl_user_tmp 
where user='test' AND id not in
(
      select minid from 
      (select min(id) as minid from tbl_user_tmp where user='test' group by name, activity, class) as newtable
)

Answer 1

您可以使用LIMIT。示例:

DELETE FROM users
LIMIT 2;

现在你只需要设置 COUNT - 1 作为你的限制 ;)