SQL Server 2005 利用 ROW_NUMBER() 單一查詢刪除重複資料

核心技巧

使用 ROW_NUMBER() OVER (PARTITION BY 重複鍵 ORDER BY 排序鍵) 為每組重複值編號，保留編號=1 的行，刪除編號>1 的行，單一陳述式完成去重。

完整示範腳本

-- 建立測試資料
IF EXISTS (SELECT * FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '#Temp%')
    DROP TABLE #temp;

CREATE TABLE #temp (
    [Id] INT,
    [Name] VARCHAR(50),
    [Age] INT,
    [Sex] BIT DEFAULT 1
);
GO

INSERT INTO #temp VALUES (1, 'James', 25, 1);
INSERT INTO #temp VALUES (1, 'James', 25, 1);
INSERT INTO #temp VALUES (1, 'James', 25, 1);
INSERT INTO #temp VALUES (2, 'Lisa', 24, 0);
INSERT INTO #temp VALUES (2, 'Lisa', 24, 0);
INSERT INTO #temp VALUES (2, 'Lisa', 24, 0);
INSERT INTO #temp VALUES (3, 'Mirsa', 23, 0);
INSERT INTO #temp VALUES (3, 'Mirsa', 23, 0);
INSERT INTO #temp VALUES (3, 'Mirsa', 23, 0);
INSERT INTO #temp VALUES (4, 'John', 26, 1);
INSERT INTO #temp VALUES (5, 'Abraham', 28, 1);
INSERT INTO #temp VALUES (6, 'Lincoln', 30, 1);

-- 單一查詢刪除重複 (保留每組第一筆)
DELETE T
FROM (
    SELECT ROW_NUMBER() OVER (PARTITION BY [Id], [Name], [Age], [Sex] ORDER BY [Id]) AS RowNumber, *
    FROM #Temp
) T
WHERE T.RowNumber > 1;

-- 驗證結果
SELECT * FROM #temp;

關鍵點解析

• PARTITION BY [Id], [Name], [Age], [Sex]：定義「重複」的業務鍵組合。
• ORDER BY [Id]：決定保留哪一筆 (此處保留最小 Id)；可改為 ORDER BY SomeDate DESC 保留最新。
• 衍生表 T 包含 RowNumber，外層 DELETE 針對 RowNumber > 1。
• 單一陳述式、無需游標/暫存表/迴圈，效能佳、鎖定時間短。

適用版本

SQL Server 2005 及以上 (支援 ROW_NUMBER() 窗口函數)。

延伸：保留最新一筆

DELETE T
FROM (
    SELECT ROW_NUMBER() OVER (PARTITION BY [Id], [Name] ORDER BY [CreateDate] DESC) AS rn, *
    FROM YourTable
) T
WHERE T.rn > 1;