刪除重複資料只保留單筆：從資料結構演算法到 SQL 多平台實作

核心邏輯：線性表去重演算法

經典順序表去重：建立新表 Lb，將 La 第一元素放入，後續元素逐一與 Lb 比對，不重複則追加。時間複雜度 O(n²)，資料庫中對應「建立暫存表 → DISTINCT → 覆寫」模式。

通用暫存表法 (適用所有資料庫)

SELECT DISTINCT * INTO #Tmp FROM SourceTable;
DROP TABLE SourceTable;
SELECT * INTO SourceTable FROM #Tmp;
DROP TABLE #Tmp;

⚠️ 會導致索引、約束、權限、觸發器遺失，需事後重建。根治：加上唯一索引/主鍵防止重複產生。

無顯式主鍵/識別欄時的刪除策略

SQL Server 2000：構造序號列

-- 相關子查詢法
SELECT 序號 = (SELECT COUNT(*) FROM Customer a WHERE a.CustId <= b.CustId), CustId, Company
FROM Customer b ORDER BY 1;

-- 自連接分組法
SELECT 序號 = COUNT(*), a.CustId, a.Company
FROM Customer a, Customer b
WHERE a.CustId >= b.CustId
GROUP BY a.CustId, a.Company ORDER BY 序號;

SQL Server 2005+：視窗函數 (標準、高效)

-- RANK() 允許並列
SELECT RANK() OVER (ORDER BY CustId DESC) AS Seq, CustId, Company FROM Customer;

-- ROW_NUMBER() 唯一序號，配合 CTE 刪除重複
WITH CTE AS (
    SELECT ROW_NUMBER() OVER (PARTITION BY CustId, Company ORDER BY CustId) AS RN
    FROM Customer
)
DELETE FROM CTE WHERE RN > 1;

Oracle：ROWID 偽欄位

每列唯一 `ROWID` 編碼位置。刪除重複保留最大 ROWID：

DELETE FROM Test a 
WHERE a.ROWID != (SELECT MAX(ROWID) FROM Test b WHERE a.Id = b.Id);

游標逐列刪除法 (教學示範，生產慎用)

DECLARE @Max INT, @Id INT;
DECLARE Cur CURSOR LOCAL FOR 
    SELECT Id, COUNT(*) FROM Test GROUP BY Id HAVING COUNT(*) > 1;
OPEN Cur;
FETCH NEXT FROM Cur INTO @Id, @Max;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @Max = @Max - 1;          -- 保留 1 筆
    SET ROWCOUNT @Max;            -- 限制影響列數
    DELETE FROM Test WHERE Id = @Id;
    FETCH NEXT FROM Cur INTO @Id, @Max;
END
CLOSE Cur; DEALLOCATE Cur;
SET ROWCOUNT 0;

現代最佳實踐：MERGE / EXCEPT / 視窗函數

SQL Server 2008+ MERGE 語法

WITH Ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Col1, Col2 ORDER BY (SELECT 0)) AS RN
    FROM SourceTable
)
DELETE FROM SourceTable
WHERE EXISTS (SELECT 1 FROM Ranked r WHERE r.Col1 = SourceTable.Col1 AND r.Col2 = SourceTable.Col2 AND r.RN > 1);

PostgreSQL / SQLite: `DELETE ... WHERE ctid NOT IN (SELECT MIN(ctid) ...)`

總結與選型指南

去重是治標，**唯一約束**才是治本。資料庫設計階段即應識別業務鍵並建立約束，避免事後清理成本。