萬盛學電腦網

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

SQL 刪除重複資料保留最小主鍵:NOT IN 子查詢實戰範例

T-SQL 刪除重複列保留首筆:子查詢 + 分組最小值模式

針對無主鍵或主鍵非業務鍵的表,需依業務鍵去重並保留最早插入的列 (最小主鍵值)。本文提供標準 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)

刪除重複語句:保留每組 username 最小 AutoID

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 列)

邏輯解析

  1. 內層子查詢:對每個 username 群組,找出最小的 [id] 值。
  2. 中層查詢:找出滿足 [id] <= 該組最小 id 的列的 AutoID(即每組最早插入的列)。
  3. 外層 DELETE:刪除 AutoID 不在 上述集合中的列。

現代寫法對比 (SQL Server 2005+ / PostgreSQL / MySQL 8.0+)

ROW_NUMBER() 窗口函數(更直觀、效能通常更好)

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);

CTE + JOIN 刪除

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)。
copyright © 萬盛學電腦網 all rights reserved