MYSQL数据去重与外表填充

时间:2023-03-08 22:18:51
MYSQL数据去重与外表填充

经常要对数据库中的数据进行去重,有时还需要使用外部表填冲数据,本文档记录数据去重与外表填充数据。

date:2016/8/17

author:wangxl

1 需求

对user_info1表去重,并添加age项。

2 表数据

user_info1:
+----+----------+------+------+
| id | name | sex | age |
+----+----------+------+------+
| 1 | xiaolong | 1 | NULL |
| 2 | xiaoyun | 1 | NULL |
| 3 | xiaoqin | 2 | NULL |
| 4 | xiaolong | 1 | NULL |
| 5 | xiaodong | 1 | NULL |
| 6 | xiaokai | 1 | NULL |
| 7 | xiaohong | 2 | NULL |
| 8 | xiaolong | 1 | NULL |
| 9 | xiaohong | 2 | NULL |
| 10 | xiaofen | 2 | NULL |
+----+----------+------+------+ user_info2:
+----------+------+
| name | age |
+----------+------+
| xiaolong | 26 |
| xiaoyun | 28 |
| xiaoqin | 27 |
| xiaodong | 27 |
| xiaokai | 27 |
| xiaohong | 24 |
| xiaofen | 22 |
+----------+------+

3 实战

3.1 去重

(1) 找出有重复字段
select * from user_info1 where name in (select name from user_info1 group by name having count(name) > 1);
(2) 找出要删除的记录,重复记录是根据单个字段(name)来判断,只留有id最小的记录
select * from user_info1 where name in (select name from user_info1 group by name having count(name) > 1) and id not in (select min(id) from user_info1 group by name having count(name) > 1);
(3) 删除表中多余的重复记录
delete from user_info1 where name in (select name from user_info1 group by name having count(name) > 1) and id not in (select min(id) from user_info1 group by name having count(name) > 1); 报错:ERROR 1093 (HY000): You can't specify target table 'user_info1' for update in FROM clause

更换思路:找出每组中非最小id并删除,如下:

(4) 找出每组最小id
select min(id) from user_info1 group by name
(5) 找出每组非最小id
select * from user_info1 where id not in (select min(id) from user_info1 group by name);
(6) 删除每组中非最小id所在行
delete from user_info1 where id not in (select id from select min(id) from user_info1 group by name);
ERROR 1093 (HY000): You can't specify target table 'user_info1' for update in FROM clause
更正:
delete from user_info1 where id not in (select minid from (select min(id) as minid from user_info1 group by name) a);、 结果展示:
+----+----------+------+------+
| id | name | sex | age |
+----+----------+------+------+
| 1 | xiaolong | 1 | NULL |
| 2 | xiaoyun | 1 | NULL |
| 3 | xiaoqin | 2 | NULL |
| 5 | xiaodong | 1 | NULL |
| 6 | xiaokai | 1 | NULL |
| 7 | xiaohong | 2 | NULL |
| 10 | xiaofen | 2 | NULL |
+----+----------+------+------+

对于没有primary key的话,怎么去重呢?

(7) 创建表test
(8) insert into test select distinct(name),sex,age from user_info1 group by name; 暂时没想出一句话解决方案.

3.2 外表插入

update user_info1 t set age=(select age from user_info2 where name=t.name);
结果如下:
+----+----------+------+------+
| id | name | sex | age |
+----+----------+------+------+
| 1 | xiaolong | 1 | 26 |
| 2 | xiaoyun | 1 | 28 |
| 3 | xiaoqin | 2 | 27 |
| 5 | xiaodong | 1 | 27 |
| 6 | xiaokai | 1 | 27 |
| 7 | xiaohong | 2 | 24 |
| 10 | xiaofen | 2 | 22 |
+----+----------+------+------+