--1、删除掉重复项只保留一条
delete from dept
where rowid not in
(select max(rowid) mrid from dept group by deptno, dname, loc)
--2、查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断
select *
from people
where peopleId in (select peopleId
from people
group by peopleId
having count(peopleId) > 1)
--3、删除表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断,只留有rowid最小的记录
delete from people
where peopleName in (select peopleName
from people
group by peopleName
having count(peopleName) > 1)
and peopleId not in (select min(peopleId)
from people
group by peopleName
having count(peopleName) > 1)
--4、查找表中多余的重复记录(多个字段)
select *
from vitae a
where (a.peopleId, a.seq) in (select peopleId, seq
from vitae
group by peopleId, seq
having count(*) > 1)
--5、删除表中多余的重复记录(多个字段),只留有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)
--6、查找表中多余的重复记录(多个字段),不包含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)
--7.消除一个字段的左边的第一位:
update tableName set [Title]=Right([Title],(len([Title])-1)) where Title like '村%'
--8.消除一个字段的右边的第一位:
update tableName set [Title]=left([Title],(len([Title])-1)) where Title like '%村'
--9.假删除表中多余的重复记录(多个字段),不包含rowid最小的记录
update vitae set ispass=-1 where peopleId in (select peopleId from vitae group by peopleId)
oracle sql去重
最新推荐文章于 2024-07-16 11:54:08 发布
本文介绍了多种SQL查询和删除语句,用于在数据库表中查找和删除重复记录。方法包括根据单个字段和多个字段判断重复,并保留特定条件的记录,如保留rowid最小的记录。此外,还提供了更新字段内容以删除特定字符的示例。
摘要由CSDN通过智能技术生成