淘先锋技术网

首页 1 2 3 4 5 6 7

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;