[SQL] 刪除重覆資料

刪除 Table 中重覆的資料並保留一筆資料。

• Sample Data

DECLARE @Temp TABLE(Data char(1))
INSERT INTO @Temp VALUES
    ('A') ,
    ('B') ,
    ('C') ,
    ('A') , -- 重覆
    ('B') , -- 重覆
    ('D') ,
    ('E') ,
    ('F') ,
    ('G') ,
    ('F')   -- 重覆

• 方法一：利用暫存資料表

SELECT DISTINCT Data INTO TempData
FROM @Temp
 
DELETE FROM @Temp -- 實體 Table 可以用 TRUNCATE
 
INSERT INTO @Temp
SELECT *
FROM TempData

DROP TABLE TempData

• 方法二：跑迴圈來刪除

WHILE 1 = 1
BEGIN
  DELETE TOP (1) 
  FROM @Temp
  WHERE Data IN
      (
          SELECT Data
          FROM @Temp
          GROUP BY Data
          HAVING COUNT(*) > 1
      )
 
  IF @@ROWCOUNT = 0
      BREAK ;
END

• 方法三：利用 CTE 特性來達成

; 
WITH CTE AS
(
  SELECT * , 
         ROW_NUMBER() OVER(PARTITION BY Data ORDER BY Data) AS ROWNO
  FROM @Temp
)
DELETE FROM CTE WHERE ROWNO > 1

延伸閱讀
• [SQL] DELETE 和 TRUNCATE

參考資料
• 論壇問題出處
• [SQL SERVER][Performance]刪除重複資料效能比較