postgresql 刪除重復數據案例詳解

Posted on by

1.建表

/*
 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");

2.根據名稱獲取重復

先看看哪些數據重復瞭

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

輸出.

name        count

da              2

dd              2

3.刪除所有重復數據

註意把要更新的幾列數據查詢出來做為一個第三方表，然後篩選更新。

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

4.保留一行數據

這裡展示我們需要保留的數據：重復數據，保留ID最大那一條

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

5.刪除數據

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

到此這篇關於postgresql 刪除重復數據案例詳解的文章就介紹到這瞭,更多相關postgresql 刪除重復數據內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：