distinct、exists、group by 、rowid对数据去重比较

最新推荐文章于 2025-12-02 20:26:41 发布

原创最新推荐文章于 2025-12-02 20:26:41 发布 · 1k 阅读

17 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #数据库

大数据同时被 2 个专栏收录

277 篇文章

订阅专栏

数据库

124 篇文章

订阅专栏

DISTINCT、EXISTS、GROUP BY 和 ROWID 都是 SQL 中用于数据去重的有效方法，但它们的工作原理、效率和适用场景有很大不同。

下面我将对这四种方法进行详细的比较和总结。

1. DISTINCT

工作原理：DISTINCT 关键字用于 SELECT 语句中，它会对查询结果的所有列进行整体比较，只返回唯一的行组合。

示例：

SELECT DISTINCT name, email, department
FROM employees;

这将返回 (name, email, department) 组合完全不同的所有记录。

优点：

简单直观：语法非常清晰，易于理解和编写。
标准通用：是所有 SQL 数据库都支持的标准语法。

缺点：

性能可能较低：当处理大数据集时，数据库需要对所有选定的列进行排序和比较，这可能比其它方法更耗资源。
无法选择非去重列：你只能选择那些用于去重的列。如果你想获取重复行中的其他列（例如，获取最新的或最早的记录ID），DISTINCT 无法直接做到。

适用场景：快速查询一个表中所有列或部分列的唯一组合，且对性能要求不高或数据量不大时。

2. GROUP BY

工作原理：GROUP BY 子句将具有相同值的行分组到一起，通常与聚合函数（如 MAX, MIN, COUNT, FIRST_VALUE 等）配合使用。通过将要去重的列作为分组字段，可以实现去重效果。

示例：

-- 简单去重，效果同 DISTINCT
SELECT name, email, department
FROM employees
GROUP BY name, email, department;

-- 高级用法：在去重的同时，获取每组中某列最大/最小的记录（这是DISTINCT做不到的）
SELECT id,
       name,
       email,
       department,
       MAX(hire_date) as latest_hire_date -- 获取每个组里最晚的入职日期
FROM employees
GROUP BY name, email, department; -- 假设name,email,department确定一个唯一的人

注意：在严格模式下（如 MySQL 的 ONLY_FULL_GROUP_BY），SELECT 后面的所有列都必须包含在 GROUP BY 子句中或是聚合函数的参数。

优点：

功能强大：不仅可以去重，还能同时获取每组的汇总信息（如最新日期、最大ID等）。
性能通常优于 DISTINCT：在很多数据库优化器中，GROUP BY 和 DISTINCT 的执行计划是相同的，但有时 GROUP BY 可以通过更好的索引利用来优化。
是实现“保留最新记录”等复杂去重的核心手段。

缺点：

语法稍复杂：需要理解分组和聚合函数的概念。
严格模式的限制：需要确保查询符合数据库的 GROUP BY 规则。

适用场景：

需要简单去重，并希望性能可能优于 DISTINCT 时。
需要进行复杂去重，例如“根据A、B列去重，但保留C列值最大的那条完整记录”。

3. EXISTS / NOT EXISTS

工作原理：这是一种基于子查询的去重方法，通常用于“保留最新”或“保留最早”的场景。它的逻辑是：“选择那些不存在比它更新的重复记录的记录”。

示例（保留每个 email 对应的最新记录）：

SELECT *
FROM employees e1
WHERE NOT EXISTS (
    SELECT 1
    FROM employees e2
    WHERE e2.email = e1.email -- 关联条件：找到重复的行
    AND e2.hire_date > e1.hire_date -- 排除条件：存在比当前行更新的记录
);
-- 意思是：找出所有这样的记录e1：在表中不存在另一条记录e2，
-- e2的email和e1相同且e2的入职日期比e1更晚。

优点：

极致灵活：可以实现非常复杂和自定义的去重逻辑。
可读性高：对于熟悉 SQL 的人来说，这种“不存在”的逻辑非常清晰。

缺点：

性能可能很差：对于每一行主查询，都可能要执行一次子查询（相关子查询），如果表很大，会导致大量的 IO 操作，性能急剧下降。
语法最复杂：需要编写和理解子查询和关联条件。

适用场景：处理非常复杂的去重逻辑，且其他方法难以实现时。在数据量大的情况下应谨慎使用，并确保关联字段上有索引。

4. ROWID (Oracle 特有)

工作原理：在 Oracle 数据库中，每一行数据都有一个唯一的物理地址标识符 ROWID。去重时，我们可以先通过 GROUP BY 找到需要保留的组的最大或最小 ROWID（通常代表最新或最早物理插入的行），然后删除那些 ROWID 不在此范围内的重复行。

示例（删除 email 重复的行，只保留 ROWID 最小的那条）：

DELETE FROM employees
WHERE ROWID NOT IN (
    SELECT MIN(ROWID)
    FROM employees
    GROUP BY email -- 按email去重
);

优点：

效率极高：对于删除重复数据的操作，这是 Oracle 下公认的最高效的方法之一。因为它直接操作物理地址，速度非常快。
非常适用于数据清理。

缺点：

数据库特定：仅适用于 Oracle。其他数据库有类似概念但名称不同（如 PostgreSQL 的 ctid，SQL Server 的 %%physloc%%，但一般不用于生产环境的去重操作）。
主要用于删除：它更常用于 DELETE 语句中物理删除重复行，而不是在 SELECT 查询中简单地显示不重复的数据。

适用场景：专门在 Oracle 数据库中，需要高效地永久删除重复记录。

总结对比表格

方法	优点	缺点	适用场景
`DISTINCT`	简单直观，通用标准	性能可能一般；无法获取非去重列	快速简单查询唯一值组合
`GROUP BY`	功能强大，性能通常较好；可实现复杂去重	语法稍复杂，需注意严格模式	最常用、最通用的去重方法，尤其适合“保留极值”
`EXISTS`	逻辑灵活，可实现非常复杂的去重规则	性能通常较差（相关子查询），语法复杂	复杂自定义去重逻辑，小数据量
`ROWID`	Oracle下删除重复数据效率极高	仅限Oracle；主要用于删除而非查询	Oracle数据库专用数据清理