【MySQL】NOT IN需要外部套一层SELECT

以这个题引出

先说我率先想到的解题思路：先根据email分组，查出每组的最小ID，然后删除结果集中任何不存在的ID的数据，因为这些ID都不是每组最小的

DELETE
FROM    Person AS p1
WHERE   id
        NOT IN  (
                    SELECT  id
                    FROM    (
                                SELECT  MIN(id) AS id
                                FROM    Person AS p2
                                GROUP BY `email`
                            ) AS minOfGroup
            );

这里的细节是，从逻辑上看，似乎外层套的一层的SELECT多余了

不过这是MySQL为了保证正确性，Oracle就没有，所以没有理由了，必须写

我自己思考了一个理由，比如

SELECT  MIN(id) AS 'id'
FROM    Person AS p2
GROUP BY `email`

上层要用列名为id来过滤，而这个子查询查出来的列名是id（我设置的别名），如果这里别名不是id就报错，这就是MySQL考虑的点，这里可能会有风险，在语法层面帮我们避免了

最后再附上额外两种解法

这种方法是通过 LEFT JOIN 找到需要删除的行，即那些 p1.id 不是对应 email 最小 id 的行（p2.minId IS NULL），然后删除这些行。

DELETE p1
FROM Person p1
LEFT JOIN (
    SELECT MIN(id) AS 'minId', email
    FROM Person
    GROUP BY email
) p2 ON p1.email = p2.email AND p1.id = p2.minId
WHERE p2.minId IS NULL;

使用 ROW_NUMBER() (某些数据库支持): 如果你的数据库支持 ROW_NUMBER() 函数，可以使用窗口函数更简洁地实现：

这个方法为每个 email 的记录根据 id 排序并分配一个行号，然后删除行号大于1的记录。

DELETE FROM Person
WHERE id IN (
    SELECT id
    FROM (
        SELECT id, ROW_NUMBER() OVER(PARTITION BY email ORDER BY id) as row_num
        FROM Person
    ) AS Ranked
    WHERE row_num > 1
);

笛卡尔积自连接，千万级数据的时候连接往往性能会高一些，因为对索引的利用

DELETE p1 FROM Person p1,
    Person p2
WHERE
    p1.Email = p2.Email AND p1.Id > p2.Id;