SQL 查詢與刪除重複資料完整方案：單欄/多欄、保留首行/最大ID、ROW_NUMBER 現代寫法

場景定義

表 T (id PK, name, value)，重複定義：name + value 組合相同視為重複。

一、查詢重複資料

1. 單欄判重 (如 peopleId)

SELECT * FROM people 
WHERE peopleId IN (
    SELECT peopleId FROM people GROUP BY peopleId HAVING COUNT(*) > 1
);

2. 多欄判重 (如 peopleId + seq)

SELECT * FROM vitae a
WHERE (a.peopleId, a.seq) IN (
    SELECT peopleId, seq FROM vitae GROUP BY peopleId, seq HAVING COUNT(*) > 1
);

3. 查重複但不含首行 (保留 rowid 最小)

SELECT * FROM vitae a
WHERE (a.peopleId, a.seq) IN (
    SELECT peopleId, seq FROM vitae GROUP BY peopleId, seq HAVING COUNT(*) > 1
)
AND rowid NOT IN (
    SELECT MIN(rowid) FROM vitae GROUP BY peopleId, seq HAVING COUNT(*) > 1
);

二、刪除重複資料 (保留一行)

方法 1：NOT IN 最大值 (簡單、直觀)

DELETE FROM YourTable
WHERE id NOT IN (
    SELECT MAX(id) FROM YourTable GROUP BY (name + value)
);

方法 2：LEFT JOIN 保留最小 ID

DELETE a FROM T a
LEFT JOIN (
    SELECT MIN(id) AS id FROM T GROUP BY name, value
) b ON a.id = b.id
WHERE b.id IS NULL;

方法 3：游標循環刪除 (舊版、大表慎用)

DECLARE @max INT, @id INT;
DECLARE cur CURSOR LOCAL FAST_FORWARD FOR
    SELECT 主鍵, COUNT(*) FROM T GROUP BY 主鍵 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 T WHERE 主鍵 = @id;
    FETCH NEXT FROM cur INTO @id, @max;
END
CLOSE cur; DEALLOCATE cur; SET ROWCOUNT 0;

方法 4：臨時表重建 (全表去重、需鎖表)

SELECT DISTINCT * INTO #Tmp FROM T;
DROP TABLE T;
SELECT * INTO T FROM #Tmp;
DROP TABLE #Tmp;
-- 缺點：丟失索引/約束/權限/觸發器，需重建

方法 5：ROW_NUMBER() 現代通用寫法 (SQL 2005+，最推薦)

-- 查重複 (含分組排序)
WITH Ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY name, value ORDER BY id) AS rn
    FROM T
)
SELECT * FROM Ranked WHERE rn > 1;

-- 刪除重複 (保留每組第一行，即最小 id)
WITH Ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY name, value ORDER BY id) AS rn
    FROM T
)
DELETE FROM Ranked WHERE rn > 1;

優點：單條語句、支援任意分組欄位、可指定保留策略 (ORDER BY id DESC 保留最大)、計畫穩定、可並行。

三、快速統計重複群組

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

-- 多欄 (名+性別)
SELECT Name, Sex, COUNT(*) FROM A GROUP BY Name, Sex HAVING COUNT(*) > 1;

方案選型總結表