SQLServer去重方法，以及适用范围

一、去重的核心逻辑

去重的本质是 识别重复数据 并 选择保留或删除策略。需明确：

1. 重复的定义：哪些列的组合视为重复？
2. 保留规则：保留第一条、最后一条，还是随机一条？
3. 操作类型：仅查询去重，还是物理删除数据？

---

二、详细方法及适用场景

方法 1：DISTINCT 关键字

• 场景：仅查询结果去重，不修改原始数据。

• 步骤：

  -- 单列去重
  SELECT DISTINCT column_name FROM table_name;
  
  -- 多列组合去重
  SELECT DISTINCT column1, column2 FROM table_name;
  

• 注意事项：

  • 若查询包含非去重列，DISTINCT 会失效。
  • 性能问题：当数据量极大时，DISTINCT 可能导致排序和哈希操作，消耗资源。

---

方法 2：GROUP BY 聚合

• 场景：去重同时需要聚合其他列（如统计数量）。

• 步骤：

  SELECT column1, column2, COUNT(*) AS duplicate_count
  FROM table_name
  GROUP BY column1, column2
  HAVING COUNT(*) > 1;  -- 可选：仅显示重复项
  

• 优化技巧：

  • 若不需要统计数量，GROUP BY 性能可能优于 DISTINCT（取决于执行计划）。

---

方法 3：ROW_NUMBER() 窗口函数

• 场景：需要保留特定记录（如最新或最早的数据）。

• 步骤：

  WITH CTE AS (
      SELECT 
          *,
          ROW_NUMBER() OVER (
              PARTITION BY column1, column2  -- 定义重复列
              ORDER BY create_time DESC      -- 按时间倒序，保留最新一条
          ) AS rn
      FROM table_name
  )
  DELETE FROM CTE WHERE rn > 1;  -- 删除重复项（保留第一条）
  

• 关键点：

  • PARTITION BY：定义重复的列组合。
  • ORDER BY：决定保留哪一条（如时间戳、自增ID）。
  • 使用 DELETE 时务必先备份数据！

---

方法 4：物理删除重复数据

• 场景：永久删除表中重复数据。

• 步骤：

  -- 步骤1：确认重复数据
  SELECT column1, column2, COUNT(*)
  FROM table_name
  GROUP BY column1, column2
  HAVING COUNT(*) > 1;
  
  -- 步骤2：使用 CTE 删除重复数据
  WITH CTE AS (
      SELECT 
          *,
          ROW_NUMBER() OVER (PARTITION BY column1, column2 ORDER BY id) AS rn
      FROM table_name
  )
  DELETE FROM CTE WHERE rn > 1;
  

• 注意事项：

  • 删除前使用 BEGIN TRANSACTION 并测试 ROLLBACK。
  • 对大表操作时，分批删除（如每次删除 1000 行）可减少锁争用。

---

方法 5：创建唯一索引/约束

• 场景：预防未来插入重复数据。

• 步骤：

  -- 检查现有重复数据
  IF NOT EXISTS (
      SELECT column1, column2 
      FROM table_name 
      GROUP BY column1, column2 
      HAVING COUNT(*) > 1
  )
  BEGIN
      -- 添加唯一约束
      ALTER TABLE table_name
      ADD CONSTRAINT UQ_table_name_column1_column2 UNIQUE (column1, column2);
  END
  

• 注意：

  • 若表中已有重复数据，需先清理再添加约束。
  • 唯一索引可提升查询性能。

---

三、进阶去重技巧

1. 多步骤复杂去重

• 场景：多列组合部分重复，需保留完整记录。

  -- 示例：保留 name + email 组合的最新记录
  WITH RankedData AS (
      SELECT 
          *,
          ROW_NUMBER() OVER (
              PARTITION BY name, email 
              ORDER BY update_time DESC
          ) AS rn
      FROM users
  )
  DELETE FROM RankedData WHERE rn > 1;
  

2. 使用临时表去重

• 场景：超大数据量时避免锁表。

  -- 步骤1：将去重数据插入临时表
  SELECT DISTINCT * INTO #temp_table FROM original_table;
  
  -- 步骤2：清空原表并重新插入
  TRUNCATE TABLE original_table;
  INSERT INTO original_table SELECT * FROM #temp_table;
  
  -- 步骤3：删除临时表
  DROP TABLE #temp_table;
  

3. 忽略重复插入

• 场景：插入时自动跳过重复项。

  INSERT INTO table_name (column1, column2)
  SELECT 'value1', 'value2'
  WHERE NOT EXISTS (
      SELECT 1 FROM table_name 
      WHERE column1 = 'value1' AND column2 = 'value2'
  );
  

---

四、性能优化建议

1. 索引支持：为 PARTITION BY 或 GROUP BY 的列创建索引。
2. 分批处理：使用 TOP 或 OFFSET FETCH 分批次删除数据。
3. 统计信息更新：去重后执行 UPDATE STATISTICS table_name。
4. 避免全表扫描：尽量指定 WHERE 条件缩小处理范围。

---

五、常见问题解答

Q1：如何判断哪种方法最快？

• 查看执行计划（Ctrl + M），关注 Clustered Index Scan/Seek 和 Sort 操作的开销。

Q2：删除重复数据后表空间未释放？

• 执行 DBCC SHRINKFILE 或重建索引释放空间（谨慎操作）。

Q3：如何防止去重时阻塞业务？

• 使用 NOLOCK 提示（风险：脏读）或低峰期操作。

---

六、总结

方法	适用场景	优点	缺点
DISTINCT	简单查询去重	语法简单	无法处理复杂保留逻辑
ROW_NUMBER()	保留特定记录	灵活控制保留规则	需要理解窗口函数
GROUP BY	去重+聚合统计	可结合聚合函数	无法直接删除重复数据
唯一索引/约束	预防未来重复	强制数据唯一性	需提前清理历史重复数据

根据业务需求和数据规模选择最优方案，生产环境务必先备份！