-- 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;
-- 根因:缺乏主鍵/唯一索引,補建即可杜絕。
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, 新種子。