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