針對無主鍵或主鍵非業務鍵的表,需依業務鍵去重並保留最早插入的列 (最小主鍵值)。本文提供標準 T-SQL 寫法與執行結果驗證。
IF OBJECT_ID('t0807') IS NOT NULL DROP TABLE t0807;
GO
CREATE TABLE t0807 (
AutoID INT IDENTITY(1,1) PRIMARY KEY,
[id] INT,
username VARCHAR(10)
);
GO
INSERT INTO t0807 ([id], username) VALUES
(1,'1'), (4,'1'), (4,'2'), (6,'2'), (2,'3'), (7,'3');
GO
-- 初始資料
-- AutoID: 1(1,1), 2(4,1), 3(4,2), 4(6,2), 5(2,3), 6(7,3)
DELETE FROM t0807
WHERE AutoID NOT IN (
SELECT AutoID FROM t0807 a
WHERE [id] <= (
SELECT MIN([id]) FROM t0807 b WHERE a.username = b.username
)
);
GO
SELECT * FROM t0807;
-- AutoID id username
-- 1 1 1
-- 3 4 2
-- 5 2 3
(影響 3 列,保留 3 列)
username 群組,找出最小的 [id] 值。[id] <= 該組最小 id 的列的 AutoID(即每組最早插入的列)。DELETE:刪除 AutoID 不在 上述集合中的列。WITH cte AS (
SELECT AutoID,
ROW_NUMBER() OVER (PARTITION BY username ORDER BY AutoID) AS rn
FROM t0807
)
DELETE FROM t0807
WHERE AutoID IN (SELECT AutoID FROM cte WHERE rn > 1);
WITH keep AS (
SELECT MIN(AutoID) AS keep_id FROM t0807 GROUP BY username
)
DELETE t FROM t0807 t
LEFT JOIN keep k ON t.AutoID = k.keep_id
WHERE k.keep_id IS NULL;
DECLARE @batch INT = 1000, @deleted INT = 1;
WHILE @deleted > 0
BEGIN
DELETE TOP (@batch) FROM t0807
WHERE AutoID NOT IN (
SELECT AutoID FROM t0807 a
WHERE [id] <= (SELECT MIN([id]) FROM t0807 b WHERE a.username = b.username)
);
SET @deleted = @@ROWCOUNT;
-- 可選 WAITFOR DELAY '00:00:01' 降低衝擊
END
username) + 排序欄位 (AutoID) 建立複合索引:CREATE INDEX IX_t0807_user_id ON t0807(username, AutoID);[id] 也參與判斷,考慮 (username, [id], AutoID)。