SQL 查詢重複值記錄的兩種寫法與效能大比拼

資料庫去重實戰：如何高效找出重複記錄

在數據清洗、主鍵建立、業務合規檢查等場景中，「查詢某欄位重複值的記錄」是高頻需求。本文以學生表（student）為例，對比兩種經典寫法的執行計畫與實測效能，並給出生產環境推薦方案。

場景設定

• 表名：student
• 欄位：Sno（學號，擬設為主鍵）、ID（身份證，唯一業務鍵）
• 數據量：10,000+ 行，Sno 存在重複
• 目標：找出所有 Sno 重複的完整記錄

方法一：自連接 + 不等值條件（推薦，秒級完成）

SELECT *
FROM student AS A
WHERE Sno IN (
    SELECT Sno 
    FROM student AS B 
    WHERE A.ID <> B.ID
);

原理解析

• 外層遍歷表 A，內層子查詢在表 B 中尋找「同學號但不同身份證」的記錄。
• A.ID <> B.ID 確保不匹配自身，等價於「存在至少另一筆相同 Sno」。
• 優化器易轉換為 Hash Match / Merge Join，配合 Sno 索引可達線性掃描複雜度。

方法二：相關子查詢 + COUNT(*)（不推薦，分鐘級甚至卡死）

SELECT *
FROM student AS A
WHERE (SELECT COUNT(*) FROM student WHERE Sno = A.Sno) > 1;

效能陷阱

• 對外層每一行（10,000+ 次）都執行一次全表掃描/索引查找計算 COUNT(*)。
• 屬於典型的 RBAR (Row-By-Agonizing-Row) 模式，複雜度接近 O(N²)。
• 大表時會導致 CPU 飆升、鎖升級、甚至實例假死。

實測對比（10,000 行測試數據）

更優寫法：窗口函數（SQL Server 2005+ / PostgreSQL / Oracle / MySQL 8.0+）

WITH DupCTE AS (
    SELECT 
        *,
        COUNT(*) OVER (PARTITION BY Sno) AS Cnt
    FROM student
)
SELECT * FROM DupCTE WHERE Cnt > 1;

• 單次掃描完成分組計數，無需自連接或子查詢。
• 執行計畫通常為 Segment + Sequence Project + Filter，效能與方法一持平甚至更優。
• 代碼可讀性最強，易維護。

進階場景：保留一條刪除其餘重複

-- 保留每組 Sno 的最小 ID，刪除其餘
WITH Ranked AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY Sno ORDER BY ID) AS RN
    FROM student
)
DELETE FROM student
WHERE ID IN (SELECT ID FROM Ranked WHERE RN > 1);

索引建議

-- 針對去重查詢建立覆蓋索引
CREATE INDEX IX_student_Sno_ID ON student (Sno) INCLUDE (ID, 其他常用列);

• 方法一、窗口函數皆可直接利用此索引進行 ORDER BY Sno 流式聚合。
• 避免在 WHERE 中對 Sno 做函數運算（如 LTRIM(RTRIM(Sno))），導致索引失效。

總結速查

下次面對「找重複」需求，請優先選擇 窗口函數 或 IN + 自連接，並配合適當索引，讓百萬級數據也能秒級出結果。