SQL 查詢與刪除重複資料的完整技巧集錦

一、查詢重複資料

1. 找出所有重複列（單一欄位判斷）

SELECT * FROM table_name
WHERE dup_col IN (
    SELECT dup_col FROM table_name
    GROUP BY dup_col HAVING COUNT(*) > 1
);

2. 僅顯示每組重複資料的一筆（保留 MAX ID）

SELECT * FROM HZT
WHERE ID IN (
    SELECT MAX(ID) FROM HZT GROUP BY Title
);

3. 多欄位組合判斷重複

SELECT * FROM vitae a
WHERE (a.peopleId, a.seq) IN (
    SELECT peopleId, seq FROM vitae
    GROUP BY peopleId, seq HAVING COUNT(*) > 1
);

二、刪除重複資料（保留一筆）

1. 單欄位：保留 MAX(ID)

DELETE FROM HZT
WHERE ID NOT IN (
    SELECT MAX(ID) FROM HZT GROUP BY Title
);

2. 單欄位：保留 MIN(ROWID)

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

3. 多欄位：保留 MIN(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
);

三、特殊情況：完全重複列（所有欄位皆同）

-- 查詢去重結果
SELECT DISTINCT * FROM tableName;

-- 實體刪除重複並保留一筆（需建臨時表）
SELECT DISTINCT * INTO #Tmp FROM tableName;
DROP TABLE tableName;
SELECT * INTO tableName FROM #Tmp;
DROP TABLE #Tmp;

四、進階技巧：利用 ROW_NUMBER() 視窗函數（12c+ 推薦）

DELETE FROM table_name
WHERE ROWID IN (
    SELECT ROWID FROM (
        SELECT ROWID,
               ROW_NUMBER() OVER (PARTITION BY key_col ORDER BY ROWID) AS rn
        FROM table_name
    ) WHERE rn > 1
);

建議

• 正式環境執行刪除前，務必先備份或在交易中驗證影響筆數。
• 長期解決應於資料表加上 UNIQUE INDEX 或 PRIMARY KEY 防止髒資料產生。