SQL 進階技巧：重複資料查找、去重與序號產生完整攻略

場景一：找出所有重複的記錄

範例資料表 emp：

方法 1：子查詢 + IN（最直觀）

SELECT * FROM emp
WHERE name IN (
    SELECT name FROM emp
    GROUP BY name
    HAVING COUNT(*) > 1
);

方法 2：相關子查詢 + COUNT（稍高效）

SELECT * FROM emp e1
WHERE (SELECT COUNT(*) FROM emp e2 WHERE e2.name = e1.name) > 1;

方法 3：EXISTS + 自我連接（標準寫法）

SELECT * FROM emp e1
WHERE EXISTS (
    SELECT 1 FROM emp e2
    WHERE e2.name = e1.name
      AND e2.emp_no <> e1.emp_no
);

方法 4：內連接去重（效能最佳）

SELECT DISTINCT e1.*
FROM emp e1
INNER JOIN emp e2
  ON e1.name = e2.name
 AND e1.emp_no <> e2.emp_no;

場景二：刪除重複資料，保留一筆

擴充範例資料（完全重複列）：

方法 1：暫存表法（簡單、需建表權限）

SELECT DISTINCT * INTO #tmp FROM emp;
DELETE FROM emp;
INSERT INTO emp SELECT * FROM #tmp;
DROP TABLE #tmp;

方法 2：新增 IDENTITY 欄位做區分（無暫存表權限時）

-- 1. 加入流水號
ALTER TABLE emp ADD chk INT IDENTITY(1,1);

-- 2. 刪除較大 chk 的重複列
DELETE FROM emp
WHERE chk NOT IN (
    SELECT MIN(chk) FROM emp GROUP BY name, age
);

-- 3. 移除輔助欄位
ALTER TABLE emp DROP COLUMN chk;

方法 3：CTE + ROW_NUMBER()（SQL 2005+ 推薦）

WITH cte AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY name, age ORDER BY (SELECT 0)) AS rn
    FROM emp
)
DELETE FROM cte WHERE rn > 1;

場景三：為查詢結果產生流水號

方法 1：IDENTITY 插入暫存表

SELECT *, IDENTITY(INT, 1, 1) AS seq INTO #tmp FROM emp ORDER BY age;
SELECT * FROM #tmp;

方法 2：ROW_NUMBER() 視窗函數（SQL 2005+ 標準寫法）

SELECT *,
       ROW_NUMBER() OVER (ORDER BY age) AS seq
FROM emp;

方法 3：相關子查詢 COUNT（舊版本相容）

SELECT e1.*,
       (SELECT COUNT(*) FROM emp e2 WHERE e2.emp_no <= e1.emp_no) AS seq
FROM emp e1
ORDER BY seq;

效能與適用版本對照

實務建議

1. 生產環境刪除資料前，**務必先備份**或在交易中驗證 SELECT 結果無誤再提交。
2. 大量資料去重建議分批處理（如每批 5,000 筆），避免長時間鎖表、交易記錄暴增。
3. 永久解決重複根源：於關鍵欄位加上 UNIQUE 約束或主鍵，從源頭阻擋髒資料寫入。