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,则返回空字符串.