postgresql 刪除重復(fù)數(shù)據(jù)案例詳解
發(fā)布日期：2022-01-21 17:34 | 文章來源：CSDN
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)容請(qǐng)搜索本站以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持本站！
版權(quán)聲明：本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請(qǐng)保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場(chǎng)，如有內(nèi)容涉嫌侵權(quán)，請(qǐng)聯(lián)系alex-e#qq.com處理。
相關(guān)文章
關(guān)注官方微信