SQL删除重复数据只保留一条

1、查找表中多余的重复记录，重复记录是根据单个字段（peopleName）来判断 

select * from people where peopleName in (select peopleName from people group by peopleName

having count(peopleName) > 1) 

解释如下：select peopleName from people group by peopleName having count(peopleName) > 1 这句sql的意思是：从people表中查出peopleName的条件是按peopleName分组后peopleName的重复次数大于1。

2、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录

DELETE FROM people WHERE peopleName = (  SELECT p.peopleName FROM  (  SELECT peopleName FROM 

people WHERE peopleName = '黎明' GROUP BY peopleName HAVING COUNT(peopleName) > 1 ) p ) 

AND peopleId != ( SELECT pp.pid FROM ( SELECT min(peopleId) pid FROM people WHERE

 peopleName = '黎明' GROUP BY peopleName HAVING COUNT(peopleName) > 1 ) pp)

或者：

DELETE FROM people WHERE peopleName in ( SELECT p.peopleName FROM ( SELECT peopleName FROM 

people GROUP BY peopleName HAVING COUNT(peopleName) > 1 ) p ) AND peopleId not in ( SELECT 

pp.pid FROM ( SELECT min(peopleId) pid FROM people GROUP BY peopleName HAVING 

COUNT(peopleName) > 1 ) pp )

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

SELECT * FROM people p WHERE (p.peopleName,p.phone) in (
select peopleName,phone from people GROUP BY peopleName HAVING count(peopleName)>1)

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

DELETE FROM people  WHERE (people.peopleName,people.phone) in ( SELECT p.peopleName,p.phone

 FROM ( SELECT peopleName,phone FROM people GROUP BY peopleName HAVING 

count(peopleName) > 1 ) p ) AND peopleId not in ( SELECT pp.pid FROM ( 

SELECT min(peopleId) pid FROM people GROUP BY peopleName HAVING 

COUNT(peopleName) > 1 ) pp )

5、查找表中多余的重复记录（多个字段），只包含rowid最小的记录 

SELECT * FROM people  WHERE (people.peopleName,people.phone) in ( SELECT p.peopleName,p.phone 

FROM (SELECT peopleName,phone FROM people GROUP BY peopleName HAVING 

count(peopleName) > 1 ) p ) AND peopleId  in ( SELECT pp.pid FROM

 ( SELECT min(peopleId) pid FROM people GROUP BY peopleName HAVING 

COUNT(peopleName) > 1 ) pp )

6、sql的left()函数表示的是从字符表达式最左边一个字符开始返回指定数目的字符.若 b 的值大于 a 的长度,则返回字符表达式的全部字符a.如果 b 为负值或 0,则返回空字符串.