共计 1564 个字符,预计需要花费 4 分钟才能阅读完成。
导读 | 这篇文章主要介绍了 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 删除重复数据案例详解的文章就介绍到这了。
正文完
星哥说事-微信公众号
![post-qrcode](https://www.xgss.net/wp-content/uploads/2023/07/二维码-微信公众号256x256.jpg)