資料表裡出現重複記錄，通常有幾個來源：ETL 流程沒做冪等性保護、應用層缺少唯一約束、或手動匯入時重複執行。本文介紹幾種查法與對應的效能取捨。

目錄

  1. 什麼是重複資料？
  2. 基礎查詢方法
  3. 進階查詢技巧
  4. 實務案例分析
  5. 效能優化建議

1. 什麼是重複資料？

重複資料指的是在資料表中，某些欄位或欄位組合的值完全相同的記錄。例如，在員工資料表中，可能會出現相同姓名、生日和部門的多筆記錄。

2. 基礎查詢方法

2.1 使用 GROUP BY 和 HAVING

最基本的查詢重複資料方式是使用 GROUP BY 和 HAVING 子句：

1
2
3
4
SELECT 欄位1, 欄位2, COUNT(*) as 重複次數
FROM 資料表
GROUP BY 欄位1, 欄位2
HAVING COUNT(*) > 1;

2.2 查看重複記錄的完整資訊

若要查看重複記錄的所有欄位資訊，可以使用以下方式：

1
2
3
4
5
6
7
8
9
10
11
WITH 重複資料 AS (
    SELECT 欄位1, 欄位2
    FROM 資料表
    GROUP BY 欄位1, 欄位2
    HAVING COUNT(*) > 1
)
SELECT A.*
FROM 資料表 A
INNER JOIN 重複資料 B
    ON A.欄位1 = B.欄位1
    AND A.欄位2 = B.欄位2;

3. 進階查詢技巧

3.1 使用 ROW_NUMBER() 函數

ROW_NUMBER() 在每個重複群組內編號，再依號碼篩選。下例 WHERE row_num > 1 取出每組第二筆以後的記錄（即「多出來的那些」），若需要列出整組重複列（含第一筆），請改用 CTE 回頭 JOIN 原表，或改用 2.2 節的做法：

1
2
3
4
5
6
7
8
9
WITH 編號資料 AS (
    SELECT *,
           ROW_NUMBER() OVER(PARTITION BY 欄位1, 欄位2 
                            ORDER BY 欄位3) as row_num
    FROM 資料表
)
SELECT *
FROM 編號資料
WHERE row_num > 1;

3.2 使用 EXISTS 子查詢

這種方法的語意直觀，適合中小型資料量或已在比對欄位上建立索引的情境。注意：這是 correlated subquery，外層每一列都會觸發一次內層掃描，在資料量大且缺乏索引時複雜度接近 O(n²)，效能不如視窗函數 ROW_NUMBER()：

1
2
3
4
5
6
7
8
9
SELECT *
FROM 資料表 A
WHERE EXISTS (
    SELECT 1
    FROM 資料表 B
    WHERE A.欄位1 = B.欄位1
    AND A.欄位2 = B.欄位2
    AND A.主鍵 <> B.主鍵
);

4. 實務案例分析

假設設備檢查資料表以「設備名稱 + 位置代碼」為業務唯一鍵，但沒有建 UNIQUE constraint，導致重複寫入。查法如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 先確認哪些業務鍵重複及筆數
WITH 重複設備 AS (
    SELECT 
        設備名稱,
        位置代碼
    FROM 設備檢查表
    GROUP BY 
        設備名稱,
        位置代碼
    HAVING COUNT(*) > 1
)
-- 再拉回所有重複列的完整資料（含設備ID，方便後續刪除）
SELECT 
    檢查表.* 
FROM 設備檢查表 檢查表
INNER JOIN 重複設備
    ON 檢查表.設備名稱 = 重複設備.設備名稱
    AND 檢查表.位置代碼 = 重複設備.位置代碼
ORDER BY 檢查表.設備名稱, 檢查表.位置代碼, 檢查表.設備ID;

重複判斷鍵應使用業務邏輯上「應唯一」的欄位組合（如姓名+生日、email、序號等），而非代理主鍵（如自增 ID）。代理主鍵每列都不同，放進 GROUP BY 永遠查不到重複。

5. 效能優化建議

  1. 善用索引：

    • 在常用於群組或排序的欄位上建立適當的索引
    • 複合索引的欄位順序要配合查詢條件

  2. 依資料量選查法：

    • 小資料量或比對欄位有索引：GROUP BY / EXISTS 都可
    • 大資料量無索引：優先用 ROW_NUMBER()（單次掃描）；EXISTS correlated subquery 複雜度接近 O(n²)，效能顯著較差

  3. 避免全表掃描：

    • 盡可能加入 WHERE 條件縮小查詢範圍
    • 使用覆蓋索引減少資料讀取

總結

幾個落地要點：

  • 定期對關鍵資料表跑 GROUP BY + HAVING 做健康檢查
  • 在業務唯一鍵上加 UNIQUE constraint 或 UNIQUE INDEX，從源頭防止重複寫入
  • 大表查重前先在比對欄位建索引，或縮小 WHERE 範圍，避免全表掃描
  • 確認查到重複後，搭配 ROW_NUMBER() + CTE 做 DELETE，保留每組最小（或最大）ID 那筆