SQL删除重复数据方法

SQL删除重复数据方法

例如：

id name value

1 a pp

2 a pp

3 b iii

4 b pp

5 b pp

6 c pp

7 c pp

8 c iii

id是主键

要求得到这样的结果

id name value

1 a pp

3 b iii

4 b pp

6 c pp

8 c iii

方法1

delete YourTable

where [id] not in (

select max([id]) from YourTable

group by (name + value))

方法2

delete a

from 表 a left join(

select id=min(id) from 表 group by name,value

)b on a.id=b.id

where b.id is null

查询及删除重复记录的SQL语句

查询及删除重复记录的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 peopleId in (select peopleId from people group by peopleId having

count(peopleId) > 1)

第1页下一页