SQL 删除重复记录，并保留其中一条

SQL：删除重复数据，只保留一条用SQL语句,删除掉重复项只保留一条在几千条记录里,存在着些相同的记录,如何能用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,seq 
having count() > 1
) 
and 
seq in 
（
select 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
)

转自：https://blog.csdn.net/shope9/article/details/87113645