SQL通用查询方案：筛选同列重复但指定列唯一的非重复行

具体场景修正方案 #

你的原查询会返回所有满足「同一email、type但不同id」的行，也就是每个重复组（email+type）下的所有id对应的行都会被列出。根据需求，这里提供几种针对性修正方案：

方式1：获取重复分组及对应所有许可ID（排查首选） #

这种方式直接展示每个重复分组的核心信息，以及所有重复的许可ID，更便于快速排查重复付费情况：

SELECT 
    email, 
    type, 
    array_agg(id ORDER BY id) AS duplicate_license_ids,
    COUNT(id) AS duplicate_count
FROM Licenses
GROUP BY email, type
HAVING COUNT(id) > 1
ORDER BY email;

方式2：保留每个重复组的单条完整行记录 #

如果需要返回完整的行数据，但每个重复组只保留一条（示例中按id取最早创建的许可）：

SELECT *
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY email, type ORDER BY id) AS row_num
    FROM Licenses
    WHERE EXISTS (
        SELECT 1 
        FROM Licenses t2
        WHERE t2.email = Licenses.email 
          AND t2.type = Licenses.type
          AND t2.id <> Licenses.id
    )
) sub_query
WHERE row_num = 1
ORDER BY email;

方式3：PostgreSQL专属简化写法 #

若使用PostgreSQL，可通过DISTINCT ON快速实现每个分组取首行：

SELECT DISTINCT ON (email, type) *
FROM Licenses
WHERE EXISTS (
    SELECT 1 
    FROM Licenses t2
    WHERE t2.email = Licenses.email 
      AND t2.type = Licenses.type
      AND t2.id <> Licenses.id
)
ORDER BY email, type, id;

如果仅需保留所有重复组的行但确保无重复记录（原查询其实已通过id唯一性实现，若需更严谨可添加DISTINCT）：

SELECT DISTINCT * 
FROM Licenses t1
WHERE EXISTS (
    SELECT 1 FROM Licenses t2
    WHERE t1.email = t2.email 
      AND t1.type = t2.type
      AND t1.id <> t2.id
) 
ORDER BY email;

通用解决思路 #

这类问题的核心是基于指定列分组，判断分组内存在不同的唯一标识列，通用解决步骤如下：

1. 明确核心列定义

   • 分组列（G列）：用于判断重复的维度列，如示例中的email、type
   • 唯一列（U列）：需排除的重复标识列，如示例中的id（通常为主键或唯一值）

2. 筛选存在重复的分组

   • 方法A：EXISTS子查询
     判断当前行的G列存在其他行，G列相同但U列不同，适合需要返回完整行数据的场景
   • 方法B：GROUP BY + HAVING
     对G列分组，通过COUNT(DISTINCT U列) > 1直接筛选出存在多个不同U列的分组，适合统计分组重复情况的场景

3. 按需输出结果

   • 若需所有符合条件的行：用EXISTS关联或JOIN分组结果
   • 若需每个分组唯一记录：用窗口函数（ROW_NUMBER/RANK）、数据库专属语法（如PostgreSQL的DISTINCT ON），或GROUP BY取聚合值（如MIN(U列)、MAX(U列)）

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