题目描述:
Write a SQL query to delete all duplicate email entries in a table named Person
, keeping only unique emails based on its smallest Id.
+----+------------------+ | Id | Email | +----+------------------+ | 1 | john@example.com | | 2 | bob@example.com | | 3 | john@example.com | +----+------------------+ Id is the primary key column for this table.
For example, after running your query, the above Person
table should have the following rows:
+----+------------------+ | Id | Email | +----+------------------+ | 1 | john@example.com | | 2 | bob@example.com | +----+------------------+
Note:
Your output is the whole Person
table after executing your sql. Use delete
statement.
中文理解:删除数据表Person中邮件重复的列,保留id最小的那一列。
解题思路:首先使用group by获取最小的id,然后delete from Person where Id not in(minId)。
代码(sql):
delete from Person where Id not in (
select minid
from
(select min(Id) as minid from Person group by Email) t1
);