oracle查询去掉重复的行,Oracle剔除重复行

Oracle删除重复行

第一种情况是：数据的完全重复

第二种情况是：部分数据的重复

第一种情况的解决方案:

select distinct * into #temp from tableName

delete from tableName

select * into tableName from #temp

drop table #temp

第二种情况的解决方案:

删除表中多余的重复记录，重复记录是根据单个字段(peopleId)来判断，只留有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)

注:rowid为Oracle自带不用该.....

3、查找表中多余的重复记录(多个字段)

select * from vitae a

where (a.peopleId,a.seq) in   (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)

4、删除表中多余的重复记录(多个字段)，只留有rowid最小的记录

delete from vitae a

where (a.peopleId,a.seq) in   (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)

and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

5、查找表中多余的重复记录(多个字段)，不包含rowid最小的记录

select * from vitae a

where (a.peopleId,a.seq) in   (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)

and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

资料引用:http://www.knowsky.com/539381.html