MySQL删除重复的行 [英] MySQL remove duplicate rows

问题描述

我有一个注释表，其结构如下:

I have a comments table, whose structure is as:

id, name, email, comment

我有很多重复的评论，名字和电子邮件都一样.我需要删除它们，有人可以建议我如何使用一个查询来实现这一目标吗?

I have many duplicate comments, with same name and email. I need to remove them, can anyone suggest me how can I achieve this using a single query?

谢谢

推荐答案

DELETE FROM comments c1 

WHERE EXISTS (
    SELECT * 
    FROM comments c2 
    WHERE c2.id <> c1.id 
        AND c2.name = c1.name 
        AND c2.email = c1.email 
        AND c2.comment = c1.comment
)

AND c1.id <> (
    SELECT MIN(c2.id) 
    FROM comments c2 
    WHERE c2.name = c1.name 
        AND c2.email = c1.email 
        AND c2.comment = c1.comment
)

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