SQL 刪除重複資料只保留一筆：單欄位/多欄位/ROWID 完整方案

重複資料清理是資料治理常態作業。本文提供針對**單欄位**、**多欄位**、**含 ROWID/主鍵**等不同場景的標準刪除腳本，並說明各資料庫（Oracle、SQL Server、MySQL、PostgreSQL）的語法差異。

場景一：單欄位判斷重複，保留最小主鍵

-- 通用標準寫法（支援大多數資料庫）
DELETE FROM people
WHERE peopleId IN (
    SELECT peopleId FROM people
    GROUP BY peopleId HAVING COUNT(*) > 1
)
AND peopleId NOT IN (
    SELECT MIN(peopleId) FROM people
    GROUP BY peopleId HAVING COUNT(*) > 1
);

• 子查詢 1：找出重複的 peopleId。
• 子查詢 2：每組重複中保留最小 peopleId（主鍵）。
• 外層刪除：刪除重複組中**非最小主鍵**的列。

場景二：多欄位組合判斷重複（如 peopleId + seq）

DELETE FROM vitae a
WHERE (a.peopleId, a.seq) IN (
    SELECT peopleId, seq FROM vitae
    GROUP BY peopleId, seq HAVING COUNT(*) > 1
)
AND a.rowid NOT IN (
    SELECT MIN(rowid) FROM vitae
    GROUP BY peopleId, seq HAVING COUNT(*) > 1
);

• 使用 **元組比較** (col1, col2) IN (...)（Oracle、PostgreSQL、MySQL 8.0+ 支援）。
• rowid 為 Oracle 物理位址偽欄位；SQL Server 用 %%physloc%% 或主鍵；MySQL 用主鍵。

場景三：查詢重複資料（不刪除，供審核）

-- 單欄位
SELECT * FROM people
WHERE peopleId IN (SELECT peopleId FROM people GROUP BY peopleId HAVING COUNT(*) > 1);

-- 多欄位
SELECT * FROM vitae a
WHERE (a.peopleId, a.seq) IN (
    SELECT peopleId, seq FROM vitae GROUP BY peopleId, seq HAVING COUNT(*) > 1
);

資料庫特定語法對照表

SQL Server 專用寫法（不支援元組 IN）

-- 使用 CTE + ROW_NUMBER() 通用高效寫法
WITH dup AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY peopleId, seq ORDER BY id) AS rn
    FROM vitae
)
DELETE FROM dup WHERE rn > 1;

• PARTITION BY 定義重複群組。
• ORDER BY 決定保留哪一列（如最小 id）。
• rn > 1 刪除群組內其餘列。
• **效能最佳**，單次掃描，無相關子查詢開銷。

MySQL 專用寫法（8.0+ 支援 CTE/窗口函數）

WITH dup AS (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY peopleId ORDER BY id) AS rn
    FROM people
)
DELETE p FROM people p JOIN dup d ON p.id = d.id WHERE d.rn > 1;

Oracle 專用：ROWID 直接刪除

DELETE FROM vitae
WHERE ROWID NOT IN (
    SELECT MIN(ROWID) FROM vitae
    GROUP BY peopleId, seq HAVING COUNT(*) > 1
)
AND (peopleId, seq) IN (
    SELECT peopleId, seq FROM vitae GROUP BY peopleId, seq HAVING COUNT(*) > 1
);

大表刪除策略（避免長交易、鎖升級、日誌爆漲）

1. 分批刪除：每次 DELETE TOP (10000) ... (SQL Server) 或 LIMIT 10000 (MySQL)，循環直到 @@ROWCOUNT = 0。
2. 建新表換名：CREATE TABLE new AS SELECT DISTINCT ... FROM old → 重建索引/約束 → RENAME，速度最快、日誌最少。
3. 分區交換：若表已分區，可 ALTER TABLE ... EXCHANGE PARTITION 快速清理。

預防重複的長效機制

• **唯一約束/索引**：ALTER TABLE ADD UNIQUE (col1, col2)，源頭阻擋。
• **ETL 去重**：資料入庫前用 ROW_NUMBER() 或 DISTINCT 清洗。
• **冪等寫入**：應用層使用 INSERT ... ON DUPLICATE KEY UPDATE / MERGE。

選擇適合資料庫版本與資料量的方案，配合唯一約束預防，建立「清理-預防」閉環治理體系。