在Oracle中找出重複的紀錄的方法(轉)

在Oracle中找出重複的紀錄的方法(轉)[@more@]SQL> desc test
Name Null? Type
----------------------------------------- -------- -----------------
ID NUMBER
--表 test有重複的記錄1,10
SQL> select * from test;

ID
----------
1
2
3
4
10
1
1
1
1
1
10

11 rows selected.
--查詢表中的哪些記錄有重複



SQL> select * from test group by id having count(*)>1;

ID
----------
1
10
--查詢出沒有重複記錄的結果集
SQL> select * from test group by id;

ID
----------
1
2
3
4
10

SQL> select distinct * from test;

ID
----------
1
2
3
4
10
--刪除重複的記錄
SQL> delete from test a where a.rowid!=(select max(rowid) from test b
2 where a.id=b.id);

6 rows deleted.

SQL> commit;

Commit complete.
--刪除後的查詢結果集
SQL> select * from test;

ID
----------
2
3
4
1
10