postgresql 删除重复数据案例详解_PostgreSQL_

 /* Navicat Premium Data Transfer Source Server : localhost Source Server Type : PostgreSQL Source Server Version : 110012 Source Host : localhost:5432 Source Catalog : postgres Source Schema : public Target Server Type : PostgreSQL Target Server Version : 110012 File Encoding : 65001 Date: 30/07/2021 10:10:04 */ -- ---------------------------- -- Table structure for test -- ---------------------------- DROP TABLE IF EXISTS "public"."test"; CREATE TABLE "public"."test" ( "id" int4 NOT NULL DEFAULT NULL, "name" varchar(255) COLLATE "pg_catalog"."default" DEFAULT NULL, "age" int4 DEFAULT NULL ) ; -- ---------------------------- -- Records of test -- ---------------------------- INSERT INTO "public"."test" VALUES (1, 'da', 1); INSERT INTO "public"."test" VALUES (2, 'da', 12); INSERT INTO "public"."test" VALUES (3, 'dd', 80); INSERT INTO "public"."test" VALUES (4, 'dd', 80); INSERT INTO "public"."test" VALUES (5, 'd1', 13); -- ---------------------------- -- Primary Key structure for table test -- ---------------------------- ALTER TABLE "public"."test" ADD CONSTRAINT "test_pkey" PRIMARY KEY ("id"); 

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

 delete from test where name in (select t.name from (select name ,count(1) from test group by name having count(1)>1) t) 

 SELECT 1. FROM test WHERE id NOT IN ( ( SELECT min( id ) AS id FROM test GROUP BY name ) ) 

 DELETE FROM test WHERE id NOT IN ( SELECT t.id FROM ( SELECT max( id ) AS id FROM test GROUP BY name ) t )