sql 删除重复数据只留一条_sql两条一样的数据删除一条-CSDN博客

本文链接：https://blog.csdn.net/u011149152/article/details/125143029

这篇博客介绍了如何在SQL中删除重复记录，提供了针对单个字段和多个字段的删除策略，包括Oracle和MySQL的解决方案。示例包括使用ROWID、GROUP BY和HAVING子句来定位并删除重复数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

常用

其他

mysql

常用

oracle中，借助rowid，来留下一条

delete from t1

where (f1,f2,f3) in (
	select f1,f2,f3 from t1 
		group by f1,f2,f3 
		having count(*) > 1)

and rowid not in (
	select min(rowid) from t1 
		group by f1,f2,f3 
		having count(*)>1);

其他

在几千条记录里,存在着些相同的记录,如何能用SQL语句,删除掉重复的呢
1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断
select * from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)

2、删除表中多余的重复记录，重复记录是根据单个字段（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)

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)   

6.消除一个字段的左边的第一位：

update tableName set [Title]=Right([Title],(len([Title])-1)) where Title like '村%'

7.消除一个字段的右边的第一位：

update tableName set [Title]=left([Title],(len([Title])-1)) where Title like '%村'

8.假删除表中多余的重复记录（多个字段），不包含rowid最小的记录
update vitae set ispass=-1
where peopleId in (select peopleId from vitae group by peopleId

mysql

DELETE  FROM Person 
WHERE  EXISTS(
	SELECT * FROM (
		SELECT MAX(PersonID) PersonID,PersonName,Age FROM dbo.Person
		GROUP BY PersonName,Age
		HAVING COUNT(1)>1) T
	WHERE Person.PersonName=T.PersonName 
		AND Person.Age=T.Age
		AND Person.PersonID<T.PersonID--如果上面使用MIN函数，这里就要改成>
)