找回密码
立即注册
搜索
热搜: Java Python Linux Go
云栈社区»论坛 › 技术文档「 Note & Doc 」 › SQL去重方法全解析：从DISTINCT到窗口函数的实战指南与应用场景 ...
发回帖 发新帖
0xBADDCAFE

2208

积分

0

好友

314

主题

SQL去重方法全解析：从DISTINCT到窗口函数的实战指南与应用场景

发表于 前天 00:16 | 查看: 7| 回复: 0

数据重复是数据库操作中常见的问题，它可能导致统计分析失真、存储空间冗余等诸多麻烦。为了解决这些困扰，我们需要系统掌握 SQL 中的各种去重工具。本文将带你深入了解 6 个核心的 SQL 去重关键字或操作符，并梳理出一套从基础到进阶的完整去重方案。无论你是想快速消除单表重复，还是需要处理复杂的多结果集，都能从中找到精准的解决方法。（所有代码块与表格均可左右滚动）

1. DISTINCT：单表基础去重

DISTINCT 是 SQL 中最基础的去重关键字，用于消除查询结果集中的重复行。它作用于整个 SELECT 子句返回的所有列，仅保留组合值完全唯一的行。如果查询多列，则需要所有列的值都相同，才会被视为重复行。例如 SELECT DISTINCT col1, col2 FROM table 会对 col1 和 col2 的组合进行去重。

需要注意的是，DISTINCT 在去重时通常会隐式地对结果进行排序，这可能会在大数据量场景下影响性能，建议结合适当的索引进行优化。

原理：直接在查询结果集上过滤掉重复行，保留唯一值。
语法：SELECT DISTINCT column1, column2 FROM table;

模拟数据：

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50)
);

INSERT INTO employees VALUES
(1, ‘Alice’, ‘HR’),
(2, ‘Bob’, ‘Engineering’),
(3, ‘Alice’, ‘HR’),   -- 姓名重复
(4, ‘Charlie’, ‘Marketing’),
(5, ‘Bob’, ‘Engineering’); -- 姓名+部门重复

查询示例：

-- 单列去重
SELECT DISTINCT name FROM employees;

/* 输出：
+----------+
| name     |
+----------+
| Alice    |
| Bob      |
| Charlie  |
+----------+
注释：重复的 ‘Alice’ 和 ‘Bob’ 被合并 */
-- 多列联合去重
SELECT DISTINCT name, department FROM employees;

/* 输出：
+----------+---------------+
| name     | department    |
+----------+---------------+
| Alice    | HR            |
| Bob      | Engineering   |
| Charlie  | Marketing     |
| Bob      | Engineering   | -- 此行保留（因id不同）
+----------+---------------+
注释：多列去重需所有字段值完全相同 */

2. GROUP BY：分组去重

GROUP BY 通过按指定字段分组来实现去重，它通常与聚合函数（如 COUNT、MAX）结合使用来处理分组后的数据。其基本语法是 SELECT 列 FROM 表 GROUP BY 列，结果为每个分组保留唯一的一行。例如，按部门分组统计人数，GROUP BY 比 DISTINCT 更灵活，因为它支持对分组数据进行复杂的计算，在大数据量下，如果相关字段有索引，其性能往往更优。

原理：按指定字段分组，每组只输出一行，实现去重并支持聚合计算。
语法：SELECT column1, COUNT(*) FROM table GROUP BY column1;

查询示例：

-- 统计每个部门的员工数
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

/* 输出：
+---------------+----------------+
| department    | employee_count |
+---------------+----------------+
| HR            | 2              |
| Engineering   | 2              |
| Marketing     | 1              |
+---------------+----------------+
注释：按部门分组并计数 */

GROUP BY 与 DISTINCT 对比：
两者都能实现去重。GROUP BY 是按字段分组，天然适合需要统计分析（如计数、求和）的场景；而 DISTINCT 是直接返回唯一行，语法更简洁。在大数据量时，GROUP BY 的性能可能更优（尤其在有索引的情况下），且结果通常是排序的。DISTINCT 则作用于所有选中列的组合，没有明确的分组逻辑。

3. UNION 与 UNION ALL：多结果集去重

UNION 和 UNION ALL 都是用于合并多个查询结果集的操作符。使用前必须确保各个查询返回的列数、列顺序和数据类型完全一致。

  • UNION：会对合并后的结果集自动去除重复行，相当于先合并再进行全局去重。这个去重过程可能涉及排序，因此会消耗更多性能。
  • UNION ALL：直接合并所有结果，包括重复行，不进行任何去重处理，因此执行效率更高。

在实际开发中，如果确认各个结果集之间没有重复，或者不需要去重，应优先使用 UNION ALL 来提升查询性能。

原理：

  • UNION：拼接结果集后进行全局去重。
  • UNION ALL：直接拼接，保留所有重复项。

模拟数据：

CREATE TABLE hr_dept (id INT, name VARCHAR(50));
CREATE TABLE eng_dept (id INT, name VARCHAR(50));

INSERT INTO hr_dept VALUES (1, ‘Alice’), (2, ‘David’);
INSERT INTO eng_dept VALUES (2, ‘David’), (3, ‘Bob’), (1, ‘Alice’);

查询对比：

-- UNION：自动去重
SELECT name FROM hr_dept
UNION
SELECT name FROM eng_dept;

/* 输出：
+-------+
| name  |
+-------+
| Alice |
| David |
| Bob   |
+-------+ */
-- UNION ALL：保留所有记录
SELECT name FROM hr_dept
UNION ALL
SELECT name FROM eng_dept;

/* 输出：
+-------+
| name  |
+-------+
| Alice |
| David |
| David |
| Bob   |
| Alice |
+-------+ */

性能提示：

  • UNION 需要排序去重，处理大表时需谨慎使用。
  • UNION ALL 效率更高，因为它没有额外的去重操作。

4. EXCEPT / MINUS：差集去重

EXCEPT (或 MINUS) 是 SQL 中用于求差集的操作符，它返回第一个查询结果中存在但第二个查询结果中不存在的记录，并自动去重。

  • EXCEPT 在 SQL Server、PostgreSQL 等数据库中支持。
  • MINUS 是 Oracle 数据库中 EXCEPT 的等价操作符。

这两个操作同样要求两个查询的列数、类型一致。请注意，MySQL 不支持 EXCEPT 或 MINUS，需要利用 LEFT JOIN 或子查询来模拟实现。这个操作非常适合用于查找两个数据集之间的差异。

原理：返回第一个结果集剔除掉第二个结果集中重复项后的部分。
数据库支持：

数据库 语法
SQL Server EXCEPT
Oracle MINUS
MySQL ❌（需模拟）

模拟数据：

CREATE TABLE all_products (id INT, name VARCHAR(50));
CREATE TABLE sold_products (id INT, name VARCHAR(50));

INSERT INTO all_products VALUES (1, ‘Laptop’), (2, ‘Phone’), (3, ‘Tablet’);
INSERT INTO sold_products VALUES (1, ‘Laptop’), (3, ‘Tablet’);

标准实现：

-- SQL Server/Oracle
SELECT * FROM all_products
EXCEPT
SELECT * FROM sold_products;  -- Oracle用MINUS

/* 输出：
+----+-------+
| id | name  |
+----+-------+
| 2  | Phone |
+----+-------+ */

MySQL替代方案：

-- LEFT JOIN模拟
SELECT ap.* 
FROM all_products ap
LEFT JOIN sold_products sp ON ap.id = sp.id
WHERE sp.id IS NULL;

-- NOT IN模拟
SELECT * FROM all_products
WHERE id NOT IN (SELECT id FROM sold_products);

5. INTERSECT：交集去重

INTERSECT 是 SQL 中求交集的操作符，它返回两个查询结果集中共有的记录，并自动去重。使用前提同样是两个查询的列数、顺序和数据类型必须一致。

典型应用场景是查找同时存在于两个表中的数据，例如查找既是会员又是活跃用户的 ID。

请注意，MySQL 也不支持 INTERSECT，需要借助 INNER JOIN 或 IN 子查询来模拟。而 Oracle、SQL Server 等主流数据库都提供了原生支持。其性能同样依赖于索引，大数据集下应谨慎评估。

原理：返回两个结果集的共同项（自动去重）。
数据库支持：

数据库 支持情况
SQL Server ✅
Oracle ✅
MySQL ❌

模拟数据：

CREATE TABLE all_members (id INT, name VARCHAR(50));
CREATE TABLE active_members (id INT, name VARCHAR(50));

INSERT INTO all_members VALUES (1, ‘Tom’), (2, ‘Jerry’), (3, ‘Spike’);
INSERT INTO active_members VALUES (1, ‘Tom’), (3, ‘Spike’), (4, ‘Tyke’);

标准实现：

-- SQL Server/Oracle
SELECT * FROM all_members
INTERSECT
SELECT * FROM active_members;

/* 输出：
+----+-------+
| id | name  |
+----+-------+
| 1  | Tom   |
| 3  | Spike |
+----+-------+ */

MySQL替代方案：

-- INNER JOIN模拟
SELECT am.* 
FROM all_members am
INNER JOIN active_members ac ON am.id = ac.id;

-- IN子查询模拟
SELECT * FROM all_members
WHERE id IN (SELECT id FROM active_members);

小贴士：了解不同数据库（如 MySQL, PostgreSQL 等）对高级 SQL 操作符的支持差异，是写出高效、可移植 SQL 的关键一步。

6. 高级去重技巧

除了上述标准关键字，我们还可以利用更强大的 SQL 特性解决复杂的去重需求。

1. 窗口函数去重
窗口函数，特别是 ROW_NUMBER()，是实现复杂条件去重的利器。例如，我们可以为每个部门的记录按入职日期排序编号，然后只取最新的一条。

-- 保留每个部门最新入职记录
SELECT * FROM (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY department 
      ORDER BY hire_date DESC
    ) AS rn
  FROM employees
) t WHERE rn = 1;

2. 子查询去重
子查询可以灵活地作为过滤条件，实现基于关联关系的去重。

-- 查找有订单的用户
SELECT * FROM users 
WHERE user_id IN (
  SELECT DISTINCT user_id FROM orders
);

3. DELETE去重
有时我们需要从物理上删除表中的重复数据，可以使用 DELETE 配合子查询。

-- 删除重复邮箱记录，保留ID最小的那条
DELETE FROM users 
WHERE user_id NOT IN (
  SELECT MIN(user_id) 
  FROM users 
  GROUP BY email
);

7. 数据库去重差异参考表

不同数据库管理系统对去重相关功能的支持各有不同，了解这些差异有助于编写可移植的 SQL。

功能 MySQL Oracle SQL Server
EXCEPT ❌ (LEFT JOIN模拟) ✅ (MINUS) ✅
INTERSECT ❌ (JOIN模拟) ✅ ✅
ROW_NUMBER 8.0+ ✅ ✅ ✅
ROWID ❌ ✅ ❌ (ROWVERSION替代)

8. 去重性能优化指南

  1. 索引策略：
    为去重操作所基于的字段创建索引可以大幅提升性能。

    -- 为去重字段创建索引
CREATE INDEX idx_dept ON employees(department);

  2. 方法选择：

    • 小数据量：优先使用 DISTINCT，语法简洁。
    • 大数据量：
      • 考虑使用 GROUP BY（如果涉及聚合）。
      • 用 EXISTS 替代 IN 子查询。
      • 避免 SELECT *，只选取必要的字段。
      • 对于海量数据，可以考虑分批处理（例如使用 LIMIT 分页循环）。

  3. 去重代价排序（效率大致从高到低）：
    UNION ALL > DISTINCT ≈ GROUP BY > UNION > EXCEPT/INTERSECT

9. 去重应用场景速查表

下表为你快速匹配不同业务场景下的推荐去重方法。

场景 推荐方法 示例片段
单表去重 DISTINCT/GROUP BY SELECT DISTINCT dept FROM emp
多表合并去重 UNION SELECT col FROM A UNION SELECT col FROM B
保留最新记录 ROW_NUMBER() PARTITION BY id ORDER BY date DESC
删除重复数据 DELETE + 子查询 DELETE WHERE id NOT IN (SELECT MIN(id)...)
查找差异数据 EXCEPT/MINUS SELECT FROM A EXCEPT SELECT FROM B
查找交集数据 INTERSECT SELECT FROM A INTERSECT SELECT FROM B

10. SQL去重方法总结

SQL 的去重方法主要可以归纳为三类：

  1. 基础去重：DISTINCT 和 GROUP BY，适合单表操作。
  2. 集合运算：UNION / EXCEPT / INTERSECT，专为处理多个结果集之间的关系设计。
  3. 高级技巧：窗口函数和子查询，用于解决条件更复杂的去重场景。

在选择具体方法时，你需要综合考虑：

  • 数据量大小：小数据用 DISTINCT，大数据多考虑 GROUP BY 和索引。
  • 数据库支持：特别注意 MySQL 对一些操作符（如 EXCEPT, INTERSECT）的原生支持缺失。
  • 业务需求：是否需要聚合计算？是否需要物理删除重复数据？

掌握这些方法的原理、差异及组合使用方式，你就能高效应对实际工作中遇到的各类数据重复问题，有效提升数据质量与查询性能。如果想深入探讨数据库的更多优化技巧，欢迎在 云栈社区 与广大开发者一起交流学习。




上一篇：TaskExplorer：比Windows自带更强的开源高级任务管理器与系统监控工具
下一篇：Linux date命令格式化输出详解：从%Y到%T的日期时间自定义指南
SQL, MySQL, PostgreSQL, 数据去重, 数据库优化

相关帖子
返回列表
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 立即注册

手机版|小黑屋|网站地图|云栈社区 ( 苏ICP备2022046150号-2 )

GMT+8, 2026-1-14 17:27 , Processed in 0.290758 second(s), 39 queries , Gzip On.

Powered by Discuz! X3.5

© 2025-2025 云栈社区.

快速回复 返回顶部 返回列表