如何在SQL中保留ID与Name组合重复的所有行（含原始记录）

API 探路者

2026-6-15

解决方案：保留ID+Name组合重复的行

要实现只保留ID和Name组合出现多次的行，你可以在原有查询基础上，通过窗口函数或分组关联的方式筛选目标数据，以下是两种常用方法：

方法一：用窗口函数COUNT()直接过滤

在原查询中添加COUNT(*) OVER(PARTITION BY "ID", "Name")统计每个分组的总记录数，再筛选出总记录数大于1的行：

WITH numbered_data AS (
    SELECT 
        *,
        ROW_NUMBER() OVER(PARTITION BY "ID", "Name" ORDER BY "date" ASC) AS row_number,
        COUNT(*) OVER(PARTITION BY "ID", "Name") AS group_count
    FROM table
)
SELECT ID, Name, date, row_number
FROM numbered_data
WHERE group_count > 1;

逻辑说明：

COUNT(*) OVER(PARTITION BY "ID", "Name")会计算每个ID+Name组合的总行数
WHERE group_count > 1直接过滤掉仅出现一次的组合（比如ID=2+Name1、ID=3+Name2这类）

如果你的SQL环境不支持CTE，也可以用子查询实现：

SELECT ID, Name, date, row_number
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER(PARTITION BY "ID", "Name" ORDER BY "date" ASC) AS row_number,
        COUNT(*) OVER(PARTITION BY "ID", "Name") AS group_count
    FROM table
) AS sub_query
WHERE group_count > 1;

方法二：分组关联过滤

先通过分组查询找出所有出现多次的ID+Name组合，再关联原表获取对应数据：

SELECT t.*,
       ROW_NUMBER() OVER(PARTITION BY t."ID", t."Name" ORDER BY t."date" ASC) AS row_number
FROM table t
INNER JOIN (
    SELECT "ID", "Name"
    FROM table
    GROUP BY "ID", "Name"
    HAVING COUNT(*) > 1
) AS duplicate_groups
    ON t."ID" = duplicate_groups."ID" 
    AND t."Name" = duplicate_groups."Name"
ORDER BY t."ID", t."Name", t."date" DESC;