PostgreSQL如何查询列count与distinct count差值对应的重复数据？

查询重复的capture_time及重复次数 #

执行以下语句可以先定位所有出现过重复的capture_time值，以及每个值的出现次数：

SELECT capture_time, COUNT(*) AS occurrence_count
FROM 替换为你的实际表名
GROUP BY capture_time
HAVING COUNT(*) > 1;

所有返回行的occurrence_count - 1相加总和就是你计算得到的差值36026。

查询所有对应重复值的原始行 #

如果你需要获取这些重复capture_time关联的全部原始行数据，可使用以下两种通用写法：

• 写法1：IN子查询（兼容所有主流SQL数据库）

SELECT *
FROM 替换为你的实际表名
WHERE capture_time IN (
    SELECT capture_time
    FROM 替换为你的实际表名
    GROUP BY capture_time
    HAVING COUNT(*) > 1
);

• 写法2：窗口函数（适用于MySQL 8.0+、PostgreSQL、SQL Server等支持窗口函数的数据库）

SELECT * FROM (
    SELECT *,
           COUNT(*) OVER (PARTITION BY capture_time) AS same_time_total
    FROM 替换为你的实际表名
) AS temp_table
WHERE same_time_total > 1;

精准查询差值对应的36026行多余重复数据 #

如果需要直接得到和差值数量完全一致的、重复出现的多余行（排除每个capture_time第一次出现的行），可使用窗口函数实现：

SELECT * FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY capture_time ORDER BY capture_time) AS row_rank
    FROM 替换为你的实际表名
) AS temp_table
WHERE row_rank > 1;

内容的提问来源于stack exchange，提问作者Shadi