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

今天在客戶端發現資料庫中存留有一些重複的垃圾資料，因此想透過 T-SQL 指令直接刪除重複的資料，不過一般透過常見透過 [GROUP BY](http://technet.microsoft.com/zh-tw/library/ms177673.aspx) 的方式刪除重複資料卻不適用於今天的狀況，因為我們資料表的**主索引鍵** (Primary Key) 是 [uniqueidentifier](http://technet.microsoft.com/zh-tw/library/ms190215.aspx) 類型，不能透過 [MAX](http://technet.microsoft.com/zh-tw/library/ms187751.aspx) 彙總函示運算，這時就要利用 [OVER 子句](http://msdn.microsoft.com/zh-tw/library/ms189461.aspx)並搭配 **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](http://technet.microsoft.com/zh-tw/library/ms177673.aspx) 子句中

不過，這樣一個解法無法適用於**主索引鍵**為 [uniqueidentifier](http://technet.microsoft.com/zh-tw/library/ms190215.aspx) 類型的表格，因為 [uniqueidentifier](http://technet.microsoft.com/zh-tw/library/ms190215.aspx) 類型的欄位無法使用 [MAX](http://technet.microsoft.com/zh-tw/library/ms187751.aspx) 彙總函示取得重複資料的唯一值，因此就必須尋求其他彙總函示來運算，而我們的解法就是透過 SQL Server 2005 才提供的 [一般資料表運算式](http://msdn.microsoft.com/zh-tw/library/ms190766.aspx) ([Common Table Expression](http://msdn.microsoft.com/zh-tw/library/ms175972.aspx)) (簡稱 **CTE**) 語法，加上 [OVER 子句](http://msdn.microsoft.com/zh-tw/library/ms189461.aspx)並搭配 **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**

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

**相關連結**

-   [使用一般資料表運算式](http://msdn.microsoft.com/zh-tw/library/ms190766.aspx)
-   [使用一般資料表運算式的遞迴查詢](http://msdn.microsoft.com/zh-tw/library/ms186243.aspx)
-   [WITH common\_table\_expression (Transact-SQL)](http://msdn.microsoft.com/zh-tw/library/ms175972.aspx)
-   [OVER 子句 (Transact-SQL)](http://msdn.microsoft.com/zh-tw/library/ms189461.aspx)
-   [每日一SQL-刪除重複資料](https://dotblogs.com.tw/lastsecret/2010/07/13/16532)
-   [How to remove duplicate rows from a table in SQL Server](http://support.microsoft.com/?scid=kb;en-us;139444)
-   [Delete Duplicate Rows From an Oracle Table](http://www.devx.com/tips/Tip/14665)  
    註: 由於 Oracle 內建 ROWID 系統欄位，該欄位會自動算出查詢出來的資料列編號，因此不需靠主索引鍵就能順利刪除重複資料。
-   [輕易刪除Excel中大量重複資料的絕招](http://it.notes.tw/2009/07/excel_13.html)