刪除資料庫重複資料

2010-07-13

3650
0
Oracle
2010-10-20

資料庫沒有建立PK時日子久了難免會出現重複資料

有時更會影響程式，導致出現bug

來測試一下刪除重複資料的方法吧

資料庫沒有建立PK時日子久了難免會出現重複資料

有時更會影響程式，導致出現bug

來測試一下刪除重複資料的方法吧

                UNION ALL
                SELECT   2 pk, 'nikon' name, 'd40' model FROM DUAL
                UNION ALL
                SELECT   3 pk, 'canon' name, 'd450' model FROM DUAL
                UNION ALL
                SELECT   4 pk, 'nikon' name, 'd40' model FROM DUAL
                UNION ALL
                SELECT   5 pk, 'canon' name, 'd450' model FROM DUAL
                UNION ALL
                SELECT   6 pk, 'nikon' name, 'd60' model FROM DUAL
                UNION ALL
                SELECT   7 pk, 'canon' name, 'd550' model FROM DUAL
                UNION ALL
                SELECT   8 pk, 'canon' name, 'd550' model FROM DUAL
                UNION ALL
                SELECT   9 pk, 'canon' name, 'd500' model FROM DUAL
                UNION ALL
                SELECT   10 pk, 'nikon' name, 'd90' model FROM DUAL
                UNION ALL
                SELECT   11 pk, 'nikon' name, 'd300' model FROM DUAL
                UNION ALL
                SELECT   12 pk, 'nikon' name, 'd300' model FROM DUAL
                UNION ALL
                SELECT   13 pk, 'canon' name, 'd500' model FROM DUAL
                UNION ALL
                SELECT   14 pk, 'nikon' name, 'd300' model FROM DUAL
                UNION ALL
                SELECT   15 pk, 'canon' name, 'd50' model FROM DUAL)
SELECT   *
  FROM   camera

會出現這樣的資料

如果我的條件是name加model不重複的話就group by 然後取最小或最大的pk就可以得到唯一一筆

然後把把除了那筆的資料全部砍掉就好了

  FROM   camera
 WHERE   pk  IN (  SELECT   MIN (pk)
                        FROM   camera
                    GROUP BY   (name, model))

這些是不要刪掉的資料

所以就

 WHERE   pk not  IN (  SELECT   MIN (pk)
                        FROM   camera
                    GROUP BY   (name, model))

如果沒有pk這種欄位的話在oracle中用rowid還蠻快的

sql server 2005的話就這樣

SELECT
	ss.name AS 'schema_name', sa.name AS 'columns_name', st.name AS 'table_name', sty.name
	, ROW_NUMBER()over(order by ss.name) rownum
FROM 
	sys.all_columns AS sa INNER JOIN
	sys.tables AS st ON sa.object_id = st.object_id INNER JOIN
	sys.schemas AS ss ON st.schema_id = ss.schema_id inner join 
	sys.types as sty on sa.system_type_id = sty.system_type_id                
)
select  columns_name,table_name
from 
	all_tab_columns 
where 
	rownum not in (
		select MIN(rownum) from all_tab_columns
		group by columns_name,table_name
	)

這樣是找到重複的資料，要砍就改成DELETE就ok了

題外話，SQL Server要找的類似Oracle裡的all_tab_columns還真麻煩(正規化的真徹底..)

2010/10/20修改

之前用反敲的方式一路解問題

解到最後解出了not in就好了

其實多看幾次就會發現一個大問題了

因為其實我是用rownum去做排列，所以同樣的資料會排成

沒錯，用not in是很笨的做法，因為目的只是取得一筆就好了

那麼用Rank > 1即可

其實之前小弟的朋友就有點出此問題了

原本他要自己寫文章點出

我在請各位連過去即可

但是發現他好像沒時間寫，所以我就自己來止血囉XD

回首頁