The Will Will Web

記載著 Will 在網路世界的學習心得與技術分享

如何刪除 SQL Server 資料庫中重複的資料 (兩種不同解法)

今天在客戶端發現資料庫中存留有一些重複的垃圾資料,因此想透過 T-SQL 指令直接刪除重複的資料,不過一般透過常見透過 GROUP BY 的方式刪除重複資料卻不適用於今天的狀況,因為我們資料表的主索引鍵 (Primary Key) 是 uniqueidentifier 類型,不能透過 MAX 彙總函示運算,這時就要利用 OVER 子句並搭配 PARTITION BY 語法才能幫我完成任務。

一般來說,最常見的刪除重複資料方法是類似如下的 T-SQL 語法:

DELETE FROM [dbo].[MyTable] WHERE 主索引鍵 NOT IN
    (SELECT MAX(主索引鍵) From [dbo].[MyTable] GROUP BY 欄位1, 欄位2, 欄位3)

這個刪除重複資料的方法重點有二:

  1. 一定要有個數值類型主索引鍵,例如:int
    ◆ 在上述語法中就是名為 ID 這個欄位
  2. 決定何謂重複資料
    ◆ 若判斷重複資料的依據是看其中 3 個欄位,那就將這些欄位全部列入 GROUP BY 子句中

不過,這樣一個解法無法適用於主索引鍵uniqueidentifier 類型的表格,因為 uniqueidentifier 類型的欄位無法使用 MAX 彙總函示取得重複資料的唯一值,因此就必須尋求其他彙總函示來運算,而我們的解法就是透過 SQL Server 2005 才提供的 一般資料表運算式 (Common Table Expression) (簡稱 CTE) 語法,加上 OVER 子句並搭配 PARTITION BY 語法來完成刪除重複資料的動作,以下是範例語法:

WITH TmpOrderdTable
AS
(
  SELECT 
    GroupID = ROW_NUMBER() OVER (PARTITION BY 欄位1, 欄位2, 欄位3 ORDER BY 主索引鍵)
  FROM
    [dbo].[MyTable]
)

DELETE FROM TmpOrderdTable WHERE GroupID > 1

透過這種方式不管任意類型的主索引鍵都能成功刪除重複資料,缺點只在於只有 SQL Server 2005 以上的版本才支援此語法。

額外補充 1

如果發現刪除的時間非常久(如果刪除十萬筆資料超過 5 秒就算久),那很可能是已經發生資料鎖定的情況,這種狀況在忙碌的正式機刪除重複資料時經常發生,這時建議先將資料庫設定為「單人存取模式」再進行刪除重複資料的動作,範例語法如下:

USE [master]
ALTER DATABASE [MyDB] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE

USE [MyDB]

WITH TmpOrderdTable
AS (
  SELECT GroupID = ROW_NUMBER() OVER (PARTITION BY 欄位1, 欄位2, 欄位3 ORDER BY 主索引鍵)
  FROM [dbo].[MyTable]
)
DELETE FROM TmpOrderdTable WHERE GroupID > 1

USE [master]
ALTER DATABASE [MyDB] SET  MULTI_USER WITH ROLLBACK IMMEDIATE

額外補充 2

若要避免資料重複,建議額外建立含有多個欄位的「唯一索引鍵」,從資料庫層級就阻擋所有可能重複資料的出現。

相關連結