mysql 查找删除重复数据

创建测试数据

MySQL [test]> create table people (id int auto_increment primary key,name varchar(50) not null ,email varchar(100) not null);
Query OK, 0 rows affected (0.02 sec)

MySQL [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| people         |
| tb_emp1        |
+----------------+
2 rows in set (0.00 sec)

MySQL [test]> 
Query OK, 1 row affected (0.00 sec)

MySQL [test]> insert into people (name,email) values ('张三',"zhangsan@qq.com");
Query OK, 1 row affected (0.00 sec)

MySQL [test]> insert into people (name,email) values ('李三',"lisan@qq.com");
Query OK, 1 row affected (0.00 sec)

MySQL [test]> insert into people (name,email) values ('章三',"lisan@qq.com");
Query OK, 1 row affected (0.01 sec)

MySQL [test]> select * from people;
+----+--------+-----------------+
| id | name   | email           |
+----+--------+-----------------+
|  1 | 张三   | zhangsan@qq.com |
|  2 | 张三   | zhangsan@qq.com |
|  3 | 李三   | lisan@qq.com    |
|  4 | 章三   | lisan@qq.com    |
+----+--------+-----------------+
4 rows in set (0.00 sec)

MySQL [test]>

比如查询email 字段重复的数据

MySQL [test]> select email,count(*) from people group by email;
+-----------------+----------+
| email           | count(*) |
+-----------------+----------+
| lisan@qq.com    |        2 |
| zhangsan@qq.com |        2 |
+-----------------+----------+
2 rows in set (0.00 sec)

去重查询（）

select count(distinct email) cnt from people;

查出哪些email重复

MySQL [test]> select email from people group by email having count(*)>1;
+-----------------+
| email           |
+-----------------+
| lisan@qq.com    |
| zhangsan@qq.com |
+-----------------+
2 rows in set (0.00 sec)

 删除重复数据，使用 DELETE JOIN 语句，

MySQL [test]> select * from people;
+----+--------+--------------------+
| id | name   | email              |
+----+--------+--------------------+
|  1 | 张三   | zhangsan@qq.com    |
|  2 | 张三   | zhangsan@qq.com    |
|  3 | 李三   | lisan@qq.com       |
|  4 | 章三   | lisan@qq.com       |
|  5 | 张7    | zngsan@qq.com      |
|  6 | 87     | znsdsgsan@qq.com   |
|  7 | 87sd   | zsdnsdsgsan@qq.com |
+----+--------+--------------------+
7 rows in set (0.01 sec)

MySQL [test]> delete s1 from people as s1 inner join people as s2 where s1.id<s2.id and s1.email = s2.email;     
#查询people 取一个别名为s1 ,inner join (再与people表建立内链接)取别名为s2 对比2个表
#where s1.id < s2.id and s1.email =s2.email   删除重复数据中 id 列小的数据，并且email 列一致的数据
Query OK, 2 rows affected (0.01 sec)

MySQL [test]> select * from people;
+----+--------+--------------------+
| id | name   | email              |
+----+--------+--------------------+
|  2 | 张三   | zhangsan@qq.com    |
|  4 | 章三   | lisan@qq.com       |
|  5 | 张7    | zngsan@qq.com      |
|  6 | 87     | znsdsgsan@qq.com   |
|  7 | 87sd   | zsdnsdsgsan@qq.com |
+----+--------+--------------------+
5 rows in set (0.00 sec)