mysql处理重复数据仅保留一条记录

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

表结构：

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)
;

 

但是执行的时候会报错：

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)    
Error Code: 1093. You can't specify target table 'account' for update in FROM clause    0.031 sec

 

分析：不能先select出同一表中的某些值，再update这个表(在同一语句中)

重新写SQL（取下别名）：

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

 

执行完成之后我们再查一遍数据的情况：

select * from account;

 

查询结果（每个手机号只有一条记录，其他均被置空）：