Oracle删除重复数据

Oracle删除重复数据

 

--oracle
/*1.查找表中多余的重复记录，重复记录是根据单个字段（sid）来判断*/
select * from student where sid in (select sid from student group by sid having count(sid) > 1)
select * from student where (sid,name) in (select sid ,name from student group by sid,name having count(*)> 1)
-- 2.删除表中的重复记录（只留rowid最小值）（效率低）
delete from people 
where peopleId in (
	select peopleId from people 
	group by peopleId  
    having count(peopleId) > 1
)
and rowid not in (
	select min(rowid) from people group by peopleId
	having count(peopleId) > 1
);


-- 3.查询sid,name都相同但不是最小rowid的数据
select * from student  where id not in (select min(id) from student group by sid,name)

-- 4.查询sid,name都相同组中的最小id，
select min(id) from student group by sid,name

-- 5.删除sid,name相同但不是最小rowid的垃圾数据(oracle允许，mysql不允许，需要使用虚拟表)
delete from student  where id not in (select min(id) from student group by sid,name)

--6.使用嵌套查询:查询重复数据
	select a.rowid,a.* from student a
	where a.rowid != (
		select max(b.rowid) from student b
		where a.sid = b.sid and a.name = b.name
	)
--使用分析函数
delete from student t
where exists (
	select 1 from (
		select row_number()over(partition by sid,name order by id desc),t.*
		from student t
	)tj 
	where tj.id = t.id and t.rn >= 2 --id倒序，保存最大项
	
)

	
--创建临时表存储重复数据，再删除（可提升效率），不再赘述。

-- MySQL：
delete from student where id not in(
   select min(id) from (select * from student) as t group by t.name, t.sid
)
-- 即：MySQL不允许在统计表的基础上，删除基表中的数据