2013年10月9日 星期三

刪除重複資料

對於未先建立主鍵(Primary Key)的資料表中,可能因重複匯入或輸入資料而造成資料重複問題,可利用HAVING子句篩選出COUNT(*)大於1筆的資料即為重複資料,SQL及執行結果,如下。刪除重複資料部分,ORACLE可利用所提供每筆資料列(Row)唯一的邏輯位置ROWIDROWID虛擬欄位可於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次測試,將分成BC兩批。

方法1: 資料列(Row)邏輯位置(Pysical Address)
ORACLE可用ROWIDMSSQL 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 ExpressionCTE
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 [暫存資料表]

沒有留言:

張貼留言