萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL 查詢與刪除重複記錄:單欄位、多欄位、游標與臨時表實戰

場景一:單一欄位判斷重複(以 peopleId 為例)

查詢重複

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

刪除重複,保留 rowid 最小的一筆

DELETE FROM people
WHERE peopleId IN (
    SELECT peopleId FROM people
    GROUP BY peopleId HAVING COUNT(peopleId) > 1
)
AND rowid NOT IN (
    SELECT MIN(rowid) FROM people
    GROUP BY peopleId HAVING COUNT(peopleId) > 1
);

場景二:多欄位判斷重複(peopleId + seq)

查詢重複

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

刪除重複,保留 rowid 最小

DELETE 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
);

場景三:僅查詢重複記錄(不含最小 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
);

場景四:簡易聚合統計

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

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

進階方法:游標逐筆刪除(適用大量資料)

DECLARE @max INT, @id INT;
DECLARE cur_rows CURSOR LOCAL FOR
    SELECT 主鍵欄位, COUNT(*) FROM 表名
    GROUP BY 主鍵欄位 HAVING COUNT(*) > 1;

OPEN cur_rows;
FETCH cur_rows INTO @id, @max;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @max = @max - 1;
    SET ROWCOUNT @max;
    DELETE FROM 表名 WHERE 主鍵欄位 = @id;
    FETCH cur_rows INTO @id, @max;
END

CLOSE cur_rows;
SET ROWCOUNT 0;

方法二:DISTINCT 導入臨時表(全欄位完全重複)

SELECT DISTINCT * INTO #Tmp FROM 表名;
DROP TABLE 表名;
SELECT * INTO 表名 FROM #Tmp;
DROP TABLE #Tmp;

建議:新增唯一索引從根本防止重複。

方法三:保留第一筆(部分關鍵欄位重複)

SELECT IDENTITY(INT,1,1) AS autoID, * INTO #Tmp FROM 表名;
SELECT MIN(autoID) AS autoID INTO #Tmp2 FROM #Tmp GROUP BY Name, Address;
SELECT * FROM #Tmp WHERE autoID IN (SELECT autoID FROM #Tmp2);
copyright © 萬盛學電腦網 all rights reserved