1.建表
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
|
/* 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.根據名稱獲取重復
先看看哪些數據重復了
|
1
|
select name ,count(1) from test group by name having count(1)>1 |
輸出.
name count
da 2
dd 2
3.刪除所有重復數據
注意把要更新的幾列數據查詢出來做為一個第三方表，然后篩選更新。
|
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) |
4.保留一行數據
這里展示我們需要保留的數據：重復數據，保留ID最大那一條
|
1
2
3
4
5
6
7
8
|
SELECT 1. FROM test WHERE id NOT IN ( ( SELECT min( id ) AS id FROM test GROUP BY name ) ) |
5.刪除數據
|
1
2
3
4
5
6
7
8
9
10
|
DELETEFROM test WHERE id NOT IN ( SELECT t.id FROM ( SELECT max( id ) AS id FROM test GROUP BY name ) t ) |
到此這篇關于postgresql 刪除重復數據案例詳解的文章就介紹到這了,更多相關postgresql 刪除重復數據內容請搜索服務器之家以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持服務器之家！
原文鏈接：https://blog.csdn.net/weixin_43632687/article/details/119239104
