SQL仅返回重复的行

基础概念

在SQL中，当需要仅返回表中重复的行时，通常是指查找在特定列或多个列组合中具有相同值的行。这与简单的去重操作(DISTINCT)相反，而是专门找出那些有重复值的记录。

实现方法

1. 使用GROUP BY和HAVING子句

这是最常用的方法，通过分组和计数来识别重复项：

SELECT column1, column2, ..., COUNT(*) as count
FROM table_name
GROUP BY column1, column2, ...
HAVING COUNT(*) > 1;

2. 使用窗口函数(适用于现代SQL数据库)

WITH duplicates AS (
    SELECT *,
           COUNT(*) OVER (PARTITION BY column1, column2, ...) as count
    FROM table_name
)
SELECT * FROM duplicates WHERE count > 1;

3. 使用自连接

SELECT DISTINCT a.*
FROM table_name a
JOIN table_name b ON a.column1 = b.column1 
                  AND a.column2 = b.column2
                  AND ...
                  AND a.primary_key <> b.primary_key;

应用场景

数据清洗：识别并处理重复数据
数据质量检查：确保关键字段的唯一性
数据分析：找出高频出现的记录模式
数据库维护：优化存储空间

常见问题及解决方案

问题1：查询结果包含所有重复行还是每组只显示一行？

原因：取决于查询写法
解决：如果需要显示所有重复行，可以使用子查询或窗口函数方法

问题2：如何确定哪些列用于判断重复？

原因：重复的定义取决于业务需求
解决：明确业务规则，选择适当的列组合

问题3：性能问题

原因：在大表上查找重复可能很慢
解决：在关键列上创建索引，或分批处理数据

完整示例

假设有一个员工表(employees)，要找出重复的姓名和部门组合：

-- 方法1: GROUP BY
SELECT first_name, last_name, department_id, COUNT(*) as duplicate_count
FROM employees
GROUP BY first_name, last_name, department_id
HAVING COUNT(*) > 1;

-- 方法2: 窗口函数
WITH dup_check AS (
    SELECT *,
           COUNT(*) OVER (PARTITION BY first_name, last_name, department_id) as dup_count
    FROM employees
)
SELECT * FROM dup_check WHERE dup_count > 1;

-- 方法3: 自连接
SELECT DISTINCT e1.*
FROM employees e1
JOIN employees e2 ON e1.first_name = e2.first_name
                 AND e1.last_name = e2.last_name
                 AND e1.department_id = e2.department_id
                 AND e1.employee_id <> e2.employee_id;

选择哪种方法取决于数据库类型、数据量和具体需求。窗口函数通常性能较好且更灵活，但需要数据库支持。