create TABLE LC196(Id int(10), Email VARCHAR(20));

insert into LC196 VALUES(1, 'john@example.com');
insert into LC196 VALUES(2, 'bob@example.com');
insert into LC196 VALUES(3, 'john@example.com');
insert into LC196 VALUES(4, 'bon@example.com');


-- 法一：使用group by, 再取min(id)

DELETE from LC196 a
where a.id not in 
(
	SELECT need.id from 
		(select min(id) as id
			from LC196
			GROUP BY email
		) as need
);


-- 使用 exists 优化
DELETE from LC196 a
where not exists
(
    select need.id from
    (
        select min(id) as id from LC196 GROUP BY email
    ) as need
    where need.id = a.id
);

-- 法二：自连接
-- 自连接
SELECT p1.*
FROM LC196 p1,
    LC196 p2
WHERE
    p1.Email = p2.Email

-- 找出重复的id大的值
SELECT p1.*
FROM LC196 p1,
    LC196 p2
WHERE
    p1.Email = p2.Email AND p1.Id > p2.Id;
		

-- 删除
DELETE p1 FROM LC196 p1,
    LC196 p2
WHERE
    p1.Email = p2.Email AND p1.Id > p2.Id;