使用指令碼從 SQL Server 資料表中移除重複列

本文提供指令碼，從 Microsoft SQL Server 資料表中移除重複列。

原始產品版本： SQL S
原始 KB 編號： 70956

摘要

有兩種常見方法可從 SQL Server 資料表中刪除重複記錄。 為方便示範，請從建立範例資料表及資料開始：

CREATE TABLE original_table (key_value int )

INSERT INTO original_table values (1)
INSERT INTO original_table values (1)
INSERT INTO original_table values (1)

INSERT INTO original_table values (2)
INSERT INTO original_table values (2)
INSERT INTO original_table values (2)
INSERT INTO original_table values (2)

然後，請嘗試下列方法，從資料表中移除重複列。

方法 1

執行下列指令碼：

SELECT DISTINCT *
INTO duplicate_table
FROM original_table
GROUP BY key_value
HAVING COUNT(key_value) > 1

DELETE original_table
WHERE key_value
IN (SELECT key_value
FROM duplicate_table)

INSERT original_table
SELECT *
FROM duplicate_table

DROP TABLE duplicate_table

本指令碼依照指定順序執行下列動作：

• 將原始資料表中任何重複列的一個實例移至重複的資料表。
• 刪除原始資料表中也位於重複資料表的所有列。
• 將重複資料表中的列移回原始資料表。
• 卸除重複的資料表。

這個方法很簡單。 不過，資料庫需具備足夠空間，才能暫時建置重複的資料表。 因為您正在移動資料，此方法也會產生額外負荷。

此外，如果您的資料表有 身分識別 欄，當您將資料還原至原始資料表時，必須使用 SET IDENTITY_INSERT ON。

方法 2

Microsoft SQL Server 2005 中引進的 ROW_NUMBER 函式可讓此作業變得更容易：

DELETE T
FROM
(
SELECT *
, DupRank = ROW_NUMBER() OVER (
              PARTITION BY key_value
              ORDER BY (SELECT NULL)
            )
FROM original_table
) AS T
WHERE DupRank > 1 

本指令碼依照指定順序執行下列動作：

• 使用 ROW_NUMBER 函數，根據 key_value 可能是以逗號分隔的一個或多個欄來分割資料。
• 刪除收到 DupRank 值大於 1 的所有記錄。 這個值表示記錄是重複的。

因為表達式 (SELECT NULL) ，腳本不會根據任何條件來排序數據分割的數據。 如果您刪除重複項目的邏輯需要選擇要刪除的記錄，以及要根據其他數據行的排序順序保留哪些記錄，您可以使用 ORDER BY 表達式來執行此動作。

其他相關資訊

方法 2 簡單且有效，原因如下：

• 您不需要暫時將重複的記錄複製到另一個數據表。
• 例如，您不需要將原始數據表與本身聯結 (例如，藉由使用 和 HAVING) 的組合傳回所有重複記錄的GROUP BY子查詢。
• 為了達到最佳效能，您應該在數據表上具有對應的索引，該索引會使用 key_value 做為索引鍵，並包含您可能已在表達式中 ORDER BY 使用的任何排序數據行。

不過，這個方法不適用於不支援 ROW_NUMBER 函式的過期 SQL Server 版本。 在此情況下，您應該改用 方法 1 或一些類似的方法。