删除表中的重复条目 [英] delete duplicate entries in table

问题描述

我要从下表中删除多个重复的键:

I want to delete multiple duplicate keys from the below table:

id | name   | uid
1  | ekta   | 5
2  | ekta   | 5
3  | sharma | 10
4  | sharma | 10

想要像

id | name   | uid
1  | ekta   | 5
3  | sharma | 10

我正在使用mysql.这是可能的吗? 我不能使用unique constraint查询来进行唯一的输入，因为我希望将这些重复的输入输入到表中.

I am using mysql. Is it ossible.? I can't use unique constraint query to make unique enteries because i want this duplicate entries ones entered to the table.

推荐答案

一种方法是使用LEFT JOIN将表联接到子查询上.子查询在每个UID中获得最低的ID.当子查询中的记录不匹配时，仅表示它没有匹配的记录，可以安全地删除.

One way of doing this is by joining the table on a subquery using LEFT JOIN. The subquery gets the lowest ID for every UID. When a record doesn't have match on the subquery, it just means that it has no matching record and can be safely deleted.

DELETE  a
FROM    TableName a
        LEFT JOIN
        (
            SELECT  uid, MIN(ID) min_ID
            FROM    TableName
            GROUP   BY uid
        ) b ON  a.uid = b.uid AND
                a.ID = b.min_ID
WHERE   b.uid IS NULL

• SQLFiddle演示
• SQLFiddle Demo

但是，如果UID的记录可以具有不同的名称，则您需要在group by子句中包含name，否则将仅保留具有最低ID的唯一uid.

However, if the records of UID can have different name, then you need to include name on the group by clause or else only unique uid with the lowest ID will remain.

DELETE  a
FROM    TableName a
        LEFT JOIN
        (
            SELECT  uid, MIN(ID) min_ID, name
            FROM    TableName
            GROUP   BY uid, name
        ) b ON  a.uid = b.uid AND
                a.ID = b.min_ID AND
                a.name = b.name
WHERE   b.uid IS NULL

• SQLFiddle演示
• SQLFiddle演示(具有相同名称的相同uid )
• SQLFiddle Demo
• SQLFiddle Demo (the same uid with different name)

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