DELETE FROM tablename
WHERE id NOT IN (
SELECT MAX(id) FROM tablename GROUP BY col1, col2, ...
);
原理:依業務欄位分組,保留每組最大 ID,刪除其餘。
SELECT DISTINCT * INTO temp FROM tablename;
DELETE FROM tablename;
INSERT INTO tablename SELECT * FROM temp;
DROP TABLE temp;
原理:將去重後資料存入臨時表,清空原表再寫回。
ROW_NUMBER() 視窗函數單語句完成,效能最佳。WITH cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY id) AS rn
FROM tablename
)
DELETE FROM tablename WHERE id IN (SELECT id FROM cte WHERE rn > 1);