表 YourTable(id PK, name, value)，保留 name+value 唯一組合的最小/最大 id，刪除其餘重複行。
| 方案 | 核心語法 | 適用場景 | 效能備註 |
|---|---|---|---|
| 1. NOT IN 子查詢 | DELETE FROM t WHERE id NOT IN (SELECT MAX(id) FROM t GROUP BY name, value) | 小表、簡單去重 | NOT IN 遇 NULL 失效，大表慢 |
| 2. LEFT JOIN 保留最小 id | DELETE a FROM t a LEFT JOIN (SELECT MIN(id) id FROM t GROUP BY name,value) b ON a.id=b.id WHERE b.id IS NULL | 中小表 | JOIN 比子查詢穩定 |
| 3. ROW_NUMBER() 窗口函數 (SQL 2005+) | WITH cte AS (SELECT *, ROW_NUMBER() OVER(PARTITION BY name,value ORDER BY id) rn FROM t) DELETE FROM cte WHERE rn > 1 | 大表、標準寫法 | 單掃描、可並行、推薦首選 |
| 4. 臨時表重建 | SELECT DISTINCT * INTO #tmp FROM t; TRUNCATE TABLE t; INSERT INTO t SELECT * FROM #tmp | 全表重複、無 PK | 鎖表久、需重建索引/權限 |
| 5. 游標循環刪除 | DECLARE cur CURSOR FOR SELECT key, cnt FROM t GROUP BY key HAVING cnt>1 ... WHILE @@FETCH_STATUS=0 DELETE TOP(cnt-1) FROM t WHERE key=@key | 極舊版本無窗口函數 | 效能極差、不推薦 |
| 6. IDENTITY 臨時表法 | SELECT IDENTITY(int,1,1) autoID,* INTO #tmp FROM t; SELECT MIN(autoID) INTO #tmp2 FROM #tmp GROUP BY name,value; SELECT * FROM #tmp WHERE autoID IN (SELECT autoID FROM #tmp2) | 保留第一行、需顯示結果 | 兩次掃描、中等效能 |
| 7. Oracle ROWID 法 (異構參考) | DELETE FROM t WHERE rowid NOT IN (SELECT MAX(rowid) FROM t GROUP BY cols) | Oracle 專用 | 物理地址定位、極快 |
| 8. MERGE 語句 (SQL 2008+) | MERGE t USING (SELECT MIN(id) id, name, value FROM t GROUP BY name,value) s ON t.id=s.id WHEN NOT MATCHED BY SOURCE THEN DELETE | 同步/去重一體化 | 單語句、可輸出動作日誌 |
UNIQUE INDEX (name, value) 從源頭阻擋
