刪除重複資料

對於未先建立主鍵（Primary Key）的資料表中，可能因重複匯入或輸入資料而造成資料重複問題，可利用HAVING子句篩選出COUNT(*)大於1筆的資料即為重複資料，SQL及執行結果，如下。刪除重複資料部分，ORACLE可利用所提供每筆資料列（Row）唯一的邏輯位置ROWID，ROWID虛擬欄位可於DML指令使用，以此即可確認剔除重複資料列；MSSQL 2008版本也提供類以ROWID虛擬欄位即%%physloc%%，使用類似SQL刪除重複值，另外，也可利用一般表格運算式（Common Table Expression:CTE）進行刪除。

SQL	執行結果
SELECT VAL      , COUNT(*) Cnt FROM Test GROUP BY VAL HAVING COUNT(*) >1

註：MSSQL將進行2次測試，將分成B、C兩批。

方法1: 資料列(Row)邏輯位置(Pysical Address)

ORACLE可用ROWID，MSSQL 2008以上可使用%%physloc%%，兩者語法類似，如下。

	SQL	執行結果
MSSQL %%physloc%%	DELETE Test WHERE 1=1      AND %%physloc%% >      (      SELECT MIN(%%physloc%%)      FROM Test S      WHERE 1=1          AND S.VAL = 'B' --先只刪除B          AND Test.VAL = S.VAL      )	刪除1筆[B]重複資料。
ORACLE ROWID	DELETE Test WHERE 1=1      AND ROWID >      (      SELECT MIN(ROWID)      FROM Test S      WHERE 1=1          AND Test.VAL = S.VAL      )	刪除1筆[B]、2筆[C]重複資料。

方法2: Common Table Expression（CTE）

以ROW_NUMBER建立序號，ROW_NUMBER()函數須內含ORDER BY子句，如無可用之欄位時則可用GETDATE()，僅MSSQL支援CTE進行刪除方式，ORACLE將發生語法錯誤，SQL如下：

	SQL	執行結果
S:WITH	WITH Store AS ( SELECT ROW_NUMBER() OVER (PARTITION BY VAL ORDER BY GETDATE()) SEQ FROM   Test WHERE 1=1      AND VAL = 'C'  --先只刪除C ) DELETE FROM Store WHERE  1=1      AND SEQ > 1	刪除2筆[C]重複資料。

方法3: 暫存資料表

步驟如下，不過建議先考慮採用前述2種方法。

ü   SELECT DISTINCT INTO暫存資料表

ü   TRUNCATE TABLE原資料表

ü   INSERT INTO [原資料表] SELECT FROM [暫存資料表]