删除mysql中的重复数据

[code]
//创建表
CREATE TABLE users_info (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) DEFAULT NULL,
email VARCHAR(255) NOT NULL
);

//插入数据
INSERT INTO users_info (username,email)
VALUES (‘Carine ‘,’aa@qq.com’),
(‘Jean’,’aa@qq.com’),
(‘Peter’,’bb@qq.com’),
(‘Janine ‘,’bb@qq.com’),
(‘Jonas ‘,’cc@qq.com’),
(‘Janine ‘,’cc@qq.com’);

//查看超过一条的数据
SELECT email, COUNT(email) FROM users_info GROUP BY email HAVING COUNT(email) > 1;

//删除重复数据,保留id最大的
DELETE t1 FROM users_info as t1 INNER JOIN users_info as t2 WHERE t1.id<t2.id AND t1.email=t2.email

//删除重复数据,保留id最小的
DELETE t1 FROM users_info as t1 INNER JOIN users_info as t2 WHERE t1.id>t2.id AND t1.email=t2.email

//查看删除后的结果
SELECT * FROM users_info
[/code]