查詢重複行

SELECT * 
FROM table1 a 
WHERE rowid != (
    SELECT MAX(rowid) 
    FROM table1 b 
    WHERE a.name1 = b.name1 
      AND a.name2 = b.name2  -- 所有業務主鍵列
);

刪除重複行

DELETE FROM table1 a 
WHERE rowid != (
    SELECT MAX(rowid) 
    FROM table1 b 
    WHERE a.name1 = b.name1 
      AND a.name2 = b.name2
);

✅ 優點：精確保留一行，無需臨時表。
❌ 缺點：Oracle 專有，跨庫遷移需改寫；子查詢大表時效能一般。

方法二：GROUP BY + HAVING 法（標準 SQL）

查詢重複統計

SELECT COUNT(*), MAX(name) 
FROM student 
GROUP BY num 
HAVING COUNT(*) > 1;

直接刪除（⚠️ 危險：會刪除該組所有行）

DELETE FROM student 
WHERE num IN (
    SELECT num FROM student 
    GROUP BY num 
    HAVING COUNT(*) > 1
);

⚠️ 警告：上述語句會把重複組的所有行全部刪除，而非保留一行。生產環境嚴禁直接執行。

安全刪除保留一行（標準寫法）

DELETE FROM student 
WHERE rowid NOT IN (
    SELECT MIN(rowid)  -- 或 MAX(rowid)，視保留策略
    FROM student 
    GROUP BY num
);

MySQL/PostgreSQL 替換 rowid 為主鍵 id：

DELETE t1 FROM student t1
JOIN (
    SELECT MIN(id) AS keep_id FROM student GROUP BY num
) t2 ON t1.num = t2.keep_id
WHERE t1.id != t2.keep_id;

方法三：DISTINCT + 臨時表法（小表適用）

-- 1. 建去重新表
CREATE TABLE table_new AS 
SELECT DISTINCT * FROM table1;

-- 2. 清空原表
TRUNCATE TABLE table1;

-- 3. 寫回去重數據
INSERT INTO table1 SELECT * FROM table_new;

-- 4. 刪除臨時表
DROP TABLE table_new;

✅ 優點：語法簡單，跨資料庫通用。
❌ 缺點：大表鎖表時間長、需雙倍空間、丟失自增 ID 連續性、外鍵關聯斷裂風險。

四、大表高效去重最佳實踐（MySQL 8.0+ / PostgreSQL）

WITH ranked AS (
    SELECT *, 
           ROW_NUMBER() OVER (PARTITION BY biz_key ORDER BY create_time DESC) AS rn
    FROM big_table
)
DELETE FROM big_table 
WHERE id IN (SELECT id FROM ranked WHERE rn > 1);

• 窗口函數單掃描完成標記，無臨時表開銷。
• 可按業務邏輯決定保留最新/最舊/最大 ID 行。
• 配合 LIMIT 10000 分批刪除，避免長事務與鎖升級。

五、預防重複的根治方案

1. 業務主鍵加唯一索引：ALTER TABLE t ADD UNIQUE uk_biz (biz_col1, biz_col2);
2. INSERT IGNORE / ON DUPLICATE KEY UPDATE：應用層寫入時自動去重。
3. ETL 階段去重：數據入庫前在 Spark/Flink/暫存表完成去重。