删除重复的行，并需要在mysql中保留所有它们中的一个 [英] delete duplicate rows and need to keep one from all of them in mysql

问题描述

我想基于两列删除重复的行，但需要将所有这些都保留1行.

I want to delete duplicate rows based on two columns but need to keep 1 row all of them.

重复的行可以超过两行，

Duplicate rows can be more than two rows like,

ID  NAME PHONE
--  ---- ----
1   NIL  1234 
2   NIL  1234 
3   NIL  1234 
4   MES  5989

我想从3个以上的行中删除2行，并保留1行.

I want to delete any of 2 rows from above 3 and keep 1 row.

推荐答案

DELETE  a
FROM    tableA a
        LEFT JOIN
        (
            SELECT MIN(ID) ID, Name, Phone
            FROM    TableA
            GROUP   BY Name, Phone
        ) b ON  a.ID = b.ID AND
                a.NAme = b.Name AND
                a.Phone = b.Phone
WHERE   b.ID IS NULL

• SQLFiddle演示
• SQLFiddle Demo

执行delete语句后，对列执行唯一约束，以使您无法再次插入重复的记录，

After you have executed the delete statement, enforce a unique constraint on the column so you cannot insert duplicate records again,

ALTER TABLE TableA ADD CONSTRAINT tb_uq UNIQUE (Name, Phone)

这篇关于删除重复的行，并需要在mysql中保留所有它们中的一个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！