如何在SQL中查询同表内name、email、number重复的所有行

嘿，这个需求我熟！想要找出name、email、number完全重复的所有行（包括每一条重复记录），用GROUP BY确实搞不定，自连接又太麻烦，给你两个简洁好用的方案，尤其是第一个用窗口函数的方法，超省心！

原始数据表 #

name email number type
1 abc@example.com 10 A
1 abc@example.com 10 B
2 def@def.com 20 B
3 ggg@ggg.com 30 B
1 abc@example.com 10 A
4 hhh@hhh.com 60 A

期望结果 #

name email number type
1 abc@example.com 10 A
1 abc@example.com 10 B
1 abc@example.com 10 A

解决方案1：使用窗口函数（推荐） #

这是最简洁高效的方法，不需要分组或者复杂的自连接，用COUNT() OVER(PARTITION BY ...)统计每个组合的出现次数，再筛选出次数大于1的所有行：

SELECT name, email, number, type
FROM (
    SELECT 
        name, email, number, type,
        -- 按name、email、number分组，统计每组的总行数
        COUNT(*) OVER (PARTITION BY name, email, number) AS duplicate_count
    FROM your_table_name -- 替换成你的实际表名
) AS temp
WHERE duplicate_count > 1;

原理说明：

• PARTITION BY name, email, number会把数据按这三个字段的组合分成不同的组
• COUNT(*) OVER()会计算每个组里的总行数，赋值给duplicate_count
• 最后筛选出duplicate_count > 1的行，也就是所有属于重复组合的记录，自然保留了每行的type字段

解决方案2：使用EXISTS子查询 #

如果你的数据库不支持窗口函数（比如一些老版本的数据库），可以用这个方法，通过检查是否存在其他相同组合的行来筛选：

SELECT t1.name, t1.email, t1.number, t1.type
FROM your_table_name t1
WHERE EXISTS (
    SELECT 1
    FROM your_table_name t2
    WHERE t2.name = t1.name
      AND t2.email = t1.email
      AND t2.number = t1.number
      -- 排除当前行本身，这里用主键或数据库自带的行标识
      -- MySQL用t2.id != t1.id（若有主键id），Oracle用t2.ROWID != t1.ROWID，PostgreSQL用t2.ctid != t1.ctid
      AND t2.id != t1.id
);

这个方法的逻辑是：对于每一行t1，只要存在另一行t2和它的name、email、number完全相同，就保留t1。需要注意的是，你得有一个唯一标识每行的字段（比如主键），如果没有的话，窗口函数的方法会更靠谱。

测试一下这两个方法，用你给的原始数据，都会得到你想要的结果哦！

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