Oracle中如何查询多列组合存在重复的记录

基础信息 #

现有员工表emp，初始数据如下：

需求：返回first_name、last_name字段组合存在重复的所有记录，预期返回结果：

可用SQL写法 #

1. 窗口函数写法（推荐，支持MySQL8.0+、PostgreSQL、SQL Server等主流数据库新版本） #

SELECT Id, first_name, last_name, age
FROM (
    SELECT *,
           COUNT(*) OVER (PARTITION BY first_name, last_name) AS repeat_cnt
    FROM emp
) t
WHERE repeat_cnt > 1;

通过PARTITION BY按两个姓名字段分区计数，直接筛选计数大于1的记录即可，执行效率高，逻辑清晰。

2. IN子查询写法（兼容旧版数据库如MySQL5.x） #

SELECT *
FROM emp
WHERE (first_name, last_name) IN (
    SELECT first_name, last_name
    FROM emp
    GROUP BY first_name, last_name
    HAVING COUNT(*) > 1
);

先通过分组查询出所有存在重复的姓名组合，再匹配返回组合对应的全部记录。

3. JOIN关联写法（兼容旧版数据库） #

SELECT e1.*
FROM emp e1
INNER JOIN (
    SELECT first_name, last_name
    FROM emp
    GROUP BY first_name, last_name
    HAVING COUNT(*) > 1
) e2 
ON e1.first_name = e2.first_name 
AND e1.last_name = e2.last_name;

逻辑和IN写法一致，部分场景下JOIN的执行效率比IN更高。

注意：以上写法都会返回重复组内的所有记录，不会去重丢失数据，和需求的预期结果完全匹配。

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