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

原始產品版本:SQL Server
原始 KB 編號: 70956

摘要

本文說明如何利用 Transact-SQL(T-SQL)腳本移除Microsoft SQL Server中資料表中的重複列。 它描述了兩種常見的方法:將每個重複列的一個實例複製到獨立資料表,並使用 ROW_NUMBER 函式刪除多餘的列。 如果某個資料表包含重複記錄,例如因為它沒有唯一限制或主鍵,請參閱本文。 方法二比較簡單,因為它能就地運作。

建立一個包含重複列的範例表

為了示範這兩種方法,請建立一個名為 original_table 的範例表,只有一欄, key_value並加入重複資料:

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)

接著,使用以下方法之一移除表格中的重複資料列。

Note

這些腳本會永久刪除資料。 在對生產資料執行之前,先備份資料表或資料庫。

方法一:將重複的資料列移到獨立資料表,並重新插入一份副本

方法一使用兩個資料表: original_table 以及第二個資料表 duplicate_table,腳本會建立後丟棄。 若要將腳本套用到另一張資料表,請將 original_table 替換為資料表名稱。 此方法若重複是由多個欄位共同定義,則需重寫 GROUP BY 和 COUNT 邏輯。

執行下列指令碼:

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

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

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

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

另外,如果你的資料表有 IDENTITY 欄位,還原資料到原始資料表時必須使用 SET IDENTITY_INSERT ON 。

方法二:使用 ROW_NUMBER 函式刪除重複的資料列

ROW_NUMBER函數於2005年Microsoft SQL Server引入,使此操作簡單許多。 將 key_value 替換為定義重複的欄位。

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 將資料分割,key_value 可以是一欄或多欄,中間以逗號分隔。
  • 刪除收到 DupRank 值大於 1 的所有記錄。 這個值表示記錄是重複的。

由於 (SELECT NULL) 運算式,腳本不會根據任何條件來排序分割後的資料。 如果您刪除重複項目的邏輯需要根據其他資料行的排序順序來決定要刪除哪些記錄以及保留哪些記錄,您可以使用 ORDER BY 表達式來執行此動作。

移除重複資料時,請保留最近的一列

若要保留具有最新值的資料列,請依該欄以降序排序。 舉例來說,考慮一個假設的 employee_table,其中同一位員工有多筆資料。 以下腳本只保留最近 updated_date 值的那一列:

DELETE T
FROM
(
SELECT *
, DupRank = ROW_NUMBER() OVER (
              PARTITION BY employee_name
              ORDER BY updated_date DESC
            )
FROM employee_table
) AS T
WHERE DupRank > 1

具有最新 updated_date 值的那一列其 DupRank 值為 1,因此腳本會保留該列並刪除較舊的列。

方法 2 簡單且有效,原因如下:

  • 您不必暫時將重複的記錄複製到另一個數據表。
  • 它不需要您將原始資料表與自身聯結(例如,使用透過 GROUP BY 和 HAVING 的組合傳回所有重複記錄的子查詢)。
  • 為了獲得最佳效能,您應該在數據表上建立對應的索引,使用 key_value 作為索引鍵,並包含您可能在 ORDER BY 表達式中使用的任何排序數據行。

此ROW_NUMBER函式可在 SQL Server 2005 及更新版本中使用。 如果你使用的 SQL Server 版本不支援 ROW_NUMBER 函式,請改用方法一或類似方法。

我該如何防止資料表中出現重複的列?

移除重複後,你可以透過在識別列的欄位上定義 唯一條件約束 或 唯一索引 來幫助防止新的重複列。