题目:
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 | +----+------------------+
# Write your MySQL query statement below
#删除表中重复的邮件地址,删除规则:保留id更小的
# Delete from Person p
# where p.Id not in (select Min(Id) as Id from Person group by Email);
#因为会报错:You can't specify target table 'Person' for update in FROM clause
#使用中间表
Delete FROM Person
WHERE Id NOT IN (SELECT t.Id FROM (SELECT MIn(Id) as Id FROM Person GROUP BY Email) t );