SQL 查詢與刪除重複數據完全手冊：單欄位、多欄位、保留最小/最大 ROWID 六大場景

1. 查詢單欄位重複值

SELECT * FROM table
WHERE col1 IN (
    SELECT col1 FROM table GROUP BY col1 HAVING COUNT(col1) > 1
);

2. 刪除單欄位重複，保留最小 ROWID

DELETE FROM table
WHERE col1 IN (
    SELECT col1 FROM table GROUP BY col1 HAVING COUNT(col1) > 1
)
AND ROWID NOT IN (
    SELECT MIN(ROWID) FROM table GROUP BY col1 HAVING COUNT(col1) > 1
);

3. 查詢多欄位重複組合

SELECT * FROM table a
WHERE (a.col1, a.col2) IN (
    SELECT col1, col2 FROM table GROUP BY col1, col2 HAVING COUNT(*) > 1
);

4. 刪除多欄位重複，保留最小 ROWID

DELETE FROM table a
WHERE (a.col1, a.col2) IN (
    SELECT col1, col2 FROM table GROUP BY col1, col2 HAVING COUNT(*) > 1
)
AND ROWID NOT IN (
    SELECT MIN(ROWID) FROM table GROUP BY col1, col2 HAVING COUNT(*) > 1
);

5. 通用刪除重複（保留最小 ID）

-- 單欄位
DELETE FROM table WHERE id NOT IN (SELECT MIN(id) FROM table GROUP BY name);
-- 多欄位
DELETE FROM table WHERE id NOT IN (SELECT MIN(id) FROM table GROUP BY col1, col2);

6. 刪除重複（保留最大 ID）

DELETE FROM table
WHERE id IN (SELECT MAX(id) FROM table GROUP BY name HAVING COUNT(*) > 1);

7. 查詢多欄位重複（不含最小 ROWID）

SELECT * FROM table a
WHERE (a.col1, a.col2) IN (
    SELECT col1, col2 FROM table GROUP BY col1, col2 HAVING COUNT(*) > 1
)
AND ROWID NOT IN (
    SELECT MIN(ROWID) FROM table GROUP BY col1, col2 HAVING COUNT(*) > 1
);

8. 統計單欄位/組合欄位重複次數

SELECT Name, sex, COUNT(*) FROM A GROUP BY Name, sex HAVING COUNT(*) > 1;

關鍵提示

• ROWID 為 Oracle/某些資料庫偽列；MySQL 請改用主鍵 id 或唯一鍵。
• 大表刪除建議分批提交，避免長事務鎖表。
• 執行前務必備份或先用 SELECT 驗證結果集。