萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL 快速查找表中重複數據:GROUP BY + HAVING 經典模式

場景

建立主鍵/唯一索引失敗、數據清洗、業務去重時,需快速定位重複記錄及重複次數。

核心 SQL

SELECT column1, column2, COUNT(*) AS DuplicateCount
FROM TableName
GROUP BY column1, column2
HAVING COUNT(*) > 1
ORDER BY DuplicateCount DESC

字段說明

  • column1, column2:構成業務主鍵的列組合(可單列、多列)。
  • COUNT(*):該組合出現的次數。
  • HAVING COUNT(*) > 1:過濾出重複組合。
  • ORDER BY DuplicateCount DESC:重複最嚴重的排在前面,優先處理。

進階:獲取完整重複行(含所有列)

WITH DupKeys AS (
    SELECT column1, column2
    FROM TableName
    GROUP BY column1, column2
    HAVING COUNT(*) > 1
)
SELECT t.*
FROM TableName t
JOIN DupKeys d ON t.column1 = d.column1 AND t.column2 = d.column2
ORDER BY t.column1, t.column2

刪除重複保留一條(常用模式)

;WITH CTE AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY column1, column2 ORDER BY (SELECT 0)) AS RN
    FROM TableName
)
DELETE FROM CTE WHERE RN > 1

ORDER BY (SELECT 0) 表示任意保留一行;若需保留最早/最新,換成具體列如 ORDER BY CreateTime。

copyright © 萬盛學電腦網 all rights reserved