max,min函数with group by是针对每组的数据求最大最小值
Approach: Using DELETE
and WHERE
clause
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 | +----+------------------+
DELETE p1 FROM Person p1,
Person p2
WHERE
p1.Email = p2.Email AND p1.Id > p2.Id
mysql能运行但leetcode通不过:
SELECT MIN(Id) as Id, Email
FROM Person
group BY Email;
select distinct id, email from person
where id not in
(select p1.id
from Person p1, person p2
where p1.id > p2.id
and p1.email = p2.email);