MySQL 刪除重複資料 SQL 寫法大全 (保留唯一/保留最小 ID 等)

場景範例

id | name | value
1  | a    | pp
2  | a    | pp   <-- 重複
3  | b    | iii
4  | b    | pp
5  | b    | pp   <-- 重複
6  | c    | pp
7  | c    | pp   <-- 重複
8  | c    | iii

目標：保留每組 (name, value) 的第一筆（最小 id）。

方法一：NOT IN 子查詢 (保留最大 ID)

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

方法二：LEFT JOIN 找出孤兒行

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

常用查重/刪重模板

單一欄位判重

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

多欄位判重

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

Oracle 環境特有：使用 ROWID

-- 透過 ROWID 刪除完全重複列 (保留最大 ROWID)
DELETE FROM employee e2
WHERE ROWID NOT IN (
    SELECT MAX(ROWID) FROM employee e1
    WHERE e1.emp_id = e2.emp_id
      AND e1.emp_name = e2.emp_name
      AND e1.salary = e2.salary
);

-- 效率更高：單表分組
DELETE FROM employee
WHERE ROWID NOT IN (
    SELECT MAX(ROWID) FROM employee
    GROUP BY emp_id, emp_name, salary
);

臨時表法 (通用)

CREATE TABLE tmp AS SELECT DISTINCT * FROM employee;
TRUNCATE TABLE employee;
INSERT INTO employee SELECT * FROM tmp;
DROP TABLE tmp;

根據資料量、資料庫版本、是否有主鍵/ROWID 選擇適合的方式。