此题需要注意的是题目要求是删除,故而不能使用group by,来查询
解法如下,采用窗口函数
delete from Person
where Id
in
(
select
t1.Id
from(
select
Id,Email,
row_number()over(partition by Email order by Id) rn
from Person
) t1
where t1.rn>1
);
不采用窗口函数的解法
首先查询出要删除的数据
SELECT p1.*
FROM Person p1,
Person p2
WHERE
p1.Email = p2.Email AND p1.Id > p2.Id
;
然后删掉
DELETE p1.* FROM Person p1,
Person p2
WHERE
p1.Email = p2.Email AND p1.Id > p2.Id
;