Skip to content

Latest commit

 

History

History
87 lines (67 loc) · 1.83 KB

File metadata and controls

87 lines (67 loc) · 1.83 KB

English Version

题目描述

表: Person

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| email       | varchar |
+-------------+---------+
id是该表的主键列。
该表的每一行包含一封电子邮件。电子邮件将不包含大写字母。

 

编写一个 SQL 删除语句删除 所有重复的电子邮件,只保留一个id最小的唯一电子邮件。

任意顺序 返回结果表。 (注意: 仅需要写删除语句,将自动对剩余结果进行查询)

查询结果格式如下所示。

 

 

示例 1:

输入: 
Person 表:
+----+------------------+
| id | email            |
+----+------------------+
| 1  | [email protected] |
| 2  | [email protected]  |
| 3  | [email protected] |
+----+------------------+
输出: 
+----+------------------+
| id | email            |
+----+------------------+
| 1  | [email protected] |
| 2  | [email protected]  |
+----+------------------+
解释: [email protected]重复两次。我们保留最小的Id = 1。

解法

SQL

DELETE
FROM
    Person
WHERE
    Id NOT IN (
    SELECT
        MIN( Id )
    FROM
        ( SELECT * FROM Person ) AS p
    GROUP BY
        p.Email
    );
DELETE p2
FROM
    person AS p1
    JOIN person AS p2 ON p1.email = p2.email
WHERE
    p1.id < p2.id;