删除重复记录保留1条

发表于 2021-01-26 更新于 2023-01-25

删除重复的数据，目前有两种策略，一种是，分组统计，找到唯一的数据，选择保留，或在重复的范围中，取反删除不唯一的条目。另外一种思路，联同一张表，进行查找删除。

在删除重复数据中，需要注意：1、如果是删除操作，注意该表可能还在有新增的操作，注意不能影响到新插入的数据。2、能分批操作，分批。（但是分批，一般来说也不太好操作）

文章以下的内容，是需要建立在以下的认识。1、全体样本。全体样本，可能还在变化，如新增、删除、更新。（如果是这种复杂情况，建议需要三思）2、全体样本中的唯一。这个应该是要保留的数据（group by ）3、重复样本全体分组，注意是分组，我们还可以对它进行运算。（group by + having count(*)>1）这个里面，包含了全部的重复数据，但是不能直接删除它们，因为要从他们中间保存1条。4、重复样本分组max/min，（max + group by + having count(）> 1)

文章：

https://www.cnblogs.com/chongcheng/p/12900841.html

https://segmentfault.com/a/1190000019458642

分组

利用分组 group by 字段1,字段2,字段2 + having count(*) > 1来实现找到重复的数据。

方式一（保留）：

然后从中挑选出唯一的数据，然后选择插入到另外一长表或者删除掉，

方式二（删除）：

或者删除除已找到的数据以外的所有数据。（方法较麻烦，某些情况下，查询表，又删除该表的内容，可能会触发错误，需要用到子查询。）

相当于排除法，具体排除有就种方式。

例子

单字段

在几千条记录里,存在着些相同的记录,如何能用SQL语句,删除掉重复的呢
1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断

select * from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)

2、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录

delete from people
where  peopleName in (select peopleName  from people group by peopleName   having count(peopleName) > 1)
and  peopleId not in (select min(peopleId) from people group by peopleName   having count(peopleName)>1)

多字段

3、查找表中多余的重复记录（多个字段）

select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)

4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录

delete from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

5、查找表中多余的重复记录（多个字段），不包含rowid最小的记录

select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

插入到新表

不删除原有的表的数据，将整理后的不重复的数据插入到新表中。

只想包含局部的列，不想要全部的列，如，原表的id不要。如下。可能的情况有，由于没有id的限制，下面的语句可以执行多次，会导致数据重复。

INSERT INTO td_mail_03 ( ship_id, send_id, recv_id, kafka_time, insert_time )
SELECT ship_id,
		 send_id,
		 recv_id,
		 kafka_time,
		 insert_time
FROM td_mail_02
WHERE "id" IN 
    (SELECT MIN (ID)
    FROM td_mail_02
    GROUP BY  ship_id, recv_id, send_id )

全部的列都复制。两者的表结构一致。则可以使用更简单的sql。如下：

INSERT INTO td_mail_03  
SELECT *
FROM td_mail_02
WHERE "id" IN 
    (SELECT MIN (ID)
    FROM td_mail_02
    GROUP BY  ship_id, recv_id, send_id )

删除法

在删除的时候，要注意，此时系统是否还在新入库数据。如果还在新入库数据，排除的时候，最好加上范围，如：between(min,max)，进行分批处理，这样，避免删除掉新添加的数据。在大量数据的时候，非常有必要。

方法1：

该方法排除的比较麻烦。另外，按下列方式进行排除，需要重复执行两个子查询，子查询本身是耗费时间的，如果在此旗舰，又增加了数据，下面删除，可能不妥。

delete from user_info 
where (name,sex,age) in ( 
	select x.* from ( -- 删除的时候，这里要在包裹一层子查询
		select -- 查询重复数据中，name, sex, age的值
			name, sex, age
		from user_info
		group by name, sex, age
		having count(*) > 1
	) as x
)
and id not in (
	select min_id from ( -- 删除的时候，这里要在包裹一层子查询
		select -- 查询重复数据中，最小的id值
			min(id) as min_id
		from user_info 
		group by name, sex, age
		having count(*) > 1
	) as y
);

注意：上面的删除语句中，我们在两个where条件中的子查询语句外面又包裹了一层子查询，即为上面SQL语句中的as x和as y两个查询语句，之所以包裹一层的原因是在程序如下的错误提示：

1093 - You can't specify target table 'user_info' 
for update in FROM clause, Time: 0.084000s

上述错误的原因是：修改一个表的时候子查询不能是这被修改的这个表，所以，我们的解决办法是，在子查询外面再套一层查询语句就可以了。

方法2：

delete from user_info 
where id not in(
	select min_id from (
		select 
			min(id) as min_id
		from user_info 
		group by name, sex, age
	) as x
);

注意：这里为了避免MySQL的1903错误，我们也在where条件的子查询中包裹了另外一个子查询，即上面SQL中as x查询语句。

上面相当于从全部数据中，排除数据，然后删除。相当于，一次性操作全表。我们可以加上范围（id between(min,max)），分批处理。

自联表

进行联表查询，inner join 同一张表，进行处理。会有重复，重复条数按组合排列来计算。（重复n条，则该条会有n*(n-1)/条，即组合排列。如果某条记录有3条重复的，那么查出来应该是组合 c3 2。4条重复的，则c4 2）

通过两个表关联的方式来删除数据，这个方式效率比较高，推荐使用这种方式。自己和自己关联，关联的条件就是我们判断数据是否为重复数据的key。除此之外，最重要的一个条件是：两个表的id关联条件，这个是删除保留数据的关键条件。

理解：首先name、sex、age相等，这个是查找重复的条件，而a.id=b.id 这样查找，条目数跟原有的表一样的条目，只是多了一些重复的列。而 a.id > b.id 相当于找到的是a.id较大的id，这样删除时，只保留最小的id。a.id < b.id 是找到较小的id，按a.id时，保留较小的id。另外，a.id中结果有重复。

查询重复的数据如下：

select a.*,b.* 
from user_info as a 
inner join user_info as b 
on a.name = b.name 
and a.sex = b.sex 
and a.age = b.age
and  a.id > b.id;

写法1：

删除重复数据SQL语句如下：

DELETE A .*
FROM
	user_info AS A
INNER JOIN user_info AS b ON A . NAME = b. NAME
AND A .sex = b.sex
AND A .age = b.age
AND A . ID > b. ID;

上面的写法，好像好像没有成功。

写法2：

查询待删除的重复数据SQL如下：

select * from user_info as a 
where a.id <> (
	select 
		min(b.id) 
	from user_info as b 
	where a.name = b.name
	and a.sex = b.sex
	and a.age = b.age
);

删除重复数据的SQL语句如下：

delete a.* from user_info as a
where a.id <> (
	select
		min(b.id)
	from (
		select * from user_info
	) as b
	where a.`name`= b.`name`
	and a.sex = b.sex
	and a.age = b.age
);

最后提醒一点： 在真正删除之前，记得对原数据备份一下。以便删除错误后，数据不能恢复回来。可以使用如下的语句来创建一个备份表，以便于在删除错误后，把数据恢复到原来的表中去。

create table user_info_bak as select * from user_info; --创建一个备份表
truncate table user_info; -- 清空原始表中的数据
insert into user_info select * from user_info_bak; -- 从备份表中把数据插入到原始表中

像上面这样操作，数据如果删除失误的时候，可以从user_info_bak中还原数据到user_info表中。

自己写的例子：

首先，表结构：

重复定义如下：ship_id、send_id、recv_id均相等，则认为重复了。

CREATE TABLE td_mail_02
(
    id bigserial,
    ship_id bigint NOT NULL,
    send_id varchar(24),
    recv_id varchar(24),
    kafka_time timestamp without time zone,
    insert_time timestamp without time zone,
    PRIMARY KEY ("id")
)
WITH (
    OIDS = FALSE
)
DISTRIBUTED BY (id);

查看需要删除的重复数据：

SELECT * from td_mail_02 where "id" BETWEEN 1 and 20  and id not in (
SELECT min("id") from td_mail_02 WHERE "id" BETWEEN 1 and 20  
GROUP BY
	ship_id,
	recv_id,
	send_id );

删除

DELETE from td_mail_02 where "id" BETWEEN 1 and 20  and id not in (
SELECT min("id") from td_mail_02 WHERE "id" BETWEEN 1 and 20  
GROUP BY
	ship_id,
	recv_id,
	send_id );

但是，此方法，只能保证局部BETWEEN 1 and 20 没有重复数据。如果是分批来删，只能保证每批里面没有重复数据，每披之间的数据无法保证是否重复。虽然有缺陷，但是按天删除重复数据，还是可以的。

自联表的查询

SELECT
	DISTINCT a."id"   -- 如果是* 显示全部
FROM
	td_mail_02 a
INNER JOIN td_mail_02 AS b ON a.ship_id = b.ship_id
AND a.recv_id = b.recv_id
AND a.send_id = b.send_id
AND a."id" > b."id"
WHERE
	a."id" BETWEEN 1
AND 8

配合脚本执行，将上一步的代码写入。

delete from td_mail_02 where id in (上一步结构)

或者直接点：

DELETE from td_mail_02 where id in (
SELECT
	DISTINCT A."id"
FROM
	td_mail_02 A
INNER JOIN td_mail_02 AS b ON A .ship_id = b.ship_id
AND A .recv_id = b.recv_id
AND A .send_id = b.send_id
AND A ."id" > b."id"
WHERE
	A ."id" BETWEEN 1
AND 8 )

我突然想到，用脚本来保存结果，其实，我也能将其写到一张临时表里面。然后下次直接从表里面删除掉。然后分两个脚本来操作。