解析postgresql 删除重复数据案例

这篇文章主要介绍了postgresql 删除重复数据案例详解,本篇文章通过简要的案例,讲解了该项技术的了解与使用,以下就是详细内容,需要的朋友可以参考下

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 删除重复数据案例详解的文章就介绍到这了。

原文来自：https://www.jb51.net/article/218647.htm
本文地址：https://www.linuxprobe.com/postgresql-linux-go.html编辑：向金平，审核员：逄增宝
Linux命令大全：https://www.linuxcool.com/