SQL 查詢與刪除重複資料完整指南：單欄位、多欄位、跨資料庫通用技巧

重複資料是資料品質常見痛點。本文彙整針對單一欄位、多欄位組合、不同資料庫（SQL Server/Oracle/MySQL/PostgreSQL）的查詢與刪除最佳實務。

一、查詢多欄位完全重複的記錄（自我連接法）

表 Persons(id, pname, cardid, address)，找出姓名+身分證+地址三欄皆同的記錄：

SELECT p1.*
FROM Persons p1
JOIN Persons p2
  ON p1.id <> p2.id
  AND p1.cardid = p2.cardid
  AND p1.pname = p2.pname
  AND p1.address = p2.address;

二、刪除重複資料三大經典法（以 Oracle ROWID 為例，概念通用）

1. ROWID / 物理識別碼法

-- 查詢保留最大 ROWID 的重複群組
SELECT * FROM Table1 a
WHERE rowid != (SELECT MAX(rowid) FROM Table1 b
                WHERE a.col1=b.col1 AND a.col2=b.col2 ...);

-- 刪除
DELETE FROM Table1 a
WHERE rowid != (SELECT MAX(rowid) FROM Table1 b
                WHERE a.col1=b.col1 AND a.col2=b.col2 ...);

適用：Oracle、PostgreSQL (ctid)、SQLite (rowid) 等有物理位置識別碼的資料庫。

2. GROUP BY + HAVING 法

-- 查詢重複群組與筆數
SELECT col1, col2, COUNT(*) FROM Table1
GROUP BY col1, col2 HAVING COUNT(*) > 1;

-- 刪除（注意：某些資料庫不支援 DELETE 直接加 GROUP BY）
-- 通用寫法：用子查詢或 CTE
DELETE FROM Table1
WHERE (col1, col2) IN (
    SELECT col1, col2 FROM Table1
    GROUP BY col1, col2 HAVING COUNT(*) > 1
)
AND pk NOT IN (
    SELECT MIN(pk) FROM Table1
    GROUP BY col1, col2 HAVING COUNT(*) > 1
);

關鍵：需有主鍵或唯一識別欄位決定保留哪一筆。

3. DISTINCT + 重建表法（小表適用）

CREATE TABLE Table_New AS SELECT DISTINCT * FROM Table1;
TRUNCATE TABLE Table1;
INSERT INTO Table1 SELECT * FROM Table_New;
DROP TABLE Table_New;

適用：資料量小、無外鍵參考、可接受短暫鎖表。

三、通用進階模式：保留每組第一筆（依主鍵/時間）

單欄位判斷重複

-- 查詢
SELECT * FROM People
WHERE PeopleId IN (SELECT PeopleId FROM People GROUP BY PeopleId HAVING COUNT(*) > 1);

-- 刪除（保留最小 ROWID/主鍵）
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);

多欄位判斷重複

-- 查詢
SELECT * FROM Vitae a
WHERE (a.PeopleId, a.Seq) IN (
    SELECT PeopleId, Seq FROM Vitae GROUP BY PeopleId, Seq HAVING COUNT(*) > 1
);

-- 刪除（保留最小 ROWID）
DELETE FROM Vitae a
WHERE (a.PeopleId, a.Seq) IN (
    SELECT PeopleId, Seq FROM Vitae GROUP BY PeopleId, Seq HAVING COUNT(*) > 1
)
AND a.rowid NOT IN (
    SELECT MIN(rowid) FROM Vitae GROUP BY PeopleId, Seq HAVING COUNT(*) > 1
);

四、SQL Server / PostgreSQL / MySQL 現代寫法：ROW_NUMBER() 窗格函數

WITH DupCTE AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY pk) AS rn
    FROM Table1
)
-- 查詢重複
SELECT * FROM DupCTE WHERE rn > 1;
-- 刪除重複
DELETE FROM Table1
WHERE pk IN (SELECT pk FROM DupCTE WHERE rn > 1);

優勢：標準 SQL、可攜性高、邏輯清晰、效能佳（單次掃描）。

五、快速找出有重複值的欄位組合

-- 單欄位
SELECT Name, COUNT(*) FROM A GROUP BY Name HAVING COUNT(*) > 1;
-- 多欄位
SELECT Name, Sex, COUNT(*) FROM A GROUP BY Name, Sex HAVING COUNT(*) > 1;

六、游標迴圈刪除（舊版 SQL Server 無窗格函數時的替代方案）

DECLARE @max INT, @id INT;
DECLARE cur CURSOR LOCAL FOR
    SELECT pk, COUNT(*) FROM Table1 GROUP BY pk HAVING COUNT(*) > 1;
OPEN cur;
FETCH NEXT FROM cur INTO @id, @max;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @max = @max - 1;
    SET ROWCOUNT @max;
    DELETE FROM Table1 WHERE pk = @id;
    FETCH NEXT FROM cur INTO @id, @max;
END
CLOSE cur; DEALLOCATE cur;
SET ROWCOUNT 0;

七、根治建議

1. 資料表**必須定義主鍵或唯一條件約束**，從源頭防止完全重複。
2. 業務鍵（如身分證+姓名）加上 UNIQUE INDEX。
3. ETL 階段加入去重邏輯（ROW_NUMBER 或 MERGE）。
4. 定期執行重複掃描腳本納入維護計畫。