在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
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
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/10617542/viewspace-961497/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
