leetcode 196：删除重复的电子邮箱
力扣 196
错误解法
DELETE from Person where (emial, id)not in(SELECT distinct email, min(id) id from Person group by email);
执行错误提示
错误的意思是说，不能先select出同一表中的某些值，再
update这个表(在同一语句中)。解决板房就是将select出的结果再通过中间表select一遍，这样就规避了错误。注意，这个问题只出现于mysql。
正确解法
（1）
DELETE from Person where id not in(
SELECT id from (SELECT distinct email, min(id) id from Person group by email)t);
（2）
delete from Person where id in(select t1.id from(
select Id, Email, row_number() over(partition by Email order by Id asc) 'rn'
from Person)t1 where t1.rn>1);
网友评论