1.创建测试表，并插入数据
create table test(
id int(11) primary key auto_increment comment ‘用户编号‘,
username varchar(20) comment ‘用户名‘,
phone char(11) comment ‘手机号码‘,
add_time int(11) comment ‘注册时间‘,
)engine=innodb default charset=utf8;
insert into test(username,phone) values(‘a‘,‘13545885556‘),(‘b‘,15456768789),(‘a‘,‘13545885556‘),(‘b‘,15456768789),(‘c‘,‘14545574857‘),(‘a‘,‘13545885556‘);
数据如下：
+----+----------+-------------+----------+
| id | username | phone | add_time |
+----+----------+-------------+----------+
| 1 | a | 13545885556 | NULL |
| 2 | b | 15456768789 | NULL |
| 3 | a | 13545885556 | NULL |
| 4 | b | 15456768789 | NULL |
| 5 | c | 14545574857 | NULL |
| 6 | a | 13545885556 | NULL |
+----+----------+-------------+----------+
2.根据username分组，然后查找出id最大的。就是我们要保留的数据。
SELECT username,MAX(id) FROM test GROUP BY username;
查询结果
+----------+---------+
| username | MAX(id) |
+----------+---------+
| a | 6 |
| b | 4 |
| c | 5 |
+----------+---------+
3.接下来删除掉不包含这些id的记录
DELETE FROM test WHERE id NOT IN (SELECT t.xid FROM (SELECT MAX(id) AS xid FROM test GROUP BY username)t);
其实还有许多其它的方法，有什么写的不妥的地方，欢迎大家指教！