【实验】重复数据查找与删除
1.第一种查询和删除重复的方法
sec@ora10g> SELECT * FROM t;
CODE NAME
---------- --------------------
006 Michael
007 Andy
007 Andy
008 Tina
010 Anna
009 John
6 rows selected.
sec@ora10g> SELECT *
2 FROM t t1
3 WHERE t1.ROWID <> (SELECT MIN (t2.ROWID)
4 FROM t t2
5 WHERE t1.code = t2.code)
6 /
CODE NAME
---------- --------------------
007 Andy
sec@ora10g> delete FROM t t1
2 WHERE t1.ROWID <> (SELECT MIN (t2.ROWID)
3 FROM t t2
4 WHERE t1.code = t2.code)
5 /
1 row deleted.
sec@ora10g> SELECT *
2 FROM t t1
3 WHERE t1.ROWID <> (SELECT MIN (t2.ROWID)
4 FROM t t2
5 WHERE t1.code = t2.code)
6 /
no rows selected
2.第二种查询和删除重复的方法
sec@ora10g> SELECT t1.code,
2 t1.name,
3 ROW_NUMBER ()
4 OVER (PARTITION BY t1.code ORDER BY t1.ROWID)
5 rn
6 FROM t t1;
CODE NAME RN
---------- -------------------- ----------
006 Michael 1
007 Andy 1
007 Andy 2
008 Tina 1
009 John 1
010 Anna 1