mysql 怎么删除数据并只保留一条_MySQL中如何删除重复数据只保留一条

在几千条记录里,存在着些相同的记录,如何能用SQL语句,删除掉重复的呢

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

本文由职坐标整理并发布，了解更多内容，请关注职坐标数据库MySQL频道！