删除重复的行并需要在 mysql 中保留所有行中的一个
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15548655/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
delete duplicate rows and need to keep one from all of them in mysql
提问by Neel
I want to delete duplicate rows based on two columns but need to keep 1 row all of them.
我想删除基于两列的重复行，但需要全部保留 1 行。
Duplicate rows can be more than two rows like,
重复行可以超过两行，例如，
ID NAME PHONE
-- ---- ----
1 NIL 1234
2 NIL 1234
3 NIL 1234
4 MES 5989
I want to delete any of 2 rows from above 3 and keep 1 row.
我想从 3 上面删除 2 行中的任何一行并保留 1 行。
回答by John Woo
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
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)
回答by Matt Busche
DELETE
FROM Table
WHERE Table.id NOT IN (
SELECT MIN(idTable) idtable
FROM idTable
GROUP BY name, phone)
