共用方式為

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

  • 發行項

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

原始產品版本：SQL Server
原始 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 簡單且有效，原因如下：

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

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