删除重复记录，保留重复中的一条！-优快云博客

本文介绍使用SQL进行数据插入、查询及删除操作，并详细解释如何根据单个字段或组合字段来查找和删除重复记录的方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

CREATE TABLE bb1
(
name character varying,
value character varying,
id bigint
)

truncate bb1;

INSERT INTO bb1(name, value, id)VALUES ('a', '11', nextval('public.hibernate_sequence'));
INSERT INTO bb1(name, value, id)VALUES ('a', '12', nextval('public.hibernate_sequence'));
INSERT INTO bb1(name, value, id)VALUES ('a', '13', nextval('public.hibernate_sequence'));
INSERT INTO bb1(name, value, id)VALUES ('c', '22', nextval('public.hibernate_sequence'));
INSERT INTO bb1(name, value, id)VALUES ('c', '21', nextval('public.hibernate_sequence'));
INSERT INTO bb1(name, value, id)VALUES ('d', '31', nextval('public.hibernate_sequence'));
INSERT INTO bb1(name, value, id)VALUES ('e', '32', nextval('public.hibernate_sequence'));

truncate bb1;

INSERT INTO bb1(name, value, id)VALUES ('a', '11', 1);
INSERT INTO bb1(name, value, id)VALUES ('a', '12', 2);
INSERT INTO bb1(name, value, id)VALUES ('a', '13', 3);
INSERT INTO bb1(name, value, id)VALUES ('c', '21', 4);
INSERT INTO bb1(name, value, id)VALUES ('c', '22', 5);
INSERT INTO bb1(name, value, id)VALUES ('d', '31', 6);
INSERT INTO bb1(name, value, id)VALUES ('e', '32', 7);

select * form bb1

查询重复数据根据单个字段（name）判断

select name,count(*) from bb1 group by name having count(*)>1

删除重复数据,保留重复数据中ID最大的一个，根据单个字段（name）判断
delete from bb1 where name in (select name,count(*) from bb1 group by name having count(*)>1) and id not in(select max(id) from bb1 group by name having count(*)>1)

truncate bb1;

INSERT INTO bb1(name, value, id)VALUES ('a', '11', 1);
INSERT INTO bb1(name, value, id)VALUES ('a', '11', 2);
INSERT INTO bb1(name, value, id)VALUES ('a', '13', 3);
INSERT INTO bb1(name, value, id)VALUES ('c', '21', 4);
INSERT INTO bb1(name, value, id)VALUES ('c', '21', 5);
INSERT INTO bb1(name, value, id)VALUES ('d', '31', 6);
INSERT INTO bb1(name, value, id)VALUES ('e', '32', 7);

select * form bb1

查询重复数据根据多个字段（name,value）判断

select name,name,count(*) from bb1 group by name,value having count(*)>1

删除重复数据,保留重复数据中ID最大的一个，根据多个字段（name,value）判断

delete from bb1 a where (a.name,a.value) in (select name,value from bb1 group by name,value having count(*)>1) and a.id not in(select max(id) from bb1 group by name,value having count(*)>1)