1949啦网--小小 痛苦,是因为能力和欲望不匹配造成的

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

在几千条记录里,存在着些相同的记录,如何能用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

原文链接:https://www.qiquanji.com/post/8162.html

本站声明:网站内容来源于网络,如有侵权,请联系我们,我们将及时处理。

微信扫码关注

更新实时通知

作者:xialibing 分类:网页教程 浏览: