查找和删除重复记录(重点)

create table student (   
  name varchar(12),     --姓名   
  sex  varchar2(2)      --性别   
)
insert into student values('张三','01');   
insert into student values('李四','01');   
insert into student values('王五','02');   
commit;

--插入重复记录
insert into student  select * from student;  
commit;

--查看总共有多少记录
select rowid,name,sex from student;

--------------------------------------------------------

--查询到重复记录:方法1：
select rowid,name ,sex from student      
where student.rowid not in(
  select max(t1.rowid)  from student t1 group by t1.name,t1.sex
); 


--删除重复记录 :方法1(通过唯一序号rowid删除 ); 
delete from student where rowid not in ( 
　　select max(t1.rowid) from student t1 group by t1.name,t1.sex --这里用min(rowid)也可以。
); 




--------------------------------------------------------

--查询到重复记录:方法2
select rowid , y.* 
from student y
where y.rowid>
(
select min(x.rowid)
from student x
where x.name=y.name
)


--删除重复记录 :方法2(通过唯一序号rowid删除 ); 

delete from student y
where y.rowid>
(
select min(x.rowid)
from student x
where x.name=y.name
)


----------------------------------------------------

--查询到重复记录:方法3
select distinct * from student;

--删除重复记录:方法3（通过临时表删除）：
 create table temp_student as(select * from student);--创建临时表temp_student
 truncate table Student;--清空employee表的数据
 insert into Student select * from temp_student;--再将临时表里的内容插进来

--------------------------------------------------------