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.根據(jù)名稱獲取重復(fù)
先看看哪些數(shù)據(jù)重復(fù)了
select name ,count(1) from test group by name having count(1)>1
輸出.
name count
da 2
dd 2
3.刪除所有重復(fù)數(shù)據(jù)
注意把要更新的幾列數(shù)據(jù)查詢出來做為一個(gè)第三方表，然后篩選更新。
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.保留一行數(shù)據(jù)
這里展示我們需要保留的數(shù)據(jù)：重復(fù)數(shù)據(jù)，保留ID最大那一條
SELECT 1. FROM test WHERE id NOT IN ( ( SELECT min( id ) AS id FROM test GROUP BY name ) )
5.刪除數(shù)據(jù)
DELETE FROM test WHERE id NOT IN ( SELECT t.id FROM ( SELECT max( id ) AS id FROM test GROUP BY name ) t )
到此這篇關(guān)于postgresql 刪除重復(fù)數(shù)據(jù)案例詳解的文章就介紹到這了,更多相關(guān)postgresql 刪除重復(fù)數(shù)據(jù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
標(biāo)簽：榆林 溫州 辛集 濮陽 寶雞 海西 杭州 昭通
巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《postgresql 刪除重復(fù)數(shù)據(jù)案例詳解》，本文關(guān)鍵詞 postgresql,刪除,重復(fù),數(shù)據(jù),；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題，煩請?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無關(guān)。
