删除重复记录，保留重复中的一条！

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)