PostgreSQL中如何用row_number()获取重复数据及对应首条记录

要获取每个重复组的首条记录，同时保留重复项以便对比非分区列（如column4）的差异，你可以通过以下两种方式实现：

方式一：获取重复组的所有记录（含首条） #

先筛选出存在重复的分组，再对这些分组内的记录进行编号，最终返回所有相关记录：

WITH duplicate_groups AS (
  -- 找出存在重复的COLUMN1/COLUMN2/COLUMN3组合
  SELECT COLUMN1, COLUMN2, COLUMN3
  FROM INNER_TB
  GROUP BY COLUMN1, COLUMN2, COLUMN3
  HAVING COUNT(*) > 1
),
ranked_records AS (
  -- 对重复分组内的记录编号，RN=1即为组内首条
  SELECT ITB.*,
         ROW_NUMBER() OVER(PARTITION BY COLUMN1, COLUMN2, COLUMN3 ORDER BY COLUMN1) AS RN
  FROM INNER_TB ITB
  JOIN duplicate_groups DG 
    ON ITB.COLUMN1 = DG.COLUMN1 
    AND ITB.COLUMN2 = DG.COLUMN2 
    AND ITB.COLUMN3 = DG.COLUMN3
)
-- 返回重复组的所有记录，方便对比非分区列差异
SELECT * FROM ranked_records;

方式二：首条记录与重复项横向对比 #

如果需要直接将首条记录和重复项的非分区列放在同一行对比，可以用自关联实现：

WITH ranked_records AS (
  SELECT ITB.*,
         ROW_NUMBER() OVER(PARTITION BY COLUMN1, COLUMN2, COLUMN3 ORDER BY COLUMN1) AS RN
  FROM INNER_TB ITB
)
SELECT 
  r1.id AS original_id, 
  r1.column4 AS original_column4,
  r2.id AS duplicate_id, 
  r2.column4 AS duplicate_column4,
  r1.column1, 
  r1.column2, 
  r1.column3
FROM ranked_records r1
JOIN ranked_records r2 
  ON r1.COLUMN1 = r2.COLUMN1 
  AND r1.COLUMN2 = r2.COLUMN2 
  AND r1.COLUMN3 = r2.COLUMN3
  AND r1.RN = 1 
  AND r2.RN > 1;

注意事项 #

• 代码中的ORDER BY COLUMN1可根据实际需求调整（比如按id、创建时间字段排序），确保RN=1的记录是你需要的基准记录。
• 若存在多个非分区列，只需在SELECT语句中添加对应字段即可。

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