PostgreSQL 查找重复数据（一） |
您所在的位置：网站首页 › 如何按两个条件查找重复数据 › PostgreSQL 查找重复数据（一） |
PostgreSQL 查找重复数据（一）
|
CREATE TABLE "public"."table2" (
"id" int4,
"at" timestamp(6)
)
;
ALTER TABLE "public"."table2"
OWNER TO "postgres";
INSERT INTO "public"."table2"("id", "at") VALUES (3148180, '2023-05-08 19:28:00');
INSERT INTO "public"."table2"("id", "at") VALUES (3148173, '2023-05-08 19:28:00');
INSERT INTO "public"."table2"("id", "at") VALUES (3148181, '2023-05-08 19:28:30');
INSERT INTO "public"."table2"("id", "at") VALUES (3148174, '2023-05-08 19:28:30');
INSERT INTO "public"."table2"("id", "at") VALUES (3148175, '2023-05-08 19:29:00');
INSERT INTO "public"."table2"("id", "at") VALUES (3148176, '2023-05-08 19:29:30');
INSERT INTO "public"."table2"("id", "at") VALUES (3148177, '2023-05-08 19:30:00');
INSERT INTO "public"."table2"("id", "at") VALUES (3148182, '2023-05-08 19:30:30');
INSERT INTO "public"."table2"("id", "at") VALUES (3148178, '2023-05-08 19:30:30');
INSERT INTO "public"."table2"("id", "at") VALUES (3148183, '2023-05-08 19:31:00');
INSERT INTO "public"."table2"("id", "at") VALUES (3148179, '2023-05-08 19:31:00');
INSERT INTO "public"."table2"("id", "at") VALUES (3148184, '2023-05-08 19:31:30');
使用聚合函数查找重复记录 基于单个字段的重复记录 如果想要找出 at重复的数据，可以基于该字段进行分组统计，并且返回行数大于 1 的分组 SELECT at, count(at) FROM table2 GROUP BY at HAVING count(at) > 1;
查询结果显示存在重复情况。如果想要查看完整的重复数据，可以使用子查询或者连接查询： SELECT * FROM table2 WHERE AT IN ( SELECT AT FROM table2 GROUP BY AT HAVING COUNT ( AT ) > 1 ) ORDER BY AT;或者： select * from table2 ou where (select count(*) from table2 inr where inr.at = ou.at) > 1或者： WITH d AS ( SELECT at FROM table2 GROUP BY at HAVING count(at) > 1) SELECT p.* FROM table2 p JOIN d ON (d.at = p.at) ORDER BY p.at;
另一种查找重复记录的方法就是直接使用自连接查询和 distinct 操作符： SELECT DISTINCT p.* FROM table2 p JOIN table2 d ON p.at = d.at WHERE p.id d.id ORDER BY p.at;
|
【本文地址】
公司简介
联系我们
今日新闻 |
点击排行 |
|
推荐新闻 |
图片新闻 |
|
专题文章 |