MySQL中删除重复数据只保留一条

来源：互联网发布：c 程序员招聘编辑：程序博客网时间：2024/05/29 08:22

用SQL语句,删除掉重复项只保留一条

在几千条记录里,存在着些相同的记录,如何能用SQL语句,删除掉重复的呢
1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断

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

2、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录
注：（这条语句会报错，查询和删除可能不能在同一条sql中）

    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 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

转：http://www.cnblogs.com/jiangxiaobo/p/6589541.html

阅读全文

0 0