Oracle如何刪除表中重複記錄保留第一條

問題描述：

有一張表因為沒有加重複判斷，導致會有些重複資料存在，現在需要根據重複規則去掉重複的記錄，只保留第一條。

解決方案：

方案1：採用rowid的方式

DELETE FROM tmp101 A
 WHERE ROWID NOT IN (SELECT MIN(ROWID)
                   FROM tmp101 b
                  WHERE A.col1 = b.col1
                    AND A.col2 = b.col2
                    AND A.col3 = b.col3
                    AND A.col4 = b.col4);

如果表中沒有外來鍵約束，上述方式可以很好的解決。如果有外來鍵的話可能會遇到如下問題。

這部分記錄已經在別的表裡面有引用了，如果要刪除，需要先刪除子表中的資料。

-- 刪除子表
delete from tmp1011 where col in(
select col FROM tmp101 A
 WHERE ROWID NOT IN (SELECT MIN(ROWID)
                   FROM tmp101 b
                  WHERE A.col1 = b.col1
                    AND A.col2 = b.col2
                    AND A.col3 = b.col3
                    AND A.col4 = b.col4));
                    
-- 刪除主表                   
DELETE FROM tmp101 A
 WHERE ROWID NOT IN (SELECT MIN(ROWID)
                   FROM tmp101 b
                  WHERE A.col1 = b.col1
                    AND A.col2 = b.col2
                    AND A.col3 = b.col3
                    AND A.col4 = b.col4);

方案2：採用row_number()over(partition by  order by)

-- 刪除子表
DELETE FROM tmp1011 WHERE col IN (
SELECT col FROM (
SELECT col,col1,col2,col3,col4
row_number()OVER(PARTITION BY col1,col2,col3,col4 ORDER BY col) rn
FROM tmp101)
WHERE rn <> 1
);
-- 刪除主表
DELETE FROM tmp101 WHERE col IN (
SELECT col FROM (
SELECT col,col1,col2,col3,col4
row_number()OVER(PARTITION BY col1,col2,col3,col4 ORDER BY col) rn
FROM tmp101)
WHERE rn <> 1
);

如果資料量大導致的刪除困難，記得加下迴圈。

-- 按照日期進行迴圈
DECLARE 
bdate DATE;
edate DATE;
BEGIN
  edate := TRUNC(SYSDATE);
  SELECT NVL(TRUNC(MIN(col5)),TRUNC(SYSDATE)) INTO bdate FROM tmp101;
  
WHILE bdate < edate LOOP
  
DELETE FROM tmp1011 WHERE col IN (
SELECT col FROM (
SELECT col,col1,col2,col3,col4
row_number()OVER(PARTITION BY col1,col2,col3,col4 ORDER BY col) rn
FROM tmp101
WHERE col5 >= bdate AND col5 < bdate + 1)
WHERE rn <> 1
);
COMMIT;
bdate := bdate + 1;
END LOOP;
END;
/