sql怎么查不重复的数据库

基础概念澄清

所谓「查不重复」本质包含两种语义：
目标导向型：仅返回某字段/字段组的唯一值集合（无需关联原始行）
典型场景：统计商品分类清单、城市列表等
全行保留型：在完整数据集基础上剔除完全重复的整条记录
典型场景：清理日志表中重复的用户操作记录

二者的核心差异在于是否保留非主键列的信息,本文将围绕这两种场景提供完整的解决方案。

---

核心实现方案详解

▶️ 方案1：DISTINCT基础用法（单字段/多字段去重）

特性	说明	示例
作用范围	作用于SELECT列表中指定的字段	SELECT DISTINCT city FROM orders;
空值处理	所有NULL被视为相同值	若存在多个NULL则只返回一次
排序控制	默认按升序排列，可通过ORDER BY调整	SELECT DISTINCT age ORDER BY age DESC
性能特点	自动创建临时哈希表存储已出现的值，时间复杂度O(n)	适合百万级以下数据量

进阶技巧：

-多字段组合去重（常用于复合主键场景）
SELECT DISTINCT country, city, zipcode 
FROM customers;

️ 注意：DISTINCT会忽略所有未被选中的字段,若需保留其他字段需改用下文方案3。

▶️ 方案2：GROUP BY分组聚合（精确控制输出列）

此方案通过分组机制实现更灵活的去重控制：

-基础写法：配合任意聚合函数使用
SELECT department, MAX(salary) AS max_salary, COUNT() AS emp_count
FROM employees
GROUP BY department;

关键优势：
️ 可自由组合非聚合列与聚合函数
️ 支持HAVING子句进行二次筛选
️ 能准确反映每组数据的统计特征

对比实验：
| 需求 | DISTINCT方案 | GROUP BY方案 |
|————————–|—————————–|——————————|
| 获取部门名称列表 | SELECT DISTINCT dept | SELECT dept FROM ... GROUP BY dept |
| 统计各部门平均工资 | 无法直接实现 | SELECT dept, AVG(salary) |
| 显示每个部门的最早入职日期| 无法关联具体员工信息 | SELECT dept, MIN(join_date) |

▶️ 方案3：窗口函数精准定位重复项（保留完整行信息）

当需要识别并处理重复行时,推荐使用窗口函数：

-标记重复的客户ID及其出现次数
SELECT 
    customer_id,
    name,
    email,
    COUNT() OVER (PARTITION BY customer_id) AS duplicate_count,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY create_time) AS row_num
FROM customer_registrations;

执行效果：

• duplicate_count >1表示该行存在重复
• row_num=1可提取每组的第一个有效记录
• 后续可通过外层查询过滤出唯一记录：

  WITH ranked_data AS (
    SELECT , ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY create_time) AS rn
    FROM customer_registrations
  )
  SELECT  FROM ranked_data WHERE rn = 1;

▶️ 方案4：自连接检测完全重复记录

适用于需要删除完全重复记录的场景：

-查找并删除完全重复的订单记录（保留最新一条）
DELETE t1 
FROM orders t1
INNER JOIN orders t2 
WHERE t1.order_id < t2.order_id AND t1.product_id = t2.product_id 
AND t1.customer_id = t2.customer_id AND t1.amount = t2.amount;

原理说明：
通过自连接比较相同条件下的不同记录，利用t1.order_id < t2.order_id确保每对重复记录只删除较早的那条。

---

特殊场景解决方案

跨表去重（关联查询场景）

-查询购买过电子产品且住在北京的唯一用户
SELECT DISTINCT u.user_id, u.username
FROM users u
JOIN orders o ON u.user_id = o.user_id
JOIN products p ON o.product_id = p.product_id
WHERE p.category = 'Electronics' AND u.city = 'Beijing';

优化建议：
️ 确保关联字段建立复合索引（如(user_id, product_id)）
️ 大数据量时优先考虑分页查询

模糊匹配去重（处理近似重复）

-查找姓名发音相似的用户（使用SOUNDEX函数）
SELECT DISTINCT user_id, username, SOUNDEX(username) AS soundex_code
FROM users
WHERE SOUNDEX(username) IN (
    SELECT SOUNDEX(username) 
    FROM users 
    GROUP BY SOUNDEX(username) 
    HAVING COUNT() > 1
);

适用场景：
客户重名校验、地址标准化等容错场景

---

性能优化指南

优化手段	实施建议	预期效果
索引建设	在DISTINCT/GROUP BY字段上创建单列或复合索引	查询速度提升5-10倍
限制返回列数	仅选择必要字段，避免SELECT	减少内存占用和磁盘I/O
分区表应用	对超大型表按时间/地域分区，并行扫描	亿级数据量下仍可快速响应
物化视图	对固定报表类查询预存结果	首次计算后后续查询毫秒级
执行计划分析	使用EXPLAIN查看查询执行路径，避免全表扫描	针对性优化低效操作符

---

相关问答FAQs

Q1: 为什么DISTINCT不能直接用于文本全文搜索？

A: DISTINCT基于精确匹配原则，而文本搜索涉及分词、同义词扩展等模糊匹配，解决方案是结合全文索引+GROUP BY：

SELECT term, doc_id, score 
FROM search_results 
WHERE matched_text @@ fulltext_index 
GROUP BY term;

Q2: 如何判断两个表之间的重复记录？

A: 使用INTERSECT运算符（MySQL用INNER JOIN模拟）：

-PostgreSQL/SQL Server语法
SELECT  FROM table1
INTERSECT
SELECT  FROM table2;
-MySQL兼容写法
SELECT t1. 
FROM table1 t1
INNER JOIN table2 t2 ON t1.id = t2.id AND t1.name = t2.name;

---

实战案例对比

假设有如下销售数据表sales(含100万条记录)：
| order_id | product_id | salesperson_id | amount | sale_date |
|———-|————|—————-|——–|—————-|
| 1 | A001 | S001 | 500 | 2023-01-05 |
| 2 | A001 | S002 | 300 | 2023-01-06 |
| 3 | B002 | S001 | 800 | 2023-01-07 |
| 4 | A001 | S001 | 500 | 2023-01-08 |

需求1：统计每个产品的销售总额（自然去重）

SELECT product_id, SUM(amount) AS total_sales
FROM sales
GROUP BY product_id; -正确结果：A001→1300, B002→800

需求2：找出销售员S001销售过的所有产品（不去重）

SELECT DISTINCT product_id 
FROM sales 
WHERE salesperson_id = 'S001'; -返回A001, B002

需求3：标记重复订单（同一产品同日多次销售）

SELECT , 
       LAG(amount) OVER (PARTITION BY product_id, sale_date) AS prev_amount,
       CASE WHEN amount = LAG(amount) OVER (PARTITION BY product_id, sale_date) THEN 'Duplicate' ELSE 'Unique' END AS status
FROM sales;

---

通过上述方案组合，可应对从简单到复杂的各种去重需求，实际开发中应根据数据量级、业务逻辑和性能要求选择合适的方法，必要时结合数据库特定功能（如Oracle的WM_CONCAT、PostgreSQL的array_agg）实现