MySQL 如何查找并刪除重復(fù)記錄的實(shí)現(xiàn)

drop table if exists people;
create table people (
 id int auto_increment primary key,
 name varchar(50) not null,
 email varchar(100) not null
);

insert into people(name, email)
values ('張三', 'zhangsan@test.com'),
  ('李四', 'lisi@test.com'),
  ('王五', 'wangwu@test.com'),
  ('李斯', 'lisi@test.com'),
  ('王五', 'wangwu@test.com'),
  ('王五', 'wangwu@test.com');

select * from people;
id|name |email   |
--|------|-----------------|
 1|張三 |zhangsan@test.com|
 2|李四 |lisi@test.com |
 3|王五 |wangwu@test.com |
 4|李斯 |lisi@test.com |
 5|王五 |wangwu@test.com |
 6|王五 |wangwu@test.com |

alter table people add constraint uk_people_email unique key (email);
ERROR 1062 (23000): Duplicate entry 'wangwu@test.com' for key 'people.uk_people_email'

select email, count(email)
from people
group by email
having count(email) > 1;
email   |count(email)|
---------------|------------|
lisi@test.com |   2|
wangwu@test.com|   3|

select *
from people
where email in (
  select email
  from people
  group by email
  having count(email) > 1)
order by email;
id|name |email   |
--|------|---------------|
 2|李四 |lisi@test.com |
 4|李斯 |lisi@test.com |
 3|王五 |wangwu@test.com|
 5|王五 |wangwu@test.com|
 6|王五 |wangwu@test.com|

select p.*
from people p
join (
 select email
 from people
 group by email
 having count(email) > 1
) d on p.email = d.email
order by email;
id|name |email   |
--|------|---------------|
 2|李四 |lisi@test.com |
 4|李斯 |lisi@test.com |
 3|王五 |wangwu@test.com|
 5|王五 |wangwu@test.com|
 6|王五 |wangwu@test.com|

select distinct p.*
from people p
join people d on p.email = d.email
where p.id > d.id
order by p.email;
id|name |email   |
--|------|---------------|
 4|李斯 |lisi@test.com |
 2|李四 |lisi@test.com |
 6|王五 |wangwu@test.com|
 5|王五 |wangwu@test.com|
 3|王五 |wangwu@test.com|

select *
from people
where (name, email) in (
  select name, email
  from people
  group by name, email
  having count(1) > 1)
order by email;
id|name |email   |
--|------|---------------|
 3|王五 |wangwu@test.com|
 5|王五 |wangwu@test.com|
 6|王五 |wangwu@test.com|

select distinct p.*
from people p
join people d on p.name = d.name and p.email = d.email
where p.id > d.id
order by email;
id|name |email   |
--|------|---------------|
 6|王五 |wangwu@test.com|
 5|王五 |wangwu@test.com|
 3|王五 |wangwu@test.com|

delete p
from people p
join people d on p.email = d.email and p.id  d.id;

select * from people;
id|name |email   |
--|------|-----------------|
 1|張三 |zhangsan@test.com|
 4|李斯 |lisi@test.com |
 6|王五 |wangwu@test.com |

delete
from people
where id not in (
  select max(id)
  from people
  group by email
  );

-- 創(chuàng)建中間表
create table people_temp like people;

-- 復(fù)制需要保留的數(shù)據(jù)行
insert into people_temp(id, name, email)
select id, name, email
from people
where id in (
  select max(id)
  from people
  group by email
  );

--刪除原表
drop table people;

-- 將中間表重命名為原表
alter table people_temp rename to people;

select id, name, email, 
  row_number() over (partition by email order by id) as row_num 
from people;
id|name |email   |row_num|
--|------|-----------------|-------|
 2|李四 |lisi@test.com |  1|
 4|李斯 |lisi@test.com |  2|
 3|王五 |wangwu@test.com |  1|
 5|王五 |wangwu@test.com |  2|
 6|王五 |wangwu@test.com |  3|
 1|張三 |zhangsan@test.com|  1|

delete
from people
where id in (
 select id
 from (
  select id,
    row_number() over (partition by email order by id desc) as row_num 
  from people) d
 where row_num > 1);