从 MYSQL 中的表中删除重复的电子邮件地址

2022-01-10 00:00:00 duplicate-removal mysql

我有一个表，其中包含 ID、firstname、lastname、address、email 的列 等等.

I have a table with columns for ID, firstname, lastname, address, email and so on.

有什么方法可以从 TABLE 中删除重复的 email 地址?

Is there any way to delete duplicate email addresses from the TABLE?

其他信息(来自评论):

Additional information (from comments):

如果有两行具有相同的 email 地址，其中一行将具有正常的 firstname 和 lastname，而另一行将具有 firstname 中的即时".因此，我可以区分它们.我只想删除名字为instant"的那个.

If there are two rows with the same email address one would have a normal firstname and lastname but the other would have 'Instant' in the firstname. Therefore I can distinguish between them. I just want to delete the one with first name 'instant'.

注意，firstname='Instant' 的某些记录只有 1 个 email 地址.我不想只删除一个唯一的电子邮件地址，所以我不能只删除 firstname='Instant' 的所有内容.

Note, some records where the firstname='Instant' will have just 1 email address. I don't want to delete just one unique email address, so I can't just delete everything where firstname='Instant'.

请帮帮我.

推荐答案

我不知道这是否可以在 MYSQL 中工作(我没有使用它)...但是您应该可以执行以下操作片段.

I don't know if this will work in MYSQL (I haven't used it)... but you should be able to do something like the following snippets.

我建议您运行它们以了解是否选择了正确的数据.如果它确实有效，那么您可能想要在列上创建一个约束.

I'd suggest you run them in order to get a feel for if the right data is being selected. If it does work, then you probably want to create a constraint on the column.

获取所有重复的电子邮件地址:

Get all of the duplicate e-mail addresses:

SELECT 
    EMAILADDRESS, COUNT(1)
FROM
    TABLE
GROUP BY EMAILADDRESS
HAVING COUNT(1) > 1

然后确定给出的 ID:

Then determine the ID from that gives:

SELECT
    ID
FROM 
    TABLE
WHERE 
    EMAILADDRESS IN (
        SELECT 
            EMAILADDRESS
        FROM
            TABLE
        GROUP BY EMAILADDRESS
        HAVING COUNT(1) > 1
    )

最后，根据上述和其他约束，删除行:

Then finally, delete the rows, based on the above and other constraints:

DELETE 
FROM 
    TABLE
WHERE
    ID IN (
        SELECT
            ID
        FROM 
            TABLE
        WHERE 
            EMAILADDRESS IN (
                SELECT 
                    EMAILADDRESS
                FROM
                    TABLE
                GROUP BY EMAILADDRESS
                HAVING COUNT(1) > 1
            )
    )  
    AND FIRSTNAME = 'Instant'

相关文章