时间: 2021-08-13 作者:daque
在oracle中,不妨经过独一rowid实行简略反复记载;还不妨建偶尔表来实行...这个只提到个中的几种大略适用的本领,蓄意不妨和大师瓜分(以表employee为例)。 sql> desc employee name null? type emp_id number(10) emp_name varchar2(20) salary number(10,2) 不妨经过底下的语句查问反复的记载: sql> select * from employee; emp_id emp_name salary 1 sunshine 10000 1 sunshine 10000 2 semon 20000 2 semon 20000 3 xyz 30000 2 semon 20000 sql> select distinct * from employee; emp_id emp_name salary 1 sunshine 10000 2 semon 20000 3 xyz 30000 sql> select * from employee group by emp_id,emp_name,salary having count (*)>1 emp_id emp_name salary 1 sunshine 10000 2 semon 20000 sql> select * from employee e1 where rowid in (select max(rowid) from employe e2 where e1.emp_id=e2.emp_id and e1.emp_name=e2.emp_name and e1.salary=e2.salary); emp_id emp_name salary 1 sunshine 10000 3 xyz 30000 2 semon 20000 2. 简略的几种本领: (1)经过创造偶尔表来实行 sql>create table temp_emp as (select distinct * from employee) sql> truncate table employee; (清空employee表的数据) sql> insert into employee select * from temp_emp; (再将偶尔内外的实质插回顾) ( 2)经过独一rowid实行简略反复记载.在oracle中,每一条记载都有一个rowid,rowid在所有数据库中是独一的,rowid决定了每条记载是在oracle中的哪一个数据文献、块、行上。在反复的记载中,大概一切列的实质都沟通,但rowid不会沟通,以是只有决定出反复记载中那些具备最大或最小rowid的就不妨了,其他十足简略。 sql>delete from employee e2 where rowid not in ( select max(e1.rowid) from employee e1 where e1.emp_id=e2.emp_id and e1.emp_name=e2.emp_name and e1.salary=e2.salary);--这边用min(rowid)也不妨。 sql>delete from employee e2 where rowid <( select max(e1.rowid) from employee e1 where e1.emp_id=e2.emp_id and e1.emp_name=e2.emp_name and e1.salary=e2.salary); (3)也是经过rowid,但功效更高。 sql>delete from employee where rowid not in ( select max(t1.rowid) from employee t1 group by t1.emp_id,t1.emp_name,t1.salary);--这边用min(rowid)也不妨。 emp_id emp_name salary 1 sunshine 10000 3 xyz 30000 2 semon 20000