# 查找单个字段中的重复数据

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 (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 FROM 删除重复数据

delete p

from people p

join people d on p.email = d.email and p.id < d.id;

-delete 语句通过连接找出需要删除的记录，以上示例保留了重复数据中的最大 id 对应的数据行。

# 利用子查询删除重复数据

-通过子查询可以找出需要保留的数据，然后删除其他的数据：

delete

from people

where id not in (

      select max(id)

      from people

      group by email

     );

-在执行上面的语句之前，记得重新创建 people 表并生成测试数据。