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

  )