場景一：單欄位判重，保留最小 ID

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

場景三：NOT IN + 子查詢（通用）

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

場景四：LEFT JOIN 刪除無對應行

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

MySQL 8.0+ 窗口函數通用寫法

WITH cte AS (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY name, value ORDER BY id) AS rn
    FROM yourtable
)
DELETE FROM yourtable WHERE id IN (SELECT id FROM cte WHERE rn > 1);

執行前必做

1. SELECT 先驗證將刪除的行數。
2. 備份目標表：CREATE TABLE bak AS SELECT * FROM target;
3. 大表分批刪除（LIMIT 1000 循環）避免長鎖、日誌暴增。