如何在SQL中筛选出test_id重复出现的所有记录？

你遇到的问题是GROUP BY会将重复的test_id合并为单条记录，无法获取所有原始行，下面提供几种可行的实现方式：

方法1：子查询关联 #

先通过GROUP BY + HAVING找出所有重复的test_id，再关联原表获取对应的完整记录：

SELECT t.recnr, t.test_id
FROM your_table t
INNER JOIN (
    SELECT test_id
    FROM your_table
    GROUP BY test_id
    HAVING COUNT(*) > 1
) duplicate_test_ids ON t.test_id = duplicate_test_ids.test_id
ORDER BY t.test_id, t.recnr;

方法2：窗口函数（推荐） #

利用窗口函数COUNT() OVER(PARTITION BY test_id)直接计算每个test_id的出现次数，再筛选次数大于1的行，这种方式更直观，适合支持窗口函数的数据库（如MySQL 8.0+、PostgreSQL、SQL Server等）：

SELECT recnr, test_id
FROM (
    SELECT 
        recnr,
        test_id,
        COUNT(*) OVER(PARTITION BY test_id) AS test_id_count
    FROM your_table
) temp
WHERE test_id_count > 1
ORDER BY test_id, recnr;

方法3：EXISTS子查询 #

通过EXISTS检查当前行的test_id是否存在其他不同的记录，从而筛选出重复行：

SELECT recnr, test_id
FROM your_table t1
WHERE EXISTS (
    SELECT 1
    FROM your_table t2
    WHERE t2.test_id = t1.test_id
      AND t2.recnr != t1.recnr
)
ORDER BY test_id, recnr;

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