mysql删除重复记录并保留一条记录

-- 第一种
DELETE t1
FROM t_user t1,
     t_user t2
WHERE t1.code= t2.code
  AND t1.name = t2.name
  AND t1.id < t2.id;

-- 第二种
delete
from t_user
where id not in (
    (select t1.max_id
     from (select max(id) as max_id from t_user group by name, code having count(1) > 1) t1))
  and (lessee_id, name) in
      (select t2.lessee_id, t2.name
       from (select lessee_id, name from t_user group by name, code having count(1) > 1) t2);

-- 第三种
delete
from t_user
where id not in (select * from (select max(id) from t_user group by name, code) t2);

目的：去除（或删除）一个表里面手机号重复的数据，但是需要保留其中一个记录，换句话说，表里面手机号不为空的数据，一个手机有且只有一条记录

表结构：

CREATE TABLE `account` (
    `id` int(11) NOT NULL,
    `phone` varchar(11) DEFAULT NULL,
    PRIMARY KEY (`id`)
);

插入一些数据：

insert into account values('1','13888888888');
insert into account values('2','13888888888');
insert into account values('3','13888888888');
insert into account values('4','13811111111');
insert into account values('5','13811111111');
insert into account values('6','15188888888');
insert into account values('7','15188888888');
insert into account values('8','15188888888');
insert into account values('9','15188888888');
insert into account values('10','19911111111');
insert into account values('11','19911111111');
insert into account values('12','19911111111');
insert into account values('13','19911111111');
insert into account values('14','19911111111');
insert into account values('15','17700000000');
insert into account values('16','17700000000');
insert into account values('17','17700000000');
insert into account values('18','17700000000');
insert into account values('19','17700000000');

查询一下现在表里面的重复情况：

select count(*)as num,phone from account where phone <> '' group by phone having num > 1;

查询结果：

现在我们要去除多余的手机号数据，直接把这个值置为空，删除同理，这里不再重复

SQL如下：

update account set phone='' where 
    phone in 
    (select phone from account where phone<>'' group by phone having count(id)>1)
    and 
    id not in
    (select min(id) from account where phone<>'' group by phone having count(id)>1)
;