萬盛學電腦網

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

SQL 查重刪重全攻略:單列/多列、保留首/尾、臨時表/窗口函數多種模式

查找重複記錄

-- 1. 顯示所有重複行(含首行)
SELECT * FROM T_Info A
WHERE (SELECT COUNT(*) FROM T_Info WHERE Title = A.Title) > 1
ORDER BY Title DESC;

-- 2. 僅顯示重複組的每組一行(保留 MAX ID)
SELECT * FROM HZT WHERE ID IN (SELECT MAX(ID) FROM HZT GROUP BY Title);

刪除重複:核心模式

場景保留策略標準寫法
單列 (PeopleID) 保留最小 RowID DELETE FROM People WHERE PeopleID IN (SELECT PeopleID FROM People GROUP BY PeopleID HAVING COUNT(*)>1) AND RowID NOT IN (SELECT MIN(RowID) FROM People GROUP BY PeopleID HAVING COUNT(*)>1);
保留最大 ID DELETE FROM HZT WHERE ID NOT IN (SELECT MAX(ID) FROM HZT GROUP BY Title);
多列 (PeopleID, Seq) 保留最小 RowID DELETE FROM Vitae A WHERE (A.PeopleID,A.Seq) IN (SELECT PeopleID,Seq FROM Vitae GROUP BY PeopleID,Seq HAVING COUNT(*)>1) AND RowID NOT IN (SELECT MIN(RowID) FROM Vitae GROUP BY PeopleID,Seq HAVING COUNT(*)>1);
顯示重複不含保留行 SELECT * FROM Vitae A WHERE (A.PeopleID,A.Seq) IN (...) AND RowID NOT IN (SELECT MIN(RowID) ...);

全量去重(完全重複行)

SELECT DISTINCT * INTO #Tmp FROM TableName;
DROP TABLE TableName;
SELECT * INTO TableName FROM #Tmp;
DROP TABLE #Tmp;
-- 根因:缺乏主鍵/唯一索引,補建即可杜絕。

現代寫法:ROW_NUMBER() 分區去重(SQL 2005+)

WITH CTE AS (
    SELECT *, RN = ROW_NUMBER() OVER (PARTITION BY KeyCol1, KeyCol2 ORDER BY SortCol)
    FROM TargetTable
)
DELETE FROM CTE WHERE RN > 1;  -- 保留每組第一行(按 SortCol 排序)

優勢:單語句、可指定保留邏輯(最早/最新/最大 ID)、無需臨時表、計劃穩定。

補充:身份列重置

去重後若需標識列連續,可 DBCC CHECKIDENT('Table', RESEED, 0)(表為空)或 RESEED, 新種子。

copyright © 萬盛學電腦網 all rights reserved