hsiung.博格 ERP軟體

tags:重覆,GUID,刪除

引用內容:

今天在客戶端發現資料庫中存留有一些重複的垃圾資料，因此想透過 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

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