資料庫去重是經典需求。本文系統整理**四大類去重場景**與對應 SQL 寫法，涵蓋單欄/多欄、完全重複/關鍵欄位重複、保留首筆/末筆、Oracle Rowid 特有寫法。

場景一：完全重複行（所有欄位相同）

保留一筆（任意）：

SELECT DISTINCT * INTO #Tmp FROM TableName;
TRUNCATE TABLE TableName;
INSERT INTO TableName SELECT * FROM #Tmp;
DROP TABLE #Tmp;

或直接建唯一索引強制去重（會報錯需先清理）。

場景二：關鍵欄位重複（如 name+value 相同），保留 id 最大/最小

方法 1：NOT IN 子查詢

DELETE FROM YourTable
WHERE id NOT IN (
    SELECT MAX(id) FROM YourTable GROUP BY name, value
);

方法 2：LEFT JOIN 找出非保留行

DELETE a
FROM 表 a
LEFT JOIN (
    SELECT MIN(id) AS id FROM 表 GROUP BY name, value
) b ON a.id = b.id
WHERE b.id IS NULL;

場景三：單欄位判斷重複（如 peopleId），保留 rowid 最小

-- 查重
SELECT * FROM people
WHERE peopleId IN (
    SELECT peopleId FROM people GROUP BY peopleId HAVING COUNT(*) > 1
);

-- 刪重（保留最小 rowid）
DELETE FROM people
WHERE peopleId IN (
    SELECT peopleId FROM people GROUP BY peopleId HAVING COUNT(*) > 1
)
AND rowid NOT IN (
    SELECT MIN(rowid) FROM people GROUP BY peopleId HAVING COUNT(*) > 1
);

場景四：多欄位組合判斷重複（peopleId, seq）

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

-- 刪重（保留最小 rowid）
DELETE FROM vitae a
WHERE (a.peopleId, a.seq) IN (
    SELECT peopleId, seq FROM vitae GROUP BY peopleId, seq HAVING COUNT(*) > 1
)
AND rowid NOT IN (
    SELECT MIN(rowid) FROM vitae GROUP BY peopleId, seq HAVING COUNT(*) > 1
);

場景五：游標逐組刪除（適用舊版本/特殊邏輯）

DECLARE @max INT, @id INT;
DECLARE cur CURSOR LOCAL FOR
    SELECT 主鍵, COUNT(*) FROM 表名 GROUP BY 主鍵 HAVING COUNT(*) > 1;
OPEN cur;
FETCH NEXT FROM cur INTO @id, @max;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @max = @max - 1;
    SET ROWCOUNT @max;
    DELETE FROM 表名 WHERE 主鍵 = @id;
    FETCH NEXT FROM cur INTO @id, @max;
END
CLOSE cur; DEALLOCATE cur;
SET ROWCOUNT 0;

Oracle 專用：利用 Rowid 刪重（效率最高）

-- 保留最大 rowid
DELETE FROM employee e2
WHERE rowid NOT IN (
    SELECT MAX(e1.rowid) FROM employee e1
    WHERE e1.emp_id = e2.emp_id
      AND e1.emp_name = e2.emp_name
      AND e1.salary = e2.salary
);

-- 單條 SQL 最高效
DELETE FROM employee
WHERE rowid NOT IN (
    SELECT MAX(rowid) FROM employee
    GROUP BY emp_id, emp_name, salary
);

選型建議

場景	推薦寫法	效能
完全重複、小表	DISTINCT + 臨時表	中
關鍵欄位重複、大表	LEFT JOIN / NOT IN	高（有索引）
需保留首/末筆、有 rowid	Rowid 子查詢	最高
複雜邏輯、逐組處理	游標	低

**核心原則**：先建唯一索引/主鍵預防重複；去重前必備份；大表分批次刪除避免鎖表。