尋找重複欄位值的資料列：四種 T-SQL 寫法效能比較

給定資料表 match_cols，需找出 col2 與 col3 組合重複的所有資料列。本文展示四種寫法並分析效能差異。

範例資料

row_no  col2  col3
------  ----  ----
1       c     d
2       a     a
3       a     d
4       c     b
5       c     c
6       b     c
7       c     a
8       c     b
9       c     d
10      d     d

方法一：自我聯結（Self-Join）

SELECT DISTINCT A.row_no, A.col2, A.col3
FROM match_cols A, match_cols B
WHERE A.col2 = B.col2 AND A.col3 = B.col3
  AND A.row_no <> B.row_no
ORDER BY A.col2, A.col3;

⚠ 大量資料時效能不佳，需掃描 N² 列。

方法二：相關子查詢 + EXISTS

SELECT A.row_no, A.col2, A.col3
FROM match_cols A
WHERE EXISTS (
    SELECT B.col2, B.col3
    FROM match_cols B
    WHERE A.col2 = B.col2 AND A.col3 = B.col3
    GROUP BY B.col2, B.col3
    HAVING COUNT(*) > 1
)
ORDER BY A.col2, A.col3;

方法三：具體化非唯一值至暫存表，再聯結（最快）

SELECT col2, col3 INTO #mytemp
FROM match_cols
GROUP BY col2, col3
HAVING COUNT(*) > 1;

SELECT #mytemp.col2, #mytemp.col3, match_cols.row_no
FROM #mytemp
JOIN match_cols ON #mytemp.col2 = match_cols.col2 AND #mytemp.col3 = match_cols.col3
ORDER BY 1,2,3;

原理：暫存表僅含少數非唯一組合（如 16 筆），以此為外部表逐一索引掃描主表，邏輯讀極少。

方法四：衍生表（Derived Table）

SELECT A.row_no, A.col2, A.col3
FROM match_cols A
JOIN (
    SELECT col2, col3
    FROM match_cols B
    GROUP BY col2, col3
    HAVING COUNT(*) > 1
) B ON A.col2 = B.col2 AND A.col3 = B.col3
ORDER BY A.col2, A.col3, A.row_no;

效能接近方法三，但無需建立實體暫存表。

結果

row_no  col2  col3
------  ----  ----
4       c     b
8       c     b
1       c     d
9       c     d

結論

大數據量下，方法三（暫存表）與方法四（衍生表）效能最佳，建議優先採用。