mysql去重复 保留一条

mysql去重复 保存一条怎么操作呢？一起来看下吧：

1查找表中多余重复记录，重复记录是依据单独字段（peopleId）来判定

SELECT    *FROM    peopleWHERE    peopleId IN(SELECT            peopleId        FROM            people        GROUP BY            peopleId        HAVING            count(peopleId)>1)

2删掉表中多余重复记录，重复记录是依据单独字段（peopleId）来判定，只留有rowid最小纪录

DELETEFROM    peopleWHERE    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 aWHERE(a.peopleId,a.seq)IN(SELECT            peopleId,seq        FROM            vitae        GROUP BY            peopleId,seq        HAVING            count(*)>1)

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

DELETEFROM    vitae aWHERE(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 aWHERE(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 tableNameSET[Title]=RIGHT([Title],(len([Title])-1))WHERE    Title LIKE'村%'

7清除一个字段的右侧的第一位：

UPDATE tableNameSET[Title]=LEFT([Title],(len([Title])-1))WHERE    Title LIKE'%村'

8假删掉表中多余重复记录（多个字段），不包括rowid最小纪录

UPDATE vitaeSET ispass=-1WHERE    peopleId IN(SELECT            peopleId        FROM            vitae        GROUP BY            peopleId

之上就是小编今天的分享，希望可以帮助到大家。

- END -